insert into multiple tables at once?

[k0r54]

Hi,

Is it possible to insert into two or more tables at the same time?

I have come up with something like this, if its possible i dont think im to far off.

Any help will be great

INSERT INTO categories, categories_description('categories.categories_extid', 'categories.store_id', 'categories_description.language_id', 'categories_description.categories_name') VALUES ('1','5','1','rock')

Thanks
Adam

[ShrineDesigns]

you would have to run two queries, example// ...
mysql_query("BEGIN", $db); // begin transaction
$res1 = @mysql_query("INSERT INTO `tlb1` VALUES ('...')", $db);
$res2 = @mysql_query("INSERT INTO `tlb2` VALUES ('...')", $db);

if(!$res1 || !$res2)
{
@mysql_query("ROLLBACK", $db); // rollback the transcation as if it were never executed
}
@mysql_query("COMMIT", $db); // end transaction

[k0r54]

sorry, i dont quite understand the code, what do you mean by begin, rollback and commit?

Thanks
Adam

[Stephen Philbin]

They are transaction commands.

If you do

START TRANSACTION;

and then run your queries, they will be stored but will not take effect. until you use

COMMIT;

If there are any errors in any of the queries in the transaction, then the whole thing will automatically do a

ROLLBACK;

Which will erase the queries. In a transaction, all the queries either succeed or fail, you never have some succeed and some fail. If one fails, they all do. You can also use then to make inserts that are visible only to that connection too. Say if you start a transaction and insert some data, then running a select query to find that data will work on the same connection within the same transaction, but running the same query on another connection will not bring up the results you just inserted in the other connection on the transaction that has not yet been commited.

Transactions are a godsend for things like multiple select and inserts that must be made atomic, but last I checked, they only work on database tables using the InnoDB engine. ISAM and MyISAM tables will just silently ignore them, rather than spitting out an error.

The principals, purposes and how to use transactions are explained in the MySQL manual, I strongly suggest you have a good read of it. It's very helpful in usunig MySQL (as manuals tend to be).