MySQL/PHP update query error

Question

I'm running a query to update a small group of 'items' in a table, from PHP. Running the code with "Sequel Pro" executes it perfectly, while running it on PHP using mysql("query here"); fails miserably.

Is there anything wrong with my query?

UPDATE `service_joblocation` 
   SET  `in_use` =  '1', 
        `in_use_since` =  '1283488686', 
        `in_use_currentcount` =  `in_use_currentcount`+1, 
        `in_use_historicalcount`= `in_use_historicalcount`+1 
  WHERE `id` = 5 
  LIMIT 1;

UPDATE `service_joblocation` 
   SET `in_use` =  '1', 
       `in_use_since` =  '1283488686', 
       `in_use_currentcount` = `in_use_currentcount`+1, 
       `in_use_historicalcount` = `in_use_historicalcount`+1 
 WHERE `id`=16 
  LIMIT 1;

UPDATE `service_joblocation` 
   SET  `in_use` =  '1', 
        `in_use_since` = '1283488686', 
        `in_use_currentcount` = `in_use_currentcount`+1, 
        `in_use_historicalcount` = `in_use_historicalcount`+1 
  WHERE `id`=18 
   LIMIT 1;

UPDATE `service_items` SET  `checkin_user`='9', `checkin_date`='1283488686', `location`='5' WHERE `id`=576;
UPDATE `service_items` SET  `checkin_user`='9', `checkin_date`='1283488686', `location`='16' WHERE `id`=577;
UPDATE `service_items` SET  `checkin_user`='9', `checkin_date`='1283488686', `location`='18' WHERE `id`=578;
UPDATE `service_jobs` SET `checkin_date`='1283488686', `checkin_user`='9',`checkin_department`='1',`checkin_client_person`='0', `items_x`=`items_x`+1 WHERE `id`='518' LIMIT 1;
UPDATE `service_jobs` SET `checkin_date`='1283488686', `checkin_user`='9',`checkin_department`='1',`checkin_client_person`='0', `items_x`=`items_x`+1 WHERE `id`='518' LIMIT 1;
UPDATE `service_jobs` SET `checkin_date`='1283488686', `checkin_user`='9',`checkin_department`='1',`checkin_client_person`='0', `items_x`=`items_x`+1 WHERE `id`='518' LIMIT 1;

This is the output message...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE service_joblocation SET in_use = '1', in_use_since = '1283488686' at line 2

Are there duplicate service_joblocation.id values? If not, there's no value to having LIMIT 1 at the end... And why did you include additional UPDATE statements that the error doesn't relate to? — OMG Ponies
– OMG Ponies, Commented Sep 3, 2010 at 4:55
Your queries look OK to me. The problem probably lies in your PHP code. Could you post that too? — Mischa
– Mischa, Commented Sep 3, 2010 at 4:59

VolkerK · Accepted Answer · 2010-09-03 05:21:11Z

2

You can only pass a single statement to mysql_query().

There are other functions/methods like e.g. mysqli::multi_query() but not for the old mysql extension.

answered Sep 3, 2010 at 5:21

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ajdi Over a year ago

Cheers :D I was freaking out because the query works fine in the application.. I guess it must pass each query separately.

Vasiliy Kulakov · Accepted Answer · 2010-09-03 04:55:59Z

0

Can you post the PHP code which executes the query (I assume you meant mysql_query()). The query looks fine to me at a glance, but I think it may be preceded by something that is incorrect, such as an unintentional quotation mark or a brace.

answered Sep 3, 2010 at 4:55

Vasiliy Kulakov

5,4811 gold badge23 silver badges16 bronze badges

Comments

aleksikallio · Accepted Answer · 2010-09-03 05:26:45Z

0

What are the data types of service_joblocation.in_use and service_joblocation.in_use_since? if they are numbers, try removing the quotation marks, i.e.

UPDATE `service_joblocation` 
SET  `in_use` =  1, 
     `in_use_since` =  1283488686, 
     `in_use_currentcount` =  `in_use_currentcount`+1, 
     `in_use_historicalcount`= `in_use_historicalcount`+1 
WHERE `id` = 5 
LIMIT 1;

answered Sep 3, 2010 at 5:26

aleksikallio

1,9221 gold badge19 silver badges32 bronze badges

Collectives™ on Stack Overflow

MySQL/PHP update query error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related