mysqli_insert_id failed with mysqli_multi_query

Question

Project:

Duplicate row data across multiple related tables.

Problem:

In php, I don't seemed to be able to get the id result from mysqli_insert_id after using the 1st mysqli_multi_query.

Status:

I've successfully queried the following using phpmyadmin (manually replacing unit_id with 1 and $unit_id1 with 61(the next corresponding)):

PHPMYADMIN

CREATE TEMPORARY TABLE  tmp
                      SELECT            `unit_id`,
                                        `title`,
                                        `status_id`,
                                        `category_id`,
                                        `tags`,
                                        `access_id`
                      FROM              unit_genData
                      WHERE             `unit_id` = 1;# 1 row affected.


                      ALTER TABLE       tmp 
                      DROP COLUMN       `unit_id`;# 1 row affected.


                      UPDATE            tmp 
                      SET               `title` = 'DUPLICATE';# 1 row affected.


                      INSERT INTO       unit_genData 
                      SELECT            0,tmp.* 
                      FROM              tmp;# 1 row affected.

                      DROP TABLE        tmp;# MySQL returned an empty result set (i.e. zero rows).


CREATE TEMPORARY TABLE  tmp 
                      SELECT            `ad_id`,
                                        `unit_id`,
                                        `ad_title`,
                                        `ad_image`,
                                        `ad_img_caption`,
                                        `ad_brief_desc`,
                                        `ad_btn_text`
                      FROM              unit_promoContent 
                      WHERE             `unit_id`=1;# 1 row affected.


                      ALTER TABLE       tmp 
                      DROP COLUMN       `ad_id`;# 1 row affected.


                      UPDATE            tmp 
                      SET               `unit_id` = 61;# 1 row affected.


                      INSERT INTO       unit_promoContent 
                      SELECT            0,tmp.* 
                      FROM              tmp;# 1 row affected.

                      DROP TABLE        tmp;# MySQL returned an empty result set (i.e. zero rows).

PHP

Note: the first multi_query duplicates the first table successfully...the 2nd multi_query is dependent on the mysqli_insert_id result.

$sql1 = "CREATE TEMPORARY TABLE tmp
                      SELECT            `unit_id`,
                                        `title`,
                                        `status_id`,
                                        `category_id`,
                                        `tags`,
                                        `access_id`
                      FROM              ".ID_TABLE."
                      WHERE             `unit_id` = " . $id . ";

                      ALTER TABLE       tmp 
                      DROP COLUMN       `unit_id`;

                      UPDATE            tmp 
                      SET               `title` = 'DUPLICATE';

                      INSERT INTO       ".ID_TABLE." 
                      SELECT            0,tmp.* 
                      FROM              tmp;
                      DROP TABLE        tmp;
                     ";
              $result = mysqli_multi_query($dbc,$sql1) 
                      or die(mysqli_error($sql1)); 

                $unit_id1 = mysqli_insert_id($dbc);                     // Store new unit_id as var                                                                                                 // Tab 2 :: Promo Content    
              $sql2 = "CREATE TEMPORARY TABLE   tmp 
                      SELECT            `ad_id`,
                                        `unit_id`,
                                        `ad_title`,
                                        `ad_image`,
                                        `ad_img_caption`,
                                        `ad_brief_desc`,
                                        `ad_btn_text`
                      FROM              unit_promoContent 
                      WHERE             `unit_id`=" . $id . ";


                      ALTER TABLE       tmp 
                      DROP COLUMN       `ad_id`;


                      UPDATE            tmp 
                      SET               `unit_id` = ". $unit_id1 .";


                      INSERT INTO       unit_promoContent 
                      SELECT            0,tmp.* 
                      FROM              tmp;
                      DROP TABLE        tmp;
                     ";

              $result = mysqli_multi_query($dbc,$sql2) 
                      or die(mysqli_error($sql2));

@Hanky웃Panky Edited the ajax to get the output from `mysqli_insert_id($dbc)` as you'd requested. `"Commands out of sync; you can't run this command now"` — cpardon, Nov 22 '14 at 12:41
Try running this code directly without ajax so you can debug the server end first. Could be something very small. Check whether that function returns false? — Hanky Panky, Nov 22 '14 at 12:46
@Hanky웃Panky So I ran another test using the following errorAlert function `function errorAlert(e, jqxhr) {alert("Your request was not successful: " + jqxhr);};`. The resulting error alert said `parseerror`. Note, I'm still getting the successful duplication of the 1st mysqli_multi_query. — cpardon, Nov 23 '14 at 11:06
@Hanky웃Panky just wanted to thank you for your suggestions. It means a lot to have someone to bounce these off of from time to time. I found the answer by separating the `mysqli_multi_query` into individual queries. Seemed like the only way I could grab the `mysqli_insert_id`. I've added the answer I managed to get to work. — cpardon, Nov 23 '14 at 12:13
You're most welcome :) Your question and answer present a use case which can be very helpful to future readers working on multi queries; therefore i have upvoted both of them :) — Hanky Panky, Nov 23 '14 at 16:56

cpardon · Accepted Answer · 2014-11-25T14:33:54.363

In the end, after a loads of testing, I found that the only way to successfully get the unit_id of the new row was to separate the 1st mysqli_multi_query into individual mysqli_query.

Even after doing this, I still found that I was getting a parseerror, so I moved mysqli_insert_id directly below the INSERT query.

Now that I've been able to get the new unit_id, I was able to successfully run mysqli_multi_query for the next duplicated table. However, I ran into the same issue with including the remaining tables to duplicate, so I finally found I had to separate all mysqli_multi_query into individual mysqli_query.

See the working solution below:
NOTE:
ID_TABLE is defined in the included config.php file (not shown here). It is a table titled unit_genData
*$id* is a var that represents the initial unit_id of the selected/checked row to be duplicated

            $sql1   = "CREATE TEMPORARY TABLE   tmp
                      SELECT            `unit_id`,
                                        `title`,
                                        `status_id`,
                                        `category_id`,
                                        `tags`,
                                        `access_id`
                      FROM              ".ID_TABLE."
                      WHERE             `unit_id` = " . $id . "";
            $result = mysqli_query($dbc,$sql1) or die(mysqli_error($dbc));        
            $sql2   = "ALTER TABLE      tmp 
                      DROP COLUMN       `unit_id`";
            $result = mysqli_query($dbc,$sql2) or die(mysqli_error($dbc));
            $sql3   = "UPDATE           tmp 
                      SET               `title` = 'DUPLICATE'";
            $result = mysqli_query($dbc,$sql3) or die(mysqli_error($dbc));  
            $sql4   = "INSERT INTO      ".ID_TABLE." 
                      SELECT            0,tmp.* 
                      FROM              tmp";
            $result = mysqli_query($dbc,$sql4) or die(mysqli_error($dbc));  

        $unit_id1   = mysqli_insert_id($dbc);   //$dbc->insert_id;  // Store new unit_id as var

            $sql5   = "DROP TABLE       tmp;";
            $result = mysqli_query($dbc,$sql5) or die(mysqli_error($dbc));

            $data       = "Insert_id for TABLE 1: ".$unit_id1.". ";
                                                                    // Duplicate Table for Tab 2
                                                                    // Promo Content
            $sql6   = "CREATE TEMPORARY TABLE   tmp 
                      SELECT            `ad_id`,
                                        `unit_id`,
                                        `ad_title`,
                                        `ad_image`,
                                        `ad_img_caption`,
                                        `ad_brief_desc`,
                                        `ad_btn_text`
                      FROM              unit_promoContent 
                      WHERE             `unit_id`=" . $id . ";
            $result = mysqli_query($dbc,$sql6) or die(mysqli_error($dbc));  


            $sql7   = "ALTER TABLE      tmp 
                      DROP COLUMN       `ad_id`";
            $result = mysqli_query($dbc,$sql7) or die(mysqli_error($dbc));  


            $sql8   = "UPDATE           tmp 
                      SET               `unit_id` = ". $unit_id1 .";
            $result = mysqli_query($dbc,$sql8) or die(mysqli_error($dbc));


            $sql9   = "INSERT INTO      unit_promoContent 
                      SELECT            0,tmp.* 
                      FROM              tmp;";
            $result = mysqli_query($dbc,$sql9) or die(mysqli_error($dbc));
            $sql10  = "DROP TABLE       tmp;";
            $result = mysqli_query($dbc,$sql10) or die(mysqli_error($dbc));

            # ======================================================= #
            #...and so on...for the rest of the tables to duplicate...#
            # ======================================================= #

    mysqli_close($dbc);

mysqli_insert_id failed with mysqli_multi_query

Project:

Problem:

Status:

PHPMYADMIN

PHP

1 Answers1