如何使用php复制表1中的一行和表2中的多个相关行

Question

我有一张有车的桌子。每辆车都有一个或多个位于另一个表中的调优阶段。

在包含调优阶段的表中，有一个car标识符: cId = car id。

首先，我复制汽车并将新生成的id从mysql_insert_id()存储到$new_carId中。

然后，我根据汽车标识符cId获取所有相关的调优阶段，并执行与汽车相同的操作，但使用的是while()-loop。

现在，新复制的stages具有来自旧车的cId。

我现在需要用存储在$new_carId中的任何id替换cId，以便将复制的stages分配给新汽车。

在我看来，我有两个选择：

让它直接在INSERT-query中发生，或者

在while() UPDATE的末尾执行sql -loop。

到目前为止，我的代码如下：

/**
 *  Duplicate an existing car with associated tuning stage:
 *  Each car may have more than one associated tuning stage located in a separate table. 
 *  We need to make sure all stages is duplicated as well, and assigned to the new car.
 *
 *  [cId] car id.
 *
 *  debug() is a personal debugging function.
 */
    if (isset($_POST['duplicate_car'])){
    #   duplicate the car.
        $orgCar_id = (int)mysql_real_escape_string($_POST['orgCar_id']);  //  ID of the car we make the duplicate from.
        $sql_car = 'INSERT INTO TUNE_cars (make, model, chassis, motor, motorkode, orgEff, orgNm, year, options, note)
            SELECT make, model, chassis, motor, motorkode, orgEff, orgNm, year, options, note FROM TUNE_cars WHERE cId = '.$orgCar_id;
        //  debug($sql_car);
        $qry_car = mysql_query($sql_car);
        $new_carId = (int)mysql_real_escape_string(mysql_insert_id());  //  We need to attach this ID to the new duplicated tuning stages.
    //
    /**
     *  Duplicate any associated tuning stages:
     *  We need to fetch all stages associated with the old car,
     *  duplicate them aswell, and attach them to the new car. 
     *
     *  [sId] stage id
     *  [cId] car id. This connects the stages to a given car.
     */
        #   fetch all stages associated with the old car.
            $sql_stages = 'SELECT sId FROM TUNE_stages WHERE cId = '.$orgCar_id;
            //  debug($sql_stages);
            $qry_stages = mysql_query($sql_stages);
        //
        #   duplicate the stages.
            while($get_stage = mysql_fetch_assoc($qry_stages)){
                $sql_dup_stage = 'INSERT INTO TUNE_stages (cId, stage, stageHp, stageNm, stagePrice, stageMethod, stageDyno, sinfo, snote)
                SELECT '.$new_carId.', stage, stageHp, stageNm, stagePrice, stageMethod, stageDyno, sinfo, snote FROM TUNE_stages WHERE sId = '.$get_stage['sId'];
                //  debug($sql_dup_stage);
                $qry_dup_stage = mysql_query($sql_dup_stage);
            //
            }
        //
    /**/
    }
/**/

如您所见，我使用了最后一种方法：

我在while()-loop的末尾做了一个UPDATE。

我相信这可以做得更简单，也欢迎大家提出建议……

Answer 1

你不需要第二次更新。变化

$sql_dup_stage = 'INSERT INTO TUNE_stages (cId, stage, stageHp, stageNm, stagePrice, stageMethod, stageDyno, sinfo, snote)
            SELECT cId, stage, stageHp, stageNm, stagePrice, stageMethod, stageDyno, sinfo, snote FROM TUNE_stages WHERE sId = '.$get_stage['sId'];

至：

$sql_dup_stage = 'INSERT INTO TUNE_stages (cId, stage, stageHp, stageNm, stagePrice, stageMethod, stageDyno, sinfo, snote)
            SELECT \''.$new_carId.'\', stage, stageHp, stageNm, stagePrice, stageMethod, stageDyno, sinfo, snote FROM TUNE_stages WHERE sId = '.$get_stage['sId']