Member Avatar for Ragnarok

I have been looking into ways I to cut down on MySQL Querys in a page and I was wandering if you could up to update querys into one mysql_query function like this:

mysql_query("UPDATE table SET val1=val1+1, val2=val2+1 WHERE id = (int); UPDATE table SET val3=val3+1, val4=val4+1 WHERE id = (int)") or die(mysql_query());

*Note: both the (int) values are different.

  • 4 Contributors
  • 4 Replies
  • 116 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by JeniF

Dani AI

Generated

Short answer: you can't reliably send two separate SQL statements joined with a semicolon to the old mysql_query() call. As pointed out, that extension expects a single statement per call. For the scenario describes (several different fields on several different rows) there are cleaner, safer ways to do it in one round trip without stacking separate UPDATEs.

A common single-statement approach is to use CASE for each column you need to change. Example pattern:

UPDATE my_table
SET
  val1 = CASE id
    WHEN 101 THEN val1 + 1
    WHEN 102 THEN val1 + 2
    ELSE val1 END,
  val2 = CASE id
    WHEN 101 THEN val2 + 3
    WHEN 102 THEN val2 + 4
    ELSE val2 END
WHERE id IN (101, 102);

That updates multiple rows in one statement and keeps the operation atomic. If you truly must update multiple tables at once, a multi-table UPDATE with proper JOIN clauses can do it — as hinted — but it requires careful join conditions so you don't accidentally create a Cartesian update.

Modern advice: move off the old mysql_* extension. Use mysqli or PDO. mysqli offers multi_query() to send several statements, but it raises security concerns (increased SQL injection risk) and adds complexity reading multiple result sets. A better route is either a single SQL statement (CASE/INSERT ... ON DUPLICATE KEY UPDATE) or executing multiple prepared statements inside a transaction so all updates succeed or fail together. See the PHP docs for mysqli::multi_query and PDO::prepare for patterns and examples.

Practical tips: always validate/cast IDs (avoid string concatenation), prefer prepared statements, measure whether the single-statement approach improves performance in your environment, and follow 's pointer to check driver error messages when something goes wrong.

Recommended Answers

All 4 Replies

Member Avatar for Killer_Typo

not sure about the update thing, but in your or die statment you should change it to

or die(mysql_error());

that way you can tell what the error might be that your recieving.

also i dont see a problem with sending various mysql query's its a good way to keep things from getting messy on a single line.

Member Avatar for Ragnarok

it does become a problem when you have to update 5 or 6 different fields on 5 or 6 different rows when you also have a few selects and an inset into as well

Member Avatar for mugur

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server (see http://www.php.net/mysql_query for more info)

commented: no reason to bump a three year old thread. +6
Member Avatar for JeniF

try something like this:

mysql_query("UPDATE table1,table2 SET table1.val1=val1+1, table1.val2=val2+1 , table2.val3=val3+1, table2.val4=val4+1 WHERE id = (int)") or die(mysql_error());

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.