php mysql insert to second table

BEFOR

To do a second table insert 'atable' after the first 'mtable' I believe LAST_INSERT_ID() is used. ver 4

Main insert:

$send = mysql_query("INSERT INTO mtable SET m_name='$name', enter=now()");}

auto increment primary id's are mtable.mid, atable.aid

Is the second insert formed as part to the same continuous string?

Does it require a second statement after the first has been accomplished?

Is there a way to prevent "breakage" -- meaning that, say only the first table gets inserted to in the event of a server glitch or whatnot.

bradgrafelman

I don't think I understand what your question is. Are you trying to insert data into two different tables, and want to use the primary ID generated from the first table in the INSERT into the second table? If so, I'd say use [man]mysql_isnert_id/man to grab the ID value and use it in the subsequent insert query/queries.

BEFOR wrote:
Is there a way to prevent "breakage" -- meaning that say only the first table gets inserted to in the event of a server glitch or whatnot.

Aye - look into using transactions. See the MySQL manual page: 12.3. MySQL Transactional and Locking Statements.

BEFOR

Are you trying to insert data into two different tables, and want to use the primary ID generated from the first table in the INSERT into the second table? If so, I'd say use mysql_insertt_id() to grab the ID value and use it in the subsequent insert query/queries.

yes, and so it is mysql_insert_id()
(although I could have provided the key column too).

look into using transactions. See the MySQL manual page: 12.3. MySQL Transactional and Locking Statements.

But I see thats mysql 5.5 and I'm on 4 for other reasons.

So I'll either have to try mysql_insert_id() , or do a layout having "more complete" tables with possibly redundant columns, or stuff everything into a single table.

Maybe I can live without the rollback -- these are for basic survey data.

I did find another way which claims a rollback -- but that too uses php 5 with $querylog

For inserting the PID into second table:

Also came across this example at http://php.net/manual/en/function.mysql-insert-id.php and I can't see why it wouldn't work as long as it is done before the main insert.

<?php
function get_current_insert_id($table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $table"; 
    return mysql_num_rows(mysql_query($q)) + 1;
}
?>

BEFOR

(sorry for double post)

But- since this is for #rows I'll forget it. #rows rarely coincides with primary id on my tables.

Now looking at SELECT MAX to pull the last primary id instead then add 1.

bradgrafelman

BEFOR;10972341 wrote:
Now looking at SELECT MAX to pull the last primary id instead then add 1.

That is exactly what you should not do. Google terms such as "DB race condition" and you'll learn why.

BEFOR wrote:
But I see thats mysql 5.5 and I'm on 4 for other reasons.

Uh, so? Transactions have been available since MySQL 4 (not sure about 3.23). I just assume MySQL 5 nowadays due to security/feature enhancements that have been made.

BEFOR

According to documentation, mysql_insert_id() acts to get the 'ID generated for an AUTO_INCREMENT column' . Specifically is this the row id or can I get the primary id specified?

For some greater focus on the 2 table statement:

function begin()
function committ()
function rollback()

Is Lock still necessary?

I'm wondering if both inserts for the first and second table (having mysql_insert_id()) would be inserted at a single begin function.

Or, whether the first insert must complete independently for getting the mysql_insert_id() and/or whether the second must somehow be nested within the committ/rollback?