asked on

How to insert data into 2 tables (it is only inserting into 1 table at the moment)

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

    <?php
    /* 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();
    
    $insertimagequestion->execute();
    
    //IF ANY ERROR WHILE INSERTING DATA INTO EITHER OF THE TABLES
    if ($insert->errno) {
      // Handle query error here
    }
    
    $insert->close();

    
    $lastID = $mysqli->insert_id;
     
     $imagequestionsql = "INSERT INTO Image_Question (ImageId, SessionId, QuestionId) 
        VALUES (?, ?, ?)";
    
                         
        if (!$insertimagequestion = $mysqli->prepare($imagequestionsql)) {
          // Handle errors with prepare operation here
           echo "Prepare statement err";
        }
    
    $sessid =  $_SESSION['id'] . ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : '');
    
    $insertimagequestion->bind_param("isi",$lastID, $sessid, $_POST['numQuestion'][$i]);
    
            $insertimagequestion->execute();
    
                    if ($insertimagequestion->errno) {
              // Handle query error here
            }
    
            $insertimagequestion->close();
    
    }
    ?>

Open in new window

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

But it is not inserting anything in the Image_Question table. How when I upload an image, it not only inserts data into the "Image" table but be able to insert the data into the "Image_Question" table as well?

Scott Madeira

A couple of thoughts...

0. Make sure you have error reporting turned on and you are displaying error messages to the screen. You may have a problem with description that you can't see.

1. Do a var_dump($_POST) to make sure that the POST variable has a value. It may be a non-existent variable.

2. Your $i variable is not set anywhere in your code segment.

3. Don't do a direct use of the $_POST into a SQL statement. You have to assume the variable has been tampered with and you need to validate first.

4. What is the relevance of sessionID in the second query as it appears to be redundant with ImageFile from the first table. The second table could just be ImageID and QuestionId to get what you need based on what info was provided.

carlbrooks1995

ASKER

I will come back to you in detail after I have done my exercie (need to lose wieght lol) I will quickly answer point number 4 you made. In the first table "Image" it just stores in all of the names of the image files uploaded into the "Image" Table. In the second table "Image_Question" it stores which questions those uploaded images go to. So where there is "SessionId" and "QuestionId" in second table, what it means is that for example below:

ImageId SessionId QuestionId

220 AAA 1
221 AAC 4

The ImageId 220 which contains the Id of the image stored in the "Image" table, belongs to Assessment AAA question number 1 for the first row.

In the second row ImageId 221, Id of the image stored in the "Image" table, belongs to Assessment AAC question number 4.

I will be back in 2 hours so please stay in touch if you can :)

carlbrooks1995

ASKER

Btw I know that I messed up to display the correct info for what the "Image_Question" table should look like in my question, what it should look like is in the comment I posted above.

The problem I have btw that even tohugh you said to me to turn error reporting on and do a var_dump, this scipt is done on th background, I am unable to see this script on the screen so how am I able to see what the var_dump displays or if there are any errors because the uploading of the image is done by ajax and we access this php script through ajax as well so that everything is done in the background

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

carlbrooks1995

ASKER

I will try and debug the code, at the moment I have chnged the code but still not getting it to insert anything into the "Image_Question" table. I can't see the output of the var_dump() or error report because the script is run on the background but I will try and do what Ray has mentioned in his comments.

Below is the updated code with all the points I tried to meet which smadeira mentioned:

<?php

 ini_set('display_errors',1); 
 error_reporting(E_ALL);

    /* 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();
    
    $insertimagequestion->execute();
    
    //IF ANY ERROR WHILE INSERTING DATA INTO EITHER OF THE TABLES
    if ($insert->errno) {
      // Handle query error here
    }
    
    $insert->close();

    
  $lastID = $mysqli->insert_id;         
$i = 0;
$c = count($_POST['fileImage']);

for($i = 0;  $i < $c; $i++ ){
 
 $imagequestionsql = "INSERT INTO Image_Question (ImageId, SessionId, QuestionId)  
    VALUES (?, ?, ?)"; 
    
     if (!$insertimagequestion = $mysqli->prepare($imagequestionsql)) { 
      // Handle errors with prepare operation here 
       echo "Prepare statement err imagequestion"; 
    } 
    
$qnum = $_POST['numQuestion'][$i];

$insertimagequestion->bind_param("isi",$lastID, $sessid, $qnum); 

$sessid =  $_SESSION['id'] . ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : ''); 
    
    $insertimagequestion->execute(); 
    
                if ($insertimagequestion->errno) { 
          // Handle query error here 
        } 
 
        $insertimagequestion->close(); 
       
        var_dump($_POST);
        
    }    
    }
    ?>

Open in new window

carlbrooks1995

ASKER

@Ray_Paseur can you show me how to use ob_start() function to send to email?

carlbrooks1995

ASKER

This is not the full solution, but it has helped me to enable to debug the problem. I have changed my code and I got some php errors which I will post in another question

Ray Paseur

@carlbrooks1995: Please read the grading guidelines here:
https://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26

A grade of "C" is the worst possible grade you can give at EE. Why did you do that?

Regarding this line of inquiry...

@Ray_Paseur can you show me how to use ob_start() function to send to email?

Sure I can, and I would be glad to, but I also have a life and cannot be sitting at my computer waiting for your messages to come in. When you ask a question like that, please expect to allow 24 to 48 hours for a response. An hour and a half is not enough time to even play a round of golf! If you need on-call assistance, please retain me. I will give you my direct phone and email, and importantly, I'll make your issues a priority.

All of the PHP functions are documented in the online man pages at php.net. For information about ob_start(), please see this:
http://us2.php.net/manual/en/function.ob-start.php

While output buffering is active the output is stored in an internal buffer. The contents of this internal buffer may be copied into a string variable using ob_get_contents().

For information about mail() please see this:
http://us2.php.net/manual/en/function.mail.php

Thus the entire script to test the concept would look like this:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ob_start();
echo 'Hello World';
$msg = ob_get_clean();
mail('you@your.org', 'Buffers', $msg);

Open in new window

carlbrooks1995

ASKER

@Ray I will be more than happy changing it from C to a B grade. I didn't mean any disrespect, it is that your answer did not obviously solve the problem but it did help me do some debugging on my own. I didn;t give it an A as that I still had to do a lot to fix the code, I gave it a C because I though that you deserved some points for at least helping me on this problem. I will happily improve the grade. Can you tell me how to improve the grade and I will improve it from C to B.

I didn't mean any disrespect and I didn't know that grade C is very bad. I am sorry.

Carlbrooks1995