如何在连接值上选择最后插入的ID

Question

I'm trying to get the last inserted id of multiple inserted rows.

record_id is auto increment

$sql = "INSERT INTO records (record_id, user_id, status, x) values ";
         $varray = array();

        $rid = $row['record_id'];
        $uid =  $row['user_name'];
        $status =  $row['status'];
        $x =  $row['x'];

        $varray[] = "('$rid', '$uid', '$status', '$x')";

       $sql .= implode(',', $varray);

      mysql_query($sql); 

      $sql2 = "INSERT INTO status_logs (id, record_id, status_id, date, timestamp, notes, user_id, x) VALUES";

      $varray2[] = "(' ', mysql_insert_id(), '$status', '$uid', '$x')";

      $sql2 .= implode(',', $varray2);

       mysql_query($sql2); 

This is the result:

INSERT INTO records (record_id, user_id,  notes, x) values ('', '1237615', 'this is a note', 'active')

INSERT INTO status_logs (log_id, record_id, status_id, date, timestamp, notes, user_id, x) VALUES('', INSERT INTO records (record_id, user_id,  notes, x) values ('', '1237615', 'this is a note', 'active')

INSERT INTO status_logs (log_id, record_id, status_id, date, timestamp, notes, user_id, x) VALUES('', mysql_insert_id(), '1', '2013:05:16 00:00:01', '', this is a note'', '1237615', 'active'), '1', '2013:05:16 00:00:01', '', this is a note'', '1237615', 'active')

There is no value for mysql_insert_id().

Answer 1

You're mixing php function mysql_insert_id() and SQL INSERT statement syntax.

Either use MySQL function LAST_INSERT_ID() in VALUES clause of INSERT statement

INSERT INTO records (user_id,  notes, x) VALUES('1237615', 'this is a note', 'active');
INSERT INTO status_logs (record_id, status_id, date, timestamp, notes, user_id, x)
VALUES(LAST_INSERT_ID(), '1', ...);
      ^^^^^^^^^^^^^^^^^

or retrieve the last inserted id by making a separate call to mysql_insert_id() right after first mysql_query(). And then use that value when you as a parameter to your second query.

$sql = "INSERT INTO records (user_id, ...) 
        VALUES(...)";
$result = mysql_query($sql); 
if (!$result) {
    die('Invalid query: ' . mysql_error()); //TODO beter error handling
}
$last_id = mysql_insert_id();
//         ^^^^^^^^^^^^^^^^^^

$sql2 = "INSERT INTO status_logs (record_id, ...) 
         VALUES $last_id, ...)";
$result = mysql_query($sql); 
if (!$result) {
    die('Invalid query: ' . mysql_error()); //TODO beter error handling
}

Note:

• You don't need to specify auto_incremented column in column list. Just omit it.
• Use at least some sort of error handling in your code

On a side note: Instead of interpolating query strings and leaving it wide open to sql-injections consider to use prepared statements with either mysqli_* or PDO.