php - 使用 MySQL 触发器更新匹配条件的所有字段

Question

我想做的是将每个插入查询的列设置为$i的最新值我以前从未在MySQL中使用过触发器，但我确实相信这是最简单的方法去做这个。我愿意接受其他想法和建议。现在我有:

$i = 1;
foreach($_FILES["upload_project_images"]["name"] as $key => $name) {   
      $p_image_query = " 
        delimiter |
            CREATE TRIGGER update_start_num BEFORE INSERT ON project_images
            FOR EACH ROW 
            BEGIN
                UPDATE `project_images` SET `NEW.i_start_num` = '$i' WHERE `i_project_id` = '$prject_id' AND 'i_type' = '2';
            END |
        delimiter;
        INSERT INTO `project_images` (i_name, i_type, i_project_id,i_start_num) VALUES ('$upload_project_images_name', '2', '$project_id','$i');";

   $result=mysql_query($p_image_query) or die(mysql_error()); 
   $i++;
  }

这个想法是，i_start_num 将等于最后一个 $i，这样我就可以在更新查询时从那里获取。

我的第一个想法是在另一个查询之后运行一个查询，但是我尝试过，但这不起作用:

  $i = 1;
  foreach($_FILES["upload_project_images"]["name"] as $key => $name) {   
        //insert the file data into the database
        $p_image_query = "INSERT INTO `project_images` (i_name, i_type, i_project_id,i_start_num) VALUES ('$upload_project_images_name', '2', '$project_id','$i')"
         $result=mysql_query($p_image_query) or die(mysql_error()); 
    $i++; 

  //update the starting image id number on all project images
        $p_update_startnum_qry ="UPDATE `project_images` SET `i_start_num` = '$i' WHERE `i_project_id` = '$prject_id' AND 'i_type' = '2'";
        $p_update_startnum_qry_result=mysql_query($p_update_startnum_qry) or die(mysql_error());
    }

这也失败了。

Answer 1

嗯，也许试试这个:

// pseudo code
INSERT INTO `project_images` (i_name, i_type, i_project_id,i_start_num) 
VALUES ('$upload_project_images_name', '2', '$project_id','$i')
ON DUPLICATE KEY UPDATE `project_images` 
SET `i_start_num` = '$i' 
WHERE `i_project_id` = '$prject_id' AND 'i_type' = '2'