Why Data is not getting inserted into the mysql database using php?

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 after it has been inserted into 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();


                    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'])."')";

    mysql_query($imagesql);

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


    $insertimage[] = "'". 
                    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 
         ((SELECT ImageId FROM Image ORDER BY ImageId DESC LIMIT 1),
          " . implode('), (', $insertimage) . ")";

          mysql_query($imageinsertsql);

              }

              mysql_close();

        ?>

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

i thought this is solved here? http://stackoverflow.com/questions/10915022/how-to-retrive-imageid-and-insert-it-into-another-table/10915261#10915261 — sephoy08, Jun 07 '12 at 07:29
possible duplicate of [How to retrieve ImageId from one db table and store it in another db table](http://stackoverflow.com/questions/10924346/how-to-retrieve-imageid-from-one-db-table-and-store-it-in-another-db-table) — , Jun 07 '12 at 07:30
When you ask a "not working" type of question please post any error messages that you get. You need to enable PHP error reporting/logging settings such as `display_errors` if you don't see an error. — Salman A, Jun 07 '12 at 07:35
you can use mysql trigger to update second table with id from first table refer [mysql refrence maual](http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html) — sohaan, Jun 07 '12 at 08:07

score 0 · Answer 1 · answered Jun 07 '12 at 07:27

0

You need to use this: SELECT LAST_INSERT_ID();

answered Jun 07 '12 at 07:27

Pavel Petrovich

569
5
6

so does it go SELECT LAST_INSERT_ID($lastimageid);? or SELECT LAST_INSERT_ID(ImageId)? – user1394925 Jun 07 '12 at 07:41
`while ($someArray) { $res = mysql_query('INSERT INTO ImagesTable SET imageName = "karumburum.jpg"'); $id = mysql_query('SELECT LAST_INSERT_ID();'); $res2 = mysql_query('INSERT INTO OtherTable SET imageId = '.$id.', otherColumn = "'.$somedata.'"'); }` – Pavel Petrovich Jun 07 '12 at 07:54

score 0 · Answer 2 · edited May 23 '17 at 10:09

You need to provide the ImageId to the VALUES clause of each record being inserted into the Image_Question table. Using PHP's mysql_insert_id() function, one could adjust your for loop as follows:

for ($i = 0;  $i < $c; $i++) {
  $insertimage[] = mysql_insert_id()
  .", '".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) . ")";

However, the ancient MySQL extension is no longer maintained and the community has begun the deprecation process; you can use instead either the improved MySQLi extension or the PDO abstraction layer, which both support prepared statements into which variables can be passed as parameters (that do not get evaluated for SQL and therefore do not need to be escaped). For example, using PDO:

$dbh = new PDO("mysql:dbname=$db;charset=utf8", $username, $password);

$qry = $dbh->prepare('INSERT INTO Image (ImageFile) VALUES (?)');
$qry->execute(array("ImageFiles/{$_FILES['fileImage']['name']})"));

$qry = $dbh->prepare('INSERT INTO Image_Question (ImageId, SessionId, QuestionId) 
                      VALUES (:ImageId, :SessionId, :QuestionId)');

$qry->bindValue(':ImageId', $dbh->lastInsertId());
$qry->bindValue(':SessionId', $_SESSION['id'] .
                 ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : '')
               );

$qid = null;
$qry->bindParam(':QuestionId', $qid);
foreach ($_POST['numQuestion'] as $qid) $qry->execute();

score -1 · Answer 3 · answered Jun 07 '12 at 07:31

-1

Try this query.

$imagesql = "INSERT INTO Question 
         (ImageId, SessionId, QuestionId) 
         VALUES 
         ((SELECT ImageId FROM image ORDER BY ImageId DESC LIMIT 1),
          " . implode('), (', $insertimage) . ")";

answered Jun 07 '12 at 07:31

sephoy08

1,028
7
16

Do I not need `$lastimageid = mysql_insert_id();`? – user1394925 Jun 07 '12 at 07:46
I still can't get it to insert data into the Image_Question table, I have updated code which includes your query, I have found out you have just said that mysql_insert_id() is needed, so my question is where do I place the mysql_insert_id() code in my code and why do you think it is not inserting data into database? (I have checked query and there is no speliing mistakes in either query) – user1394925 Jun 07 '12 at 08:00
This answer is a terrible idea: it is not concurrency safe! If another connection inserts into `image` before the above insert into `Question` takes place, the wrong association will be made. – eggyal Jun 07 '12 at 08:03
sorry bout that you do not need `$lastimageid = mysql_insert_id();` this anymore. just this query will do. – sephoy08 Jun 07 '12 at 08:03
@eggyal: if thats the case, same thing happens if you choose `mysql_insert_id();` so what do you think is the proper way? – sephoy08 Jun 07 '12 at 08:12
@sephoy08: No, `mysql_insert_id()` returns the last auto_incremented value *on the current connection* - so other concurrent connections have no effect upon it. – eggyal Jun 07 '12 at 08:32

Why Data is not getting inserted into the mysql database using php?

3 Answers3