Writing to DB Master and Detail tables. Is this ok?

Scriptcase 8 Discussion
#1

Is it safe for me to use the LAST_INSERT_ID() function of mysql when writing to a 2nd table?

Or do I need to use a transaction or read that into my own variable that I use ?

I am just wondering if LAST_INSER_ID() could possibly pick up a value from another user if running the same application at the same time?

Thanks for any input!


// Pick Master
$insert_table  = 'PickHeader';     
$insert_fields = array(  
     'SONum' => $rs[$x][0],
     'PickBatchID' => "'1'",
 );
// Insert record
$insert_sql = 'INSERT INTO ' . $insert_table
    . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
    . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';
sc_exec_sql($insert_sql);

// Pick Details    
$insert_table  = 'PickDetail';    
$insert_fields = array(  
     'PickID' => "LAST_INSERT_ID()",  // <--  IS THIS OK?
     'ItemCode' => "'".{ItemCode}."'",
        'Qty' => $rs[$x][2],
        'BinLocation' => "'".{BinLocation}."'",
     'User' => "'USR'",
     'Warehouse' => "'000'",
 );

// Insert record
$insert_sql = 'INSERT INTO ' . $insert_table
    . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
    . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';

sc_exec_sql($insert_sql);
#2

Thanks @alvagar

#3

Ok I know this is months later now… but how would I get access to that value back in PHP again? The database knows the last inserted ID… how can I get that back in my PHP event so I can use it?

#4

You can use a SC macro for that. Maybe: sc_lookup(rs, “SELECT LAST_INSERT_ID()”);