margaux Posted March 29, 2012 Share Posted March 29, 2012 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? Link to comment Share on other sites More sharing options...
Larry Posted March 30, 2012 Share Posted March 30, 2012 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. Link to comment Share on other sites More sharing options...
ericp Posted January 30, 2014 Share Posted January 30, 2014 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. Link to comment Share on other sites More sharing options...
Larry Posted February 4, 2014 Share Posted February 4, 2014 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. Link to comment Share on other sites More sharing options...
Recommended Posts