Replace string throughought many MySQL rows

Hey,

I have approximately 360 rows in the MySQL table which I will refer to as Table from this point onwards. There is a field in Table called content and it contains a news post. HTML is used in these news posts, except all images are linked to relatively (for example, <img src="media/image.jpg" /> instead of <img src="http://example.com/media/image.jpg" />).

I need to write a script which will go through every single row in Table, search content for any occurrences of <img src="media and replace it with <img src="http://zconnect.org.uk/media.

How can this be done? It doesn't need to be particularly efficient as I only need to do it once.

Thanks,
Connor Beaton

 

I'm new to this but maybe you could do something like:

$query = "UPDATE table SET content = '<img src=\"http://zconnect.org.uk/media' WHERE content LIKE '<img src=\"media%' ";

mysql_query($query) or die(mysql_error());

PHP:

EDIT: Sorry my method won't work just realised you need to preserve some parts after it so don't use it.

 

Yeah, I need to replace only a small portion of the field. Is it possible to create a loop to pull each row, do a str_replace and then re-insert it?

 

Here try this i've just read up on it and tested it out:

$query = "UPDATE table SET content = REPLACE(content,'<img src=\"media', '<img src=\"http://zconnect.org.uk/media') WHERE 1 = 1";

mysql_query($query) or die(mysql_error());

PHP:

It uses the REPLACE function which replaces part of the field.

 

Ah, thanks so much. Worked like a charm.

 

Nice one

 

Good one wd_2k6!

 

Hi there, i have something like this.

i want to replace this string:

target=\"_blank\" rel=\"nofollow\"]

by this:

>

How can i do this?
I'm trying this:

UPDATE `smf_messages` SET BODY = replace( BODY, 'target=\"_blank\" rel=\"nofollow\"]', ">");

But isn't working... :|

 

So you want to replace all the occurances of
'target=\"_blank\" rel=\"nofollow\"]'
with
">"
in column BODY of table smf_messages ?

don't forget you need to add WHERE 1 = 1 to the end of your query so it matches all rows.

So your query would become:

$query = "UPDATE `smf_messages` SET BODY = REPLACE(BODY, 'target=\"_blank\" rel=\"nofollow\"]', '>') WHERE 1 = 1";

PHP:

 

I already done this:

UPDATE `smf_messages` SET BODY = REPLACE(BODY, 'target=\"_blank\" rel=\"nofollow\"]', '>') WHERE msg_id=24324;

Code (markup):

But it didn't work.

 

How about:

$query = "UPDATE `smf_messages` SET BODY = REPLACE(BODY, 'target="_blank" rel="nofollow"]', '>') WHERE `msg_id` = '24324' ";
mysql_query($query) or die(mysql_error());
echo mysql_affected_rows();

PHP:

Is the column name BODY in caps letters?
Also does msg 24324 definatly have target="_blank" rel="nofollow"] somewhere inside its BODY field exactly, the closing ] seems a bit strange.
Does the code throw and error, also what does mysql affected rows have to say after the query.

If it is still not working please do the following and report what it outputs so we can debug where it's going wrong:

$query2 = "SELECT * FROM `smf_messages` WHERE msg_id = '24324'";
$result = mysql_query($query2) or die(mysql_error());
while($row == mysql_fetch_array($result)){
$body = $row['BODY'];
echo "BODY field: $body <br />":
$findStr = 'target="_blank" rel="nofollow"]';
$pos = strpos($body, $findStr);
if ($pos){ 
echo "The String was found at $pos";
} 
else { 
echo "The String <b>$findStr</b> was not found in <b> $body</b>"; 
}
}

PHP:

 

UPDATE `smf_messages` SET BODY = replace( BODY, "target=\\\"_blank\\\" rel=\\\"nofollow\\\"]", "target=\\\"_blank\\\" rel=\\\"nofollow\\\">" );

Code (markup):

This worked...