UBB.Developers Forums Community Development Programming MySQL and Server Configuration Editing a post via MySql

I've got some old messages that were posted back in the perl days, and of course the path to the smilies that are embeded in the posts are all pointing to the old perl directory /perl/images

I was thinking something along the lines of this,

UPDATE `B_Body` FROM `w3t_Posts` WHERE 1 AND `B_Body` LIKE '%perl%'
SET 'B_Body' LIKE %perl% ='forum';

But I'm betting what I'd end up with would be a lot of messages with the word forum being the only thing in them.

Anyone got any idea on how I could replace just one word in the body without trashing the rest of the message??

The "1 AND" is redundant, but I don't think you can use "SET - LIKE -" anyway.

I think the only way to do it is to use a PHP (or Perl) script which traverses the table.

I need to do something similar (convert embedded UBB.classic links to UBB.threads links), so I'm interested in the answer too.

Yeah, that is correct, the update can't do magic. =] Also, the syntax is "UPDATE w3t_Posts SET B_Body = 'sometext' WHERE B_Number = xxx" or something similar.

But there would really have to be a script that selects all posts with the wrong image paths and does a str_replace (or maybe strstr?) and then update on each found post. It isn't that hard to do, but it might take a while and the page would probably time out if it isn't done in stages (or from the prompt). Don't think there is another way to do this.

But I would want this script as well, but rather for the markups that have changed. I've been using Eileens old markup-hack for the perl-version which isn't exactly the same as the new markups. Also, the polls made in the perl version will have the wrong string on the button.

If noone else is writing this I might take a shot at it, if I have the time. I'm about to go away for a couple of days soon.

I'm working on a converter script right now, but I'm having some problems since my server is down so I can't see what old posts look like. Hopefully it'll be up tomorrow though, but if someone could give me some examples of how old color markups look in the database (if there is any change to the new one) and also with Eileens extra markups (lines and such) would be good.

If you can give me some example of what needs to be changed when upgrading from classic I could probably add that as well.

The conversion from classic is too complex to describe here. Since the posts are completely renumbered, it requires generating a map file when inserting the posts, and then making a second pass to fix up the links.

I'd just like to see a simple example of applying a PHP string function (str_replace, strstr, or whatever) to a column in each row on a table.

I can do this from the command line rather than as a CGI script, so I'm not concerned about timeouts.

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

---

Thanks for the example.

Could the SELECT query use excessive memory if the table is large? Would it be safer to add a LIMIT qualifier, and then put the whole thing into a loop, so that only a specified number of rows are processed at a time?

I guess, I'm not that experienced in MySQL:s memory management. But since each row is extracted one by one, I don't see why everything has to be in memory. Well, for speed of course... It probably is better to limit the query anyways, at least it can't hurt. =]