Multiple Insert in one transaction mysqli php5

Question

I have 3 tables: user, student, studentdetails

User's primary key is the foreign key for a field in table student (userid) and student's primary key is foreign key for a field in table studentdetails (studentid).

I need to insert data from one form to all 3 tables in one submit, following is the SQL script:

    $sql = "

    INSERT INTO `tbluser`(`username`, `password`, `roleid`, `datecreated`)
    VALUES ('$email','$password', '$role', CURRENT_TIMESTAMP);

    SELECT @uid := MAX(`userid`) FROM `tbluser`;

    INSERT INTO `tblstudent` (`userid`, `scholarnumber`, `firstname`, `middlename`,
    `lastname`, `datecreated`)
    VALUES ('@uid', '$scholar_no', '$first_name', '$middle_name', '$last_name',
    CURRENT_TIMESTAMP);


    SELECT @stuID :=  MAX(`studentid`) FROM `tblstudent`;

    INSERT INTO `tblstudentdetails` (`studentid`,`dob`, `studenttype`, `gender`,
    `religion`, `category`, `currentsuburbid`,   `currentcityid`, `currentaddress1`,
    `currentzipcode`, `currentstateid`,  `currentcountryid`,`mobile`,`phone1`,`email1`,
    `passportnum`, `permasuburbid`,  `permaaddress1`, `dateofjoining`,
    `admissionreferenceof`, `datecreated`, `dateupdated`) 

    VALUES ('@stuid', '$dob' ,'$studenttype' ,'$gender','$religion','$category',
    '$currentsuburbid', ' $currentcityid', '$currentaddress1', '$currentzipcode',
    '$currentstateid', '$currentcountryid', '$mobile',
    '$phone1','$email1','$passportnum','$permanentsuburbid', '$permanentaddress1',
    '$doj', ' $admissionreference',current_timestamp, current_timestamp);

    ";

I am not able to figure out the problem, the above script works in mysql (phpmyadmin) but in php it doesn't work. I understand, I need to use multi_query (??) which I am but it doesn't give any error and inserts in two tables, but doesn't in the third one. I feel it might be to do with the SELECT statement in between? At wits end here, I would greatly appreciate any help. Thanks heaps in advance.

Trying to execute multiple SQL statements isn't a good idea. Split the statements up into multiple inserts and execute them separately. — GordonM, Sep 19 '14 at 12:27
I would do separate SQL statements just to be able to have more control and error handling. But if you felt you had to do all this database work in one shot, create a stored procedure that does all this work. — Ed Manet, Sep 19 '14 at 12:30

score 1 · Answer 1 · answered Sep 19 '14 at 12:24

It looks like you're trying to run multiple SQL statements separated by semicolons from mysqli. That doesn't work. You need to issue each distinct statement separately.

You can use MySQL's transactions (as long as you're using InnoDB or some other access method for your tables, and not MyISAM: MyISAM doesn't handle transactions).

You'd do that as follows:

$connection->begin_transaction();
/* issue your statements one by one */
$connection->commit();

This will cause all your inserts, etc, to become visible simultaneously.

BUT: you're trying to make use of your most recent autoincrement ID numbers. You're doing this wrong. You need to use MySQL's LAST_INSERT_ID() function in place of your

SELECT @uid := MAX(`userid`) FROM `tbluser`;   /*wrong*/

pattern. This works because LAST_INSERT_ID() delivers the value of the ID from your first insert, so the second insert will use it. It's safe even if multiple programs are inserting things to the table because MySQL keeps a separate value for each program connection. It's faster than what you have because it doesn't have to look at the table, and return the value to your program before using it.

So do this and you'll get what you want.

/* do the first insert, using an autoincrementing uid column */
INSERT INTO `tbluser`(whatever, whatever, whatever)
              VALUES (whatever, whatever, whatever);
/* now LAST_INSERT_ID() contains the value inserted into tbluser.uid */

/* do the second insert, using the id from the first insert into tblstudent.userid */
INSERT INTO `tblstudent` (`userid`,         whatever, whatever, whatever)
                  VALUES (LAST_INSERT_ID(), whatever, whatever, whatever);
/* now LAST_INSERT_ID() contains the value inserted into tblstudend.studentid */

/* use that value to insert into tblstudentdetails.studentid */
INSERT INTO `tblstudentdetails` (`studentid`,      whatever, whatever, whatever) 
                         VALUES (LAST_INSERT_ID(), whatever, whatever, whatever);

thank you. I am using InnoDB and I was using last_insert_ID() but read somewhere that it may not always work, however, I have changed it now. Also, as suggested by Gordan & Ed, may I ask, if its a bad idea to use multi_query? I could change the code to sends one sql each time, but thought its better to let mysql do the work in one go and send the confirmation to php? Appreciate your help very much, thank you again! — Anx, Sep 19 '14 at 13:48
`LAST_INSERT_ID()` is very reliable as long as you don't edit your code to slip more `INSERT` statements between the ones depending on it. You can use `multi_query` if you like. But there's no baked-in transactional control in that API. — O. Jones, Sep 19 '14 at 15:26

Multiple Insert in one transaction mysqli php5

1 Answers1