MySQL Command Help

Basic situation is I have a database setup with a table called submitted. Each row has about 7 different variables. One of the variables is called image. There are over 700 rows and what I want to do is....

Each image variable has the following setup...

../images/thumbs/4.jpg

What single command can I use to replace the "../images/thumbs/4.jpg" to just read "thumbs/4.jpg"

 

UPDATE submitted SET image = 'thumbs/4.jpg' WHERE image = '../images/thumbs/4.jpg';

Code (sql):

 

I know that would work for JUST the 4.jpg image but is there a way to have it scan through EVERY row and just change the preceding before the #.jpg?

 

sounds to me like you need to run a loop that will run a select query on each entry (is there an auto_increment value in the table that you can use to loop through the entries?), then run it through the php str_replace function - $new_value = str_replace("../images/", "", $result) - then update the table entry with the $new_value.

 

Yes I do have an autoincrement.

Could you possible give me the query that would be needed that would run the loop to then edit each of the values? I can't really have downtime so I'd rather have it done right the first time.

 

The following should work but I use MS SQL more than MySQL

UPDATE submitted SET image = replace(image, '../images/', ''); 

Code (SQL):

 

What exactly would that replace? What would the before and after look in each row that currently has

../images/thumbs/#.jpg

 

This would by my solution:

update submitted set image = substr(image, 18);

Code (sql):

You can test to see what the field *would* look like before firing off the update:

select substr(image, 18) from submitted limit 5;

Code (sql):

 

Which command would be executed first? What if the numbers go in double or tripple digits?

 

It would remove the ../image/ and replace it with nothing so you would end up with thumbs/#.jpg for each row irrespective how large the # is

 

Worked like a charm. Thank you!

 

no problem, I keep telling our developers I know a little about what i am talking about but they wont believe me