how to retrieve id from first insert in order to use in second insert

Question

Below I have an INSERT database function using php and mysqli:

for ($i = 1, $n = $_SESSION['sessionNum']; $i <= $n; $i++) {

        $insertsql = "
  INSERT INTO Session
    (SessionName, SessionTime, SessionDate, SessionWeight, SessionDuration, TotalMarks, ModuleId, TeacherId, Room)
  VALUES
    (?, ?, ?, ?, ?, ?, ?, ?, ?)
";
if (!$insert = $mysqli->prepare($insertsql)) {
  // Handle errors with prepare operation here
}

    $sessname = $_SESSION['id'] . ($n == 1 ? '' : $i);
    $sessdate = date("Y-m-d", strtotime($_SESSION['dateChosen']));

    $insert->bind_param("sssisiiis", $sessname, $_SESSION['timeChosen'], $sessdate,
                 $_SESSION['totalWeight'], $time, $_SESSION['textMarks'],
                 $_SESSION['hiddenmodule'], $teacherid, $_SESSION['rooms']);

    $insert->execute();

    if ($insert->errno) {
      // Handle query error here
    }

    $insert->close();

}

The above code will insert this data in the database below as an example in one go:

SessionId (Auto) SessionName  SessionTime  //etc
1                ADFGR1       01:00:00
2                ADFGR2       13:00:00
3                ADFGR2       09:00:00

Anyway you can see I have not include SessionId in the code above as it is an auto increment meaning that it is going to be displayed anyway.

But the problem I have is that underneath this insert, what I want to do is that when the above insert is finished, I want to do another insert underneath:

        $insertsession = "
  INSERT INTO Session_Complete
    (SessionId)
  VALUES
    (?)
";
if (!insertdb = $mysqli->prepare($insertsession)) {
  // Handle errors with prepare operation here
}


    insertdb->bind_param("i",$value);

    insertdb->execute();

    if (insertdb->errno) {
      // Handle query error here
    }

But the problem I have with the above insert is that it requires the already inserted SessionId's from the first insert in order to insert those ids into its table. My question is how do I retrieve the SessionId from the first insert so I can include them in the second insert?

score 2 · Answer 1 · answered Jan 03 '13 at 09:14

2

if you want, you can use TRIGGER on this, eg.

delimiter $$
CREATE TRIGGER `trigger_Name` 
  AFTER INSERT ON `Session`
FOR EACH ROW 
BEGIN
  INSERT INTO Session_Complete (SessionId) values (LAST_INSERT_ID());
END$$
delimiter ;

What the trigger does is after a successful insert on table Session, it will also insert the last inserted ID on table Session_Complete.

answered Jan 03 '13 at 09:14

John Woo

258,903
69
498
492

Wow. You are like our DBA who would actually prefer a `SELECT httpResponse( requestURI )` :) kidding, this is a fine solution as long as the OP has the privileges to create triggers. – Michel Feldheim Jan 03 '13 at 09:22

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

Have a look at How do I get the last inserted ID of a MySQL table in PHP? to get the last insert ID after the insert to Session and use that value in your following insert into Session_Complete.

edited May 23 '17 at 11:48

Community

1
1

answered Jan 03 '13 at 09:15

andyb

43,435
12
121
150

score 1 · Accepted Answer · answered Jan 03 '13 at 09:15

1

it should be this:

$insert->insert_id;

answered Jan 03 '13 at 09:15

Perry

11,172
2
27
37

so do I put something like `$lastID = $insert->insert_id;` and then store `$lastID` in the bind_param() for the second insert? – user1830984 Jan 03 '13 at 09:20

score 0 · Answer 4 · edited Jan 03 '13 at 09:25

0

Use the MySQL function LAST_INSERT_ID().

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.

edited Jan 03 '13 at 09:25

Michel Feldheim

17,625
5
60
77

answered Jan 03 '13 at 09:13

kmkaplan

18,655
4
51
65

score 0 · Answer 5 · answered Jan 03 '13 at 09:15

Like with other tables in your other questions, Session_Complete looks like another excessive needless table.
Whatever info you want from this table, you can get from Session already.
So, no underneath inserts at all.

It seems you need to revise your database structure or - even better - post it here as a separate question and get a good advise on how to organize it properly.

how to retrieve id from first insert in order to use in second insert

5 Answers5