Shortening all these queries?

[MySQL_Narb]

Is there a more efficient way of doing this?

 

$database->processQuery("UPDATE `users` SET `username` = ? WHERE `username` = ? LIMIT 1", array($_POST['new'], $_POST['old']), false);
$database->processQuery("UPDATE `posts` SET `username` = ? WHERE `username` = ? LIMIT 1", array($_POST['new'], $_POST['old']), false);
$database->processQuery("UPDATE `threads` SET `username` = ? WHERE `username` = ? LIMIT 1", array($_POST['new'], $_POST['old']), false);
$database->processQuery("UPDATE `messages` SET `creator` = ? WHERE `creator` = ? LIMIT 1", array($_POST['new'], $_POST['old']), false);
$database->processQuery("UPDATE `replies` SET `username` = ? WHERE `username` = ? LIMIT 1", array($_POST['new'], $_POST['old']), false);

[mikosiko]

read multiple-table update syntax .... pay attention to the restrictions, specially if Innodb Referential integrity  constraints are in place.

 

http://dev.mysql.com/doc/refman/5.0/en/update.html

[mikosiko]

In a second thought...

 

what exactly the column 'username' (and creator) represent in each table?... is that a literal like 'johndoe'?  ... if that is the case, seems to me that you have a design problem with no normalized tables.

 

In a normalized model you should store in all those tables just a FK (foreign key) pointing to a record in other table holding that key and the username, therefore if the username need to be changed will be neccesary to update just one table... in your case, the users table should be the only one holding the username and the others the PK of the users table.

 

You users table should look like:

id        INT    (PK)

username VARCHAR

... others fields

 

and per example your table posts

id    INT  (PK)

userid  INT (FK to the users PK)

... others fields 

[Jessica]

Exactly, the username should only be stored in your users table. All other tables should reference the user ID, which will NEVER change,

[MySQL_Narb]

Exactly, the username should only be stored in your users table. All other tables should reference the user ID, which will NEVER change,

 

I use to do this all the time, and I'm not exactly sure why I go by username now. I guess I find it easier.

[Jessica]

And now you can see a way in which it's not