如何从一个db表中检索ImageId并将其存储在另一个db表中

Question

I have a INSERT function where it inserts the image filename in the 'ImageFile' field in the "Image" table, each row has it's own ImageId thanks to auto number. An example of this is below:

ImageId    ImageFile

23         orange.jpg
24         flowers.png
25         castle.png
26         orange.jpg

What I want to do is also insert the ImageId into another table with the QuestionId and SessionId so that this table (Image_Question) can use the ImageId to link the Image table with the Image Question table. Now I am trying to use mysql_insert_id to retrieve the ImageId from the Image Table and store it in the ImageId in the Image_Question table.

But I can't seem to figure out what I need to do, at the moment the the INSERTING values into the Image Table is working fine but it does not insert any values inside the Image_Question table.

So my question is for each row inserted into the Image table, how do I retrieve the ImageId from the Image Table, and insert it into the Image_Question table using mysql_insert_id()? Example below:

ImageId   SessionId  QuestionId

23        AAA        1
24        AAA        2
25        AAA        3
26        AAA        4

I have coded the INSERT values for SessionId and QuestionId but just need help retrieving and inserting the ImageId. Below is the current code:

<?php

session_start();


//connect to db

$result = 0;
$i = 0;
$insertimage = array();
$lastimageid = mysql_insert_id();


      move_uploaded_file($_FILES["fileImage"]["tmp_name"],
      "ImageFiles/" . $_FILES["fileImage"]["name"]);
      $result = 1;

        $imagesql = "INSERT INTO Image (ImageFile) 
        VALUES ('ImageFiles/".mysql_real_escape_string($_FILES['fileImage']['name'])."')";

    for($i = 0;  $i < $c; $i++ ){


    $insertimage[] = "'". mysql_real_escape_string( $lastimageid [$i] ) ."','". 
                    mysql_real_escape_string($_SESSION['id'] ) . 
                    ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : '') ."' ,'". 
                    mysql_real_escape_string( $_POST['numQuestion'][$i] ) ."'";

}

    $imageinsertsql = "INSERT INTO Image_Question (ImageId, SessionId, QuestionId) 
    VALUES (" . implode('), (', $insertimage) . ")";

    mysql_query($imageinsertsql);

    mysql_query($imagesql);

      }

      mysql_close();

?>

I have an old php version 5.2.13 because that is the version of the university's server.

Answer 1

You need to reorder your php code to run

mysql_query($imagesql); 
$lastimageid = mysql_insert_id(); 
mysql_query($imageinsertsql);

Currently you retreive lastimageid, insert Image_Question then insert Image.