Data is not being inserted into second table (MYSQLi)

Question

I am using the code below that uploads a file and inserts data into the "Image" table using mysqli:

<?php
session_start();

$username="xxx";
$password="xxx";
$database="mobile_app";

$mysqli = new mysqli("localhost", $username, $password, $database);

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    die();
}

$result = 0;

//UPLOAD IMAGE FILE

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

$result = 1;

//INSERT INTO IMAGE DATABASE TABLE

$imagesql = "INSERT INTO Image (ImageFile) VALUES (?)";

if (!$insert = $mysqli->prepare($imagesql)) {
    // Handle errors with prepare operation here
}

//Dont pass data directly to bind_param store it in a variable
$insert->bind_param("s", $img);

//Assign the variable
$img = 'ImageFiles/' . $_FILES['fileImage']['name'];

$insert->execute();

//RETRIEVE IMAGEID FROM IMAGE TABLE

$lastID = $mysqli->insert_id;

//INSERT INTO IMAGE_QUESTION DATABASE TABLE

$imagequestionsql = "INSERT INTO Image_Question (ImageId, SessionId, QuestionId) VALUES (?, ?, ?)";


if (!$insertimagequestion = $mysqli->prepare($imagequestionsql)) {
    // Handle errors with prepare operation here
}

$sessid =  $_SESSION['id'] . ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : '');

$insertimagequestion->bind_param("sss", $lastID, $sessid, $_POST['numQuestion'][$i]);

$insertimagequestion->execute();

//IF ANY ERROR WHILE INSERTING DATA INTO EITHER OF THE TABLES
if ($insert->errno) {
  // Handle query error here
}

$insert->close();

if ($insertimagequestion->errno) {
  // Handle query error here
}

$insertimagequestion->close();

}

}
?>

So for example if I insert 2 images "cat.png" and "dog.png" into "Image" Database table, it will insert it like this:

ImageId         ImageFile

220             cat.png
221             dog.png

(ImageId is an auto increment)

Anyway what I want to do is that when a file is uploaded, not only is the data inserted into the table above, but I want to also be able to retrieve the ImageId that was inserted above and place it in the "Image_Question" table below so it would be like this:

 ImageId         SessionId      QuestionId

    220             cat.png      1
    221             dog.png      4

The problem is that it is not inserting any data into the second table "Image_Question", does anyone know why it is not inserting any data? There is no errors in the php file.

To upload a file, the user selects a file for the ajax uploader in the "QandATable.php" page, when the user clicks on upload, using AJAX it will go onto the imageupload.php page and does the uploading there. So the problem I have is that no errors will appear as they are on seperate pages.

Answer 1

First, save the insert ID gained from your record addition (after the $insert->execute):

$lastID = $mysqli->insert_id;

Then reference $lastID later.

To pull up my comment from below:

$lastID = $insert->insert_id;

I think it's to do with swapping the handle names around - $mysqli, $insert etc.

Hope I read the question correctly...

Answer 2

Check for 500 Error responses in Firebug -> Net tab/Chrome Developer tools -> Network tab . Even if nothing is returned as text, this will help you debug a syntax/semantic error as opposed to a logical error.