How can I update two tables in one query?

Question

There is any way to update two tables in one query? Below is an example of my code. How can I put those two update queries in one? Thank you in advance!

   <?php
// DATABASE UPDATE
   if (isset($_POST['submit']) or isset($_GET['submit'])){

// 1st QUERY
   $db =& JFactory::getDBO();
   $query_1 = "UPDATE table_1
                  SET name     = '".$_POST["name"]."',
                      surename = '".$_POST["surename"]."'
               WHERE id=1";
   $db->setQuery($query_1);
   $db->query();

// 2nd QUERY
   $db =& JFactory::getDBO();
   $query_2 = "UPDATE table_2
                  SET team_id   = '".$_POST["team_id"]."',
                  SET team_name = '".$_POST["team_name"]."'
                  ";
   $db->setQuery($query_2);
   $db->query(); } ?>

Seriously, please **stop** whatever you're doing and read up on [proper SQL escaping](http://bobby-tables.com/php). What you're doing here is hazardously, career damagingly bad. — tadman, Jan 15 '13 at 21:27
Where is the WHERE condition in your second query. The it is written now will update all records in that table. — Mike Brant, Jan 15 '13 at 21:27
This is not an academic concern. Look at what [automatic hacking tools](http://sqlmap.org/) can do to your site. Please take this **seriously**. If you have this on the public internet, it is only a matter of time before your entire site is destroyed. I don't claim to know everything, but I do know enough about the risks of not properly escaping data. — tadman, Jan 15 '13 at 21:40
@tadman: OK I got the point... Would you have the kindness to explain what exactly is the problem on this part of code that it's just an example? — DNA180, Jan 15 '13 at 21:43
@DNA180 google 'SQL Injection'. The way your code is written now, you have no protection. Anyone could easily read your entire database, or change your entire database, or drop your entire database. — glomad, Jan 15 '13 at 21:46
@DNA180: Please start with http://bobby-tables.com/about . Short version is that you are building executable code with untrusted data that comes from outside your program. — Andy Lester, Jan 15 '13 at 21:46
Putting `$_POST` data directly in your query is extremely risky and should never be done. You must, at the very least, use the proper escaping function. In particular you should read the [Joomla documentation on constructing queries](http://docs.joomla.org/Secure_coding_guidelines#Constructing_SQL_queries) and note the use of the `$db->quote` function. — tadman, Jan 15 '13 at 21:46
@tadman: OK, now that my head is clear we can have a clear conversation!!! First of all, thank you for the links. They are very useful. Second, this form that I am making, can be shown only by site's administrator. I am still in danger? And if `$_POST` is risky, what you prefer instead of this? Give me an example. I found this answer here in stackoverflow, it didn't came out of my mind. Take a look [here](http://stackoverflow.com/questions/14296916/how-to-update-values-in-my-db-with-new-values-from-select-and-input-tags). Thank you for tour time! — DNA180, Jan 16 '13 at 08:13
`$_POST` is risky by design but the way you make it safe is by using the appropriate escape call or by employing [SQL database placeholders](http://bobby-tables.com/php) as in PDO or `mysqli`. If you skip this step, you will get into serious trouble. It's not just that the site administrator won't be hostile, but entering names like "O'Malley" will crash your application. Escaping data is *mandatory* for any application. If you develop bad habits like not escaping data you will set yourself up for failure. — tadman, Jan 16 '13 at 15:29
OK I got it, I will read all those links you gave me and I will generally search on google about it. Thank you very much!!! — DNA180, Jan 16 '13 at 15:45

score 5 · Accepted Answer · answered Jan 15 '13 at 21:36

5

MySQL does actually allow updates to multiple tables in a single query (although often it makes sense for your application to do one at a time).

UPDATE table_1, table_2
SET table_1.field = <some value>, table_2.field = <some value>
WHERE table_1.field2 = table_2.field_2 
AND table_1.field_3 = <some other value>

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

As other people have stated, you should look at using prepared statements.

answered Jan 15 '13 at 21:36

dethtron5000

10,363
1
31
32

I am really new on this stuff I am sorry!!! What exactly are those prepared statements? Thank you for your answer!!! – DNA180 Jan 15 '13 at 21:40
I'm not sure what library you are using, but for PDO the PHP documentation is pretty good: http://php.net/manual/en/pdo.prepare.php. Basically they help prevent execution of malicious queries in your database application. – dethtron5000 Jan 15 '13 at 21:43

score 1 · Answer 2 · answered Jan 15 '13 at 21:36

Use a transaction, only the InnoDB engine supports it.

mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

{First Insert here}

{Second Insert Here}

mysql> commit; Query OK, 0 rows affected (0.00 sec)

Note that you have to write commit, because if you don't it wont flush it to the database.

How can I update two tables in one query?

2 Answers2

Linked