database record update

[vbnullchar]

i have about 10,000 records that contains this link in a record "http://site/directory" now i want to replace it all with "http://site2/directory"..

 

i cannot do a update set command because the position of the link is not always the same

 

ex.. text text text text <link> text text text

 

[NArc0t1c]

So you want to make all ten thousand rows the same value?

 

Do this query,

UPDATE table SET column='http://site2/directory'

That should Update all records and set column to 'http://site2/directory' in table.

[MadTechie]

Basic idea

 

<?php
//SQL stuf
$SQL = "SELECT field  FROM table WHERE field LIKE %http://site1/directory%;";

$updateSQL = "";
//loop each record
while($row = mysql_query($SQL))
{
$ID = $row['id'];
$newData = preg_replace('%(.*)(?:http://site1/directory)(.*)%i', '$1http://site2/directory$2', $Data);
$updateSQL .= "UPDATE table SET field = '$newData' WHERE ID = $ID;";
}
mysql_query($updateSQL);
?>

[vbnullchar]

Basic idea

 

<?php
//SQL stuf
$SQL = "SELECT field  FROM table WHERE field LIKE %http://site1/directory%;";

$updateSQL = "";
//loop each record
while($row = mysql_query($SQL))
{
$ID = $row['id'];
$newData = preg_replace('%(.*)(?:http://site1/directory)(.*)%i', '$1http://site2/directory$2', $Data);
$updateSQL .= "UPDATE table SET field = '$newData' WHERE ID = $ID;";
}
mysql_query($updateSQL);
?>

 

this looks good, i'll try it.. thanks alot

[ToonMariner]

remember!

 

Many hosts restrict you to 50 or 100 queries per page!!!!!

 

better to put all this into a txt or .sql file and execute that in your friendly db admin app (liek phpmyadmin or Navicat)

[MadTechie]

Many hosts restrict you to 50 or 100 queries per page!!!!!

 

What happens if the host restricts 50 and you try 51?

if an error what error ?

 

i just want to know as i havnt had this problem but if theirs no error then atleast i can be aware of it,

[vbnullchar]

problem! it doesnt replace the link inside the <a> tag

 

http://intranet/portal should be http://iportal/home

 

<a href=http://intranet/portal?action=leave_details&leave_id=4>http://iportal/home?action=leave_details&leave_id=4</a>

[MadTechie]

erm.. it should!

 

try this regex (for multilines)

$newData = preg_replace('%(.*)(?:http://intranet/portal)(.*)%sim', '$1http://iportal/home$2', $Data);

[vbnullchar]

replace are not cosistent there are records that still contains the old link.. but its ok..

 

Thanks alot!

[MadTechie]

OK just an idea..

change

$SQL = "SELECT field  FROM table WHERE field LIKE %http://site1/directory%;";

to

$SQL = "SELECT field  FROM table;";

 

so it does all records

[ToonMariner]

just went a read a little more of this thread...

 

YOU can do all this in one query

 

$qry = "UPDATE `tablename` SET `field` = REPLACE(`field`,'http://site/directory','http://site2/directory')";

$qry = mysql_query($qry);

 

Better still just run

 

UPDATE `tablename` SET `field` = REPLACE(`field`,'http://site/directory','http://site2/directory') in your phpmyadmin...