MySQL Update Queries

[andyuk]

Hi,

I'm trying to update all the links in the content in my MySQL database.

This piece of code does the job fine:

$content = eregi_replace("\?(id|m)=(.+)\"","\2/\"",$content);
ie. ?id=80" would be changed to 80/"

I want to put this php function into a MySQL update query.

I suppose i need to ues the SUBSTRING_INDEX string function in the query, but how?

I'm totally lost at this point... please help!

Thread about MySQL and PHP functions:
http://www.phpbuilder.com/forum/read.php3?num=5&id=4769&thread=4763

MySQL documentation about string functions:
http://www.mysql.com/doc/S/t/String_functions.html

Thanks,

Andy

[jcornett]

Andy,

Why not just set up an update loop to pull each link value out, run the expression on it, then insert the new value into the DB? Here is a very generic example:

<?php
include('connect.php'); // Connect to the DB

$result = mysql_query("SELECT ident, link from <table_name>"); // ident = key of table

$i = 0;
While($row = mysql_fetch_array($result)) {
$content = eregi_replace("\?(id|m)=(.+)\"","\2/\"",$row[link]);
$result = mysql_query("UPDATE <table_name> set link = '$content' where ident = '$ident'");
$i++; // Update the counter
}
echo "Update complete... $i links updated.";

?>

Hope this helps...

John Cornett
Senior Development Engineer
Web Teks, Inc.
http://www.web-teks.com
E-Mail: [e-mail]

[andyuk]

Hi John,

I did originally try doing it that way but it didn't work, now i realise it was because i was trying to put content back into the database with quote marks. So, obviously it didn't work.

Thanks, your code has helped very much.

Andy