Jump to content

database record update


vbnullchar

Recommended Posts

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

 

Link to comment
https://forums.phpfreaks.com/topic/65949-database-record-update/
Share on other sites

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);
?>

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

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,

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>

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...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.