UBB.Developers Forums UBB.threads 7 Development How Do I? Find & replace - one word in many posts

Alright... next issue on my cleaning program.

I have changed the name of my forum and files folders. Updating the UBBT 6.4 configs was easy and everything works fine.

But... (there´s always a but ) ... I now have this cosmetic issue:

Lots of posts have links to graemlins that don´t work now.
Lots of users have avatar link that don´t work.

I know one way to fix this:
Export these tables to a text file.
Do search & replace to edit url´s.
Dump the textfile on the database

With a backup of the database, this should´t be too dangerous. But it will be lots and lots and lots of search and replace.

So my obvious question is if there is annoher way to do this?

Sure, but it would require some scripting.
Basically the php script would search for all posts containing the search word, then it would go through the results, replace the word with what you want it to be, then write the post back to the database.

Ok...sound like a good way to go.

Any idea on how a script like that would look like?

I think that the only fields to search are these:
w3t_Users / U_Picture
w3t_Posts / B_Picture, B_Body

I could look into writing something like that...

any updates to this ?

nevermind... i found it



it's case sensitve though... but atleast it works

Are you sure this is what you searched for?

"REPLACE works exactly like INSERT, except that if an old record in the
table has the same value as a new record on a unique index, the old
record is deleted before the new record is inserted.
See section 7.21 INSERT syntax."

UPDATE w3t_Posts SET B_Body = REPLACE(B_Body, 'cgi-bin','ubb')

This is the proper syntax

Are you sure this is what he wants?
Are you sure this construct even exists in MySQL?


His problem as far I understand it is:

He has posts which have

blablablablablablablablabla <img src="/path_old/gramlin.gif"> blablablablabla

in the body or a signature of a post.

He wants to replace the /path_old/ with /path_new/ .

I don´t see how this should work with the above command.

Hmm...apparently you are right, one learns something new every day:
http://www.joegrossberg.com/archives/000933.html

I would not have posted it if it was not correct

I would

[]Omegatron said:
I would not have posted it if it was not correct [/]

Omego is never wrong. Feel the Power of Cheese

I didn't think there was such a thing either. But Rick posted it somewhere here a while ago. Quite useful.

Scroungr Please

Nah Rick gave me that little useful query a few months ago as I used it a few years ago and forgot it. ;(

Okay sorry Omego Anyways heres the only thing I can find and its from Gardener. Its on post

https://www.ubbdev.com/forum/showflat...prev=#Post52206

[] OK, that sounds nasty. =/ It would really have to be a new feature of the import script then, I guess.

Here is a code snippet that might help you, it updates the body and subject of all posts and does a str_replace on each one. It isn't tested though, so beware!

code: $query = "
SELECT B_Number,B_Username,B_Subject,B_Body
FROM w3t_Posts
";
$sth = $dbh -> do_query($query);

while ( list($Number,$Username,$Subject,$Body) = $dbh -> fetch_array($sth) ) {
$Body_q = addslashes(str_replace("oldstring", "newstring", $Body));
$Subject_q = addslashes(str_replace("oldstring", "newstring", $Subject));
$Number_q = addslashes($Number);
$query = "
UPDATE w3t_Posts SET
B_Body = '$Body_q'
B_Subject = '$Subject_q'
WHERE B_Number = $Number_q
";
$sth2 = $dbh -> do_query($query);
}

[/]

[]Omegatron said:
Scroungr Please

Nah Rick gave me that little useful query a few months ago as I used it a few years ago and forgot it. ;( [/]

You've forgotten more about this stuff than I will ever know.

Yeah well scroungr the mysql query above does all that without any scripting.

so I guess thats not the one then Oh well I did an advanced search and came up blank.

Thats cause where the query I posted is posted you do not have access too so it will not come back in a search

[]Omegatron said:
Thats cause where the query I posted is posted you do not have access too so it will not come back in a search [/]

The one I posted (right above yours ) came from...

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/436/qid/753898

OK, I grabbed the "Generic Threads Page" for quick access to the database, and dropped the below code in the HTML area (or PHP area in this case)



Getting this:

SQL ERROR: Thu, Feb 19 2004 07:45:27 -0700 Database error only visible to forum administrators

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home2/medar/public_html/forums/mysql.inc.php on line 133
Done


Any idea what is goofed?

Unless you're using an old version (pre 6.1 or 6.2 I think)
There is no B_Username field in the Posts Table.

There's B_PosterId which is the user number of the user who made the post.

LOL I am tired.

Trying it again in a bit.