PHP/MySQL - updateing 2 tables in one request

Question

Morning, I want to learn more about sql and I'm wanting to update to tables;

$query3 = "INSERT INTO `$table1`, `$table2` ($table1.DISPLAY_NAME, $table1.EMAIL_ACCOUNT, $table2.DISPLAY_NAME, $table2.EMAIL_ACCOUNT) values ('" . DISPLAY_NAME . "', '" . EMAIL_ADDRESS . "', '" . $get['rn'] . "', '" . $email . "')";

could some one point me in the right direction on how I would go about this? current error is

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 ' contacts_ACT_Web_Designs (contacts_E_Jackson.DISPLAY_NAME, contacts_E_Jackson' at line 1

regards, phil

Answer 1

Are you sure a MySQL INSERT statement can insert into two tables? I have never heard of being able to insert that way with any database. Did you try two separate insert statements (one for each table)?

Answer 2

In MуSQL you can insert records only into one table. At the same time, mysql_query() does not support multiple queries. So you should split your insertion into two requests and execute them one by one.

Answer 3

MySQL cannot insert into two tables at once, so you have to split your query into two separate ones. If you need them both to be inserted together (if one fails the other will not get inserted) then you can use transactions.

Basically you start a transaction run the queries if anything goes wrong you do a rollback if everything goes well you commit the transaction.

Also as far as I remember your engine must be InnoDB for transactions to be available.