php - mysqli_insert_id 因 mysqli_multi_query 而失败

Question

项目:

跨多个相关表的重复行数据。

问题:

在 php 中，在使用第一个 mysqli_multi_query 后，我似乎无法从 mysqli_insert_id 获得 id 结果。

状态:

我已经使用 phpmyadmin 成功查询了以下内容(手动将 unit_id 替换为 1 并将 $unit_id1 替换为 61(下一个对应的)):

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

注意:第一个 multi_query 成功复制了第一个表...第二个 multi_query 取决于 mysqli_insert_id 结果。

$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)); 

Answer 1

最后，经过大量测试，我发现成功获取新行的 unit_id 的唯一方法是将第一个 mysqli_multi_query 分成单独的 mysqli_query.

即使在这样做之后，我仍然发现我遇到了解析错误，所以我将 mysqli_insert_id 直接移动到 INSERT 查询下方。

现在我已经能够获得新的 unit_id，我能够为下一个复制表成功运行 mysqli_multi_query。 但是，我遇到了同样的问题，包括要复制的剩余表，所以我最终发现我必须将所有 mysqli_multi_query 分成单独的 mysqli_query .

请参阅下面的工作解决方案:
注意:
ID_TABLE 在包含的 config.php 文件中定义(此处未显示)。这是一个标题为 unit_genData
的表*$id* 是一个 var，表示要复制的选定/选中行的初始 unit_id

            $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);