sending multiple UPDATE queries to mySQL?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

sending multiple UPDATE queries to mySQL?

Post by fgomez »

Hello,

My grasp of mySQL is just strong enough to do the things I need to do, so please bear with me.

I need to send multiple update queries to mySQL (updating a range of records won't do here), and I was wondering if it was possible to send them all at once rather than individually. I tried separating the queries with semi-colons in a variable called $q and then doing

Code: Select all

$result = mysql_query($q) ;
but I got a syntax error.

There are about 500 records to update, and I'd prefer not to ding mySQL 500 times if possible.

Thanks.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

What are you updating?

Are you updating 500 record to the same value, or are they all different values?
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

All to the same value.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

well, you could do something like this

Code: Select all

$ids = array(1,2,6,4,9,5,654,546,54,624);

$updateids = implode(",", $ids);

$sql = "UPDATE `yourTable` SET `someField` = 'someValue' WHERE `id` IN(".$updateids.")";

mysql_query($sql);
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

Perfect. I tried searching for IN() on the mySQL doc page, because I figured it would be something like this, but as you can imagine my search results were useless.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Seems a simple where clause would be simpler... if you need it. If you're updating all the records in the table..

Code: Select all

mysql_query("UPDATE `table` SET `foo` = '$bar'") or die(mysql_error());
That would update all the records in the table. If you have a specific condition you need to update on, like where first name = bob, something like this would do:

Code: Select all

mysql_query("UPDATE `table` SET `foo` = '$bar' WHERE `firstname` = 'bob'") or die(mysql_error());
Hope that helps.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

Thanks, Scott, but the IN() solution worked for me. I wasn't updating every record, so the first option wouldn't have sufficed, and the second option wouldn't have worked either, because the records I was updating didn't have anything obviously in common.
Post Reply