How to use multiple inserts with transactions in mysql

[tomjohnson]

Hello everyone,

I have a bit of a problem. I am in the middle of developing a site that needs to use transactions. I have several different tables that comprise a single insert form. Table A, B and C all require transactions.

From what I understand, mysql_query does not support multiple queries. I have several problems that I will list.

1. autocommit is turned off and my script needs to issue "COMMIT"
2. mysql_insert_id() will NOT get the last id until the COMMIT has been issued so returns false.

Basically, I cannot insert data into table B without first knowing the last_id from table A.

I can't get around how to accomplish this.

Can someone please give a hand?

Thanks,

Tom

[somedude3488]

put the results from a query from table A into an array and get the total number of results using to count() function. take that number and subtract 1 from it. put that number here:

ex.
$array = $array[$number];

then use that variable in table B's query

did you try to insert empty record then assign it

for example:
$RecID = '1001';

$Table1 = "your sql code where ID=$RecID";
mysql_query($Table1);
$Table2 = ....
...
$Table3 = ....
....

on error, delete the record

i do in this way.

[tomjohnson]

Hey guys,

Thanks for the response.. These are really good ideas and I am going to try and implement them. I will post back and let you huys know how it went. :)