How can change all the Domainname to the New Domainname with only one SQL-Query?

[php-resource.de]

how can i change LINKs in my POSTs table?

I just moved to new domain and some links on the board are linked to the wrong Domainname.

How can change all the Domainname to the New Domainname with only one SQL-Query?

Thanks 4 you help
Berni

[tHE DSS]

Not with a single SQL query, I don't think that possible... but, bring some PHP into the equation, and it's perfectly possible, without pretty much lifting a finger.

I will be wanting to do somthing very similar soon myself, so I thought now about right to try to right a script for the job.

I haven't actually tested this script on a database yet, but i've tested the script out pretty much... just not with a connection to a database.

Try it out, but have a database backup before... you MUST backup your database FIRST.

Anyway, this is the general idea :


<?php

require('./global.php');

$oldDomainName = "www.old_domain.com";
$newDomainName = "www.new_domain.com";

$getPosts = $DB_site->query("SELECT postid,pagetext FROM post ORDER BY postid ASC");
$num_rows = $DB_site->num_rows($getPosts);
echo "$num_rows Posts Retrieved...";
$rows_updated = 0;
if (!$num_rows <=0) {
while ($row = $DB_site->fetch_array($getPosts, MYSQL_ASSOC)) {
if (preg_match ("/$oldDomainName/i", $row[pagetext])) {
$newPageText = preg_replace ("'$oldDomainName'i", $newDomainName, $row[pagetext]);
$DB_site->query("UPDATE post SET pagetext='" . addslashes($newPageText) . "' WHERE postid=$row[postid]");
$rows_updated++;
}
}
echo "$rows_updated Posts Updated";
} else {
echo "No Posts Found In Table";
}
$DB_site->free_result($getPosts);

?>


... substitute the $oldDomainName with your old domain name, and the same with $newDomainName, with your new domain name.

Exectute the script from within your FORUMS root, and just wait for it to end, and the changes should have happend.

I would recommend trying this on a "non-live" server first... like, if you have a copy of your forums on your computer (intranet).

Let me know.

[php-resource.de]

thanks for your Help.
but i'm looking for a SQl-Query. I know that it is possible with only one query, i just don't know the right expression.

Update table set feld = reg(blalblbl) ....

Thanks agin
Berni

[tHE DSS]

I've been speaking with an SQL guru... and he's not sure it's possible with usual SQL.

[php-resource.de]

i know it is.
I saw it on www.vbulletin.com or in this forum.
But i just can't remember me where it was :(

thanks
berni

[tHE DSS]

I'll take your word for it.

Hope somone gives up the link... i'd like a single query to do this.

[php-resource.de]

if i find the solution i will drop you an Email.

cu
berni

[Lesane]

Wich field do you want to update in the post table? Title Or Pagetext?

If pagetext then you could use something like this:

UPDATE post SET pagetext = REPLACE(pagetext, 'http://www.oldurl.com', 'http://www.newurl.com');

This replaces all 'http://www.oldurl.com' to 'http://www.newurl.com' in the field 'pagetext' of the table 'post'.

[php-resource.de]

thanks!!!!
100% the solution i was looking for

thanks again
Berni

[tHE DSS]

Excellent!!