?
Solved

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

Posted on 2012-09-19
10
Medium Priority
?
241 Views
Last Modified: 2012-09-19
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?
0
Comment
Question by:carlbrooks1995
  • 6
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 38413242
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.
0
 

Author Comment

by:carlbrooks1995
ID: 38413285
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 :)
0
 

Author Comment

by:carlbrooks1995
ID: 38413291
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1500 total points
ID: 38414239
If you can't see any browser output from the script, it's much harder to debug.  Here are a couple of ideas...

1. ALWAYS set error_reporting(E_ALL); no exceptions.

2. Use this function to write signals and data that you can inspect later.
http://us2.php.net/manual/en/function.error-log.php

3. Use output buffering.  Capture the buffer in a string variable and email it to yourself.  It's clunky, but it works!
http://us2.php.net/manual/en/function.ob-start.php

Another thought... Just run the script directly from a plain old HTML form, bypassing all the AJAX stuff.  Once you have the script debugged, then add the AJAX layer.

HTH, ~Ray
0
 

Author Comment

by:carlbrooks1995
ID: 38414319
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

0
 

Author Comment

by:carlbrooks1995
ID: 38414435
@Ray_Paseur can you show me how to use ob_start() function to send to email?
0
 

Author Closing Comment

by:carlbrooks1995
ID: 38414806
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
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38415574
@carlbrooks1995: Please read the grading guidelines here:
http://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

0
 

Author Comment

by:carlbrooks1995
ID: 38415612
@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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question