Last_Insert_Id, Stored Procedures

[margaux]

I have a couple of questions:

 

I need to insert data from a form into several different tables which are linked with foreign keys. If I set up the queries as individual transactions. They work fine on their own but I am trying to link them to use last_insert_id(). I've tried

$q = "INSERT INTO fbpages (pub_num, url, likes, talkingAbout, wereHere, noOfAdmins, otherUrl) VALUES ('$pnum', '$url', $l, $ta, $wh, $noa, '$other');
SELECT LAST_INSERT_ID() INTO fbpid;
INSERT INTO pubs (pub_num, pubname, manager, email, address1, address2, city, county, postcode, phone, fbpage_id)
VALUES ('$pnum', '$pname', '$man', '$pem', '$a1', '$a2', '$city', '$c', '$pc', '$ph', fbpid);
SELECT LAST_INSERT_ID() INTO pid;
UPDATE users SET firstname='$fn', surname='$sn', pub_id=pid, bdm='$bdm', date_modified=NOW() WHERE id=$uid";

which gives a1064 error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT LAST_INSERT_ID() INTO fbpid; INSERT INTO pubs (pub_num, pubname, manager,' etc

 

I've got it working by using

if (mysqli_query($dbc,$q1)) {
$fbpid = mysqli_insert_id($dbc);

Is last_insert_id imore efficient?

 

2. I was thinking of making this a stored procedure as there a number of inserts and updates.If I did use a stored procedure for these transactions how would I get the last_insert_ids into variables/session variables that other pages could access?

[Larry]

You can't execute multiple queries through mysqli_query(). You can use mysql_multi_query().

 

I don't know if last_insert_id() is more or less efficient than mysqli_insert_id(). If I'm in PHP, I use mysqli_insert_id().

 

This would definitely be a candidate for a stored procedure. If your PHP script needed some results from the stored procedure, then you could use an outbound parameter to do that.

[ericp]

Hi Larry cc all,

 

1/ Regarding to the script 17.7, which uses the mysqli_insert_id() function when the new thread/ subject is created. My question is why should we apply this function for the $tid variable in this scenario - if(!$tid){...} - instead of echoing/ printing a message informing the users that the new subject is successfully created? because when the new subject/record is inserted into the threads table, the new thread_id will be automatically generated as the next highest interger, and inserted into the AUTO_INCREMENT field for this one, which should be greater than zero that satisfies the thread ID validation condition (..., FILTER_VALIDATE_INT, array('min_range' => 1) )

 

2/ Regarding to the issue of multiple query execution, suppose that I seperate the threads table columns into two tables like this:

 

a/ subjects table:

$query = 'CREATE TABLE subjects (
            subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            lang_id TINYINT(3) UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            subject VARCHAR(150) NOT NULL,
            PRIMARY KEY (subject_id),
            INDEX (lang_id),
            INDEX (user_id)
            ) ENGINE = MYISAM';

== =

b/ messages table:

 

$query = 'CREATE TABLE messages (
            messages_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            subject_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            message TEXT NOT NULL,
            posted_on DATETIME NOT NULL,
            PRIMARY KEY (messages_id),
            INDEX (subject_id)
            ) ENGINE = MYISAM';

 

And assume that the other two tables of users and languages remained unchanged.

 

=> Now, I execute multiple queries to insert values for these two tables at the same time, which I go like this:

if (!sid) {

        $q = "INSERT INTO subjects (lang_id, user_id, subject)

                VALUES ({$_SESSION['lid']}, {$_SESSION['user_id']}, '" . mysqli_real_escape_string($dbc, $subject) . " ');";

 

       $q .="INSERT INTO messages (subject_id, user_id, message, posted_on)

                VALUES ({$sid, {$_SESSION['user_id']}, ' " . mysqli_real_escape_string($dbc, $body) . " ', UTC_TIMESTAMP() );";

 

$r = mysqli_multi_query($dbc, $q);
                if (mysqli_affected_rows($dbc) == 1) {
                    
                    echo '<p> Your new subject and message have been posted successfully</p>';
                    
                } else {
                    echo '<p> Your new subject and message could not be handled because of system error. </p>';
                }

} // end of if(!$sid)

 

Note: $sid variable stands for subject_id and $body variable stands for message.

 

I obmit the $sid = mysqli_insert_id($dbc); after the mysqli_affected_rows($dbc) == 1 because i am waiting for the discussions and answers for question# 1 above.

 

And I use the value $sid for the column subject_id of the messages table in the second query because I think that it goes after the first query for the subjects table, of which the new result/row has just been affected and inserted.

 

Sorry but am I correct?

 

I am expecting all of your ideas and discussion.

 

Thanks in advance.

[Larry]

The $tid is needed for inserting the post into the posts table. As for your second question, I'm not sure I'm following you, because without the value of $sid, the second query would fail. 

 

You could, in theory, use multi_query() and also call the MySQL LAST_INSERT_ID() query within there to make all this work, but it's much more complicated.