Using a MySQL trigger to update all fields matching a condition

Question

Hi,

What I am trying to do is to set the column of each insert query to the latest value of $i I've never used a trigger in MySQL before but I do believe that's the easiest way to do this. I would be open to other ideas and suggestions. Right now I have:

$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++;
  }

The idea is that i_start_num is going to equal the last $i so I can pick up there when updating the query.

My first idea was just to run one query after the otherbut that didn't work either I tried:

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

Which also failed. Any help would be great!

Answer 1

Hmm maybe try this:

// 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'