?
Solved

How to insert the correct question number into the database

Posted on 2012-09-20
22
Medium Priority
?
296 Views
Last Modified: 2012-09-20
I am trying to pick out the correct question number from a table and inserting it in the database. Lets say I have a table where it appends rows and that I have 3 rows, the table below will look something like this:

    Question No     Image
   
    1                     (file input)
    2                    (file input)
    3                    (file input)

Below is the code that creates the table above:
 
  var qnum = 1;
    
    var $qid = $("<td class='qid'></td>" ).text(qnum);
    var $image = $("<td class='image'></td>"); 
    
        var $fileImage = $("<form action='imageupload.php' method='post' enctype='multipart/form-data' target='upload_target' onsubmit='return imageClickHandler(this);' class='imageuploadform' >" + 
            "Image File: <input name='fileImage' type='file' class='fileImage' /></label><br/><br/><label class='imagelbl'>" + 
        "<input type='submit' name='submitImageBtn' class='sbtnimage' value='Upload' /></label>" + 
        "<label><input type='button' name='imageClear' class='imageClear' value='Clear File'/></label>" +
        "</p></form>");	
    
    $tr.append($qid);
    $tr.append($image);
    
    
        ++qnum;
        $(".questionNum").text(qnum);
        $(".num_questions").val(qnum);

Open in new window

What my question is that lets say I use the file input in row 2 for example, how can I insert the question number within the same row as that file input to be inserted into the database (the question number inserted would obviously be 2)?

Another example is that if I use the file input in row 3 for example, how can I insert the question number within the same row as that file input to be inserted into the database (the question number inserted would obviously be 3)?

Below is the code I currecntly have on inserting the data into the database (main code):

 
  $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 imagequestion"; 
        } 
        
    $qnum = 1;
    
    $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();

Open in new window


At the moment the code above is just inserting number 1 each time for question number in the database
0
Comment
Question by:carlbrooks1995
  • 9
  • 8
  • 3
  • +2
22 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38417103
try to access the insert id after insert done

    $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 = 1;
   
    $insertimagequestion->bind_param("isi",$lastID, $sessid, $qnum);
   
    $sessid =  $_SESSION['id'] . ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : '');
       
        $insertimagequestion->execute();
         $lastID = $mysqli->insert_id;  
                    if ($insertimagequestion->errno) {
              // Handle query error here
            }
     
            $insertimagequestion->close();
0
 

Author Comment

by:carlbrooks1995
ID: 38417132
@pratima_mcs I don't think you understand my question. Inserting the data isn' t the problem, it is just that I don't know how to insert the correct question number depending on which file input was used to upload the file
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38417222
Define your question number as an autonumber field - the DB will automatically assign the next sequential number to the row.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38417244
I can't be specific without seeing all of your code. As it is, it looks a little odd. You create a jQuery object for your form, but then don't seem to inject it anywhere. You also set the text for an element with a questionNum class to the value of qnum. This looks like it will set all elements with that class the the value of qnum. It looks like it should be in a loop but I can't see where.

Having said all that, if you are adding form elements multiple times, it make sense to name them as an array - in your case using the qnum as the index. So your inputs would look something like:

<input name='fileImage[1]' ...
<input name='fileImage[2]' ...
<input name='fileImage[3]' ...

You can then see which ones are set in your script, grab the index and use that as the question number.
0
 

Author Comment

by:carlbrooks1995
ID: 38417266
@Julian h I can't put it as auto increment because that would be incorrect. If I append 5 rows and row 2 (question number 2) has a file uploaded (cat.png) using the file input in that row and same thing happeneded in row 4 (question number 4) except in that file input I upload (dog.png), then in the Image_Question table it should display this:

ImageId    SessionId  QuestionId
01               AAA                  2
02               AAA                  4

But at moment because qnum = 1, it displays it like this:

ImageId    SessionId  QuestionId
01               AAA                  1
02               AAA                  1
0
 

Author Comment

by:carlbrooks1995
ID: 38417279
@ChrisStanyon  I know there is no loop but the problem is that I don't really know what I should set $qnum to. That is the problem, What should $qnum be and then how should I loop it so it will go through the question numbers and select the correct question numbers? Look at comment above so you can see what I am trying to acheive
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38417283
Actually, I've just looked at your code again and I was slightly off with my post. Because each input is wrapped in it's own form, just add the question number as a hidden input

<input type="hidden" name="questionNum" value="1" />

and then grab it in your script using

$_POST['questionNum'];
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38417313
@ChrisStanyon, I would leave at as you had it originally i.e. with name="fileimage[1]"

Even though in different forms you can make a generic solution that can be extended later

So
$fileimage = $_POST['fileimage']; // Add suitable sanitation

if (is_array($fileimage)) {
    foreach($fileimage as $k => $v) {
        $query = "INSERT INTO table (question_no, value) values('$k', '$v')";
    }
}

Open in new window

PS This is Chris' answer - I don't want to hijack it so if you go with this option you can leave me out of the points.
0
 

Author Comment

by:carlbrooks1995
ID: 38417356
Can I ask what I am suppose to change $qnum to? because In my bind parameters I have this: $insertimagequestion->bind_param("isi",$lastID, $sessid, $qnum);  So I just need to know what to change $qnum to?

Or do I not need $qnum anymore and use $k instead?
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38417373
qnum from your first script (jQuery) has no bearing on the qnum from your second script (php)

In your PHP script you need to set qnum to the value POSTed to your script. If you use the array method, then add something like Julian's answer:

if (is_array($fileimage)) {
    foreach($fileimage as $k => $v) {
        qnum = $k;
    }
}

If you use the hidden input then:

qnum = $_POST['questionNum'];
0
 

Author Comment

by:carlbrooks1995
ID: 38417436
@ChrisStanyon The hidden input technique did not seem to work.

The array method did not work as well.

Can you do me a big favour Chris, I am in no rush so take your time but can you code how both array method and hidden input method should be coded because I could not seem to get it right. Like I said you can take your time:

Below is my attempt on doing the array method but problem is that it is not inserting any data into the "Image_Question" Table (no php errors):

$lastID = $mysqli->insert_id; 

$fileimage = (int)$_POST['fileimage'];        
 
 $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"; 
    } 
    
    if (is_array($fileimage)) {
    foreach($fileimage as $k => $v) {
        $qnum = $k;
    }
}

$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(); 

Open in new window


Below is the attempt of doing it by hidden input but problem is that keeps inseting '0' for question number and not the correct question number:

Below is the page where it appends the rows and contains the question numbers and file inputs (with hidden input):

var qnum = 1;
    
    var $qid = $("<td class='qid'><input type="hidden" name="questionNum" value="1" /></td>" ).text(qnum);
    var $image = $("<td class='image'></td>"); 
    
        var $fileImage = $("<form action='imageupload.php' method='post' enctype='multipart/form-data' target='upload_target' onsubmit='return imageClickHandler(this);' class='imageuploadform' >" + 
            "Image File: <input name='fileImage' type='file' class='fileImage' /></label><br/><br/><label class='imagelbl'>" + 
        "<input type='submit' name='submitImageBtn' class='sbtnimage' value='Upload' /></label>" + 
        "<label><input type='button' name='imageClear' class='imageClear' value='Clear File'/></label>" +
        "</p></form>");	
    
    $tr.append($qid);
    $tr.append($image);
    
    
        ++qnum;
        $(".questionNum").text(qnum);
        $(".num_questions").val(qnum); 

Open in new window


Below is the code where it is suppose to then insert the data into the Image_Question table:

$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 imagequestion"; 
    } 
    
$qnum = (int)$_POST['numq'];

$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(); 

Open in new window


Sorry bout this, it is just that I am really struggling on this
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38417491
OK.

In the hidden input method, you need to put the hidden field inside your FORM element. You've just dropped it into the table. Something like this (note the last line)

fileImage = $("<form action='imageupload.php' method='post' enctype='multipart/form-data' target='upload_target' onsubmit='return imageClickHandler(this);' class='imageuploadform' > \
	Image File: <input name='fileImage' type='file' class='fileImage' /> \
	<input type='submit' name='submitImageBtn' class='sbtnimage' value='Upload' /> \
	<input type='button' name='imageClear' class='imageClear' value='Clear File'/> \
	<input type='hidden' name='questionNum' value='" + qnum + "' /></p></form>");

Open in new window

and then in your script you need to reference it using the same name as you used in your form - questionNum in my example above:

$qnum = (int)$_POST['questionNum'];

Open in new window

In the array version, dont try and convert an array to integer - it just won't go:

$fileimage = $_POST['fileImage'];

Open in new window

Note the case of field name: fileImage <> fileimage
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38417499
For the array approach don't do this

$fileimage = (int)$_POST['fileimage'];        
 
You are casting the return to an int when it is an array.

$fileimage = $_POST['fileimage'];
0
 

Author Comment

by:carlbrooks1995
ID: 38417575
@Chris Using your hidden input idea you have almost got it to work. You are 90% correct. Just one little glitch. Lets say I have 4 rows and I used file input in row 2 and row 4 to upload the files, then it means the question numbers sohuld be 2 and 4 meaning the table should look like this below:

ImageId    SessionId  QuestionId
01               AAA                  2
02               AAA                  4

But instead it has got the question number wrong by only plus 1 meaning it is displaying it like this:

ImageId    SessionId  QuestionId
01               AAA                  3
02               AAA                  5
0
 
LVL 45

Accepted Solution

by:
Chris Stanyon earned 2000 total points
ID: 38417623
That tells me you're setting your qnum wrongly in your jQuery. I can't see from your code how you are looping and incrementing the qnum variable. Here's some code that demonstrates a working example:

for (var qnum=1; qnum < 5; qnum++) {
	qid = $("<td class='qid'></td>" ).text(qnum);
	image = $("<td class='image'></td>");

	fileImage = $("<form action='imageupload.php' method='post' enctype='multipart/form-data' target='upload_target' onsubmit='return imageClickHandler(this);' class='imageuploadform' > \
	Image File: <input name='fileImage' type='file' class='fileImage' /> \
	<input type='submit' name='submitImageBtn' class='sbtnimage' value='Upload' /> \
	<input type='button' name='imageClear' class='imageClear' value='Clear File'/> \
	<input type='hidden' name='questionNum' value='" + qnum + "' /></p></form>");

	image.append(fileImage);

	tr = $("<tr></tr>").append(qid,image);
	
	$('#myTable').append(tr);
};

Open in new window

It will loop through the code 4 times (setting qnum to 1,2,3 or 4) and add a row to a table with an ID of myTable
0
 

Author Comment

by:carlbrooks1995
ID: 38417872
@Chris if you refer back to the question, in the first block of code it shows how I am incrementing the qnum variable in my jquery
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38417976
I'm guessing your code has changed somewhat since then. That code shows you incrementing, but not looping. The increment happens before the end of the code, so, these two calls:

$(".questionNum").text(qnum);
$(".num_questions").val(qnum);

Are always going to have their values set to 1 higher than:

qid = $("<td class='qid'></td>" ).text(qnum)

It seems like you're incrementing in the wrong place but without seeing your loop can't be exactly sure where.

If you look at my code, it sets the qnum variable, does whatever it needs to with it, and then increments and loops - all part of the for() statement
0
 

Author Comment

by:carlbrooks1995
ID: 38418326
@Chris  Sorry I forgot to put in the loop on what I have done, the loop and increment of the qnum is below:

         
var qnum = 1;
var qremain = <?php echo (int)$_SESSION['textQuestion']; ?>;

...

 $('.num_questions').each( function() {
    
	var $this = $(this);
    
     var $questionNumber = $("<input type='hidden' class='num_questionsRow'>").attr('name',$this.attr('name')+"[]")
                   .attr('value',$this.val());
    
     $qid.append($questionNumber);                             
                   
    ++qnum;
    $(".questionNum").text(qnum);
    $(".num_questions").val(qnum);
    
    --qremain;
    $(".questionRemain").text(qremain);
});

Open in new window


What this code does is that lets say qnum starts with '1', when the user clicks on the submit button, it will append the qnum from the top (1) and append it to the table row so that the table row will state 1 for the first row. The qnum at the top then increments so that it now states 2. When the user appends another row, then the second row appended will state 2 in the row and the qnum at the top increments so it nows states 3 at the top. And so on...
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38418568
Sorry Carl, your code is baffling me somewhat. I'm only seeing bits of your code and on their own they don't make any sense.

What I see here is this:

For each element with a class of .num_questions it runs through the loop. If you have 3 elements with this class the code will run three times.

It creates a new hidden input with the same name and value as whichever num_question you're on in the loop, and then adds that element inside of qid, which looks like a TD tag with a qid class. If you're running this code 3 times, you will end up with three Hidden Inputs inside one TD - not even as part of a form, so they never get submitted (based on your earlier code)

It then increments the qnum variable by 1, and sets the text of all elements with a class of questionNum to the value of gnum, and the 'val' of all elements with a class of num_questions to the value of gnum.

Now, assume you are running this code 3 times, because you have 3 element.num_questions, and assuming the value of qnum was 1 before the loop started. Running the code above will create 3 hidden inputs inside a TD element, set the text for all element.questionNum to 4, and the value of all element.num_questions to 4.

It just doesn't make sense.

In your last comment, you also introduced the fact that your using a submit button to add a row. That's new. I can't see why you'd run your code in a loop if you add a row by clicking a button.

Another thing to think about. All the elements you are talking about are using classes, so can apply to several elements on the page. If there is only ever going to be one of particular element on a page, use an ID instead - it makes life much easier. If you only have one element on your page for storing the Question Number (as I hope you have), then it should be an ID.

<p id="num_questions">2</p>

You then reference it in jQuery as $('#num_questions')

Also, please drop the $ before variable names - it just makes them look like jQuery objects.

I think you need to post all your jQuery code, and the relevant parts of the HTML and I can have a proper look at it - it seems at the moment than all put together it's a bit of a mess...

...we'll get there :)
0
 
LVL 34

Expert Comment

by:Slick812
ID: 38418755
greetings  carlbrooks1995, ,  You have the correct idea about using the
++qnum;

in your javascript to increase the qnum  , , however, you have placed the
var qnum = 1;

in the Wrong place, you need to move it away from the code in this (I guess) OnClick function. Remove it from there and place the
var qnum = 1;

right after your first <script> tag where Your JS code begins.

I hope to say this to help you,  as some have already said here, Your code is not very well organized (mixed up), and you seem to lack an understanding of the PHP and javascript methods that you use, You might start to improve your code, if you can find some ways to see what your "code base" (the code you copyed and pasted into your pages) is doing by changing it in "Test" pages that are  more simple (have less working parts), until you get a good idea of what each part is doing, but this may not be an option for you.
0
 

Author Closing Comment

by:carlbrooks1995
ID: 38418834
Hi, I figured out what needed to be do, if I add an extra jquery variable known as numq = 0, then make that the value in the hiden input and increment rhat variable, then the correct question numbers are displayed in the database.

Thanks for all your help Chris :)
0
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38418853
Here's a clean example of what I think you're trying to achieve:

$(document).ready(function() {
	$('#addNewQuestion').click(function(e){
		//Get hold of the Table we're working with
		myTable = $('#myTable');
		
		//get the new question number
		questionNumber = myTable.data('number-of-questions') + 1;
		
		//Create 2 new table cells
		questionIDCell = $("<td class='questionNumber'></td>" ).text(questionNumber);
		imageCell = $("<td class='image'>The image Goes Here</td>");
		
		//Make a copy of our hidden form
		newForm = $('#hiddenContent form').clone();
		
		//Set the value of the questionNum field
		newForm.find('input[name=questionNum]').val(questionNumber);
		
		//Create a table cell for it
		formCell = $('<td></td>').append(newForm);
	
		//Create a new row, containing the 3 new cells
		newQuestionRow = $("<tr></tr>").append(questionIDCell,imageCell,formCell);
	
		//Add the new row to the table
		myTable.append(newQuestionRow);
		
		//Update how many questions the table has.
		myTable.data('number-of-questions',questionNumber);
	})
});

Open in new window

<body>

<button id="addNewQuestion">Add a New Question</button>

<table id="myTable" data-number-of-questions="0">
	<!-- New Rows will be added by jQuery -->
</table>

<div id="hiddenContent" style="display:none;">
	<form action='imageupload.php' method='post' enctype='multipart/form-data' target='upload_target' onsubmit='return imageClickHandler(this);' class='imageuploadform' >
		Image File: <input name='fileImage' type='file' class='fileImage' />
		<input type='submit' name='submitImageBtn' class='sbtnimage' value='Upload' />
		<input type='button' name='imageClear' class='imageClear' value='Clear File'/>
		<input type='text' name='questionNum' value='' />
	</form>
</div>
	
</body>

Open in new window

Have a fiddle - http://jsfiddle.net/emupb/
0

Featured Post

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.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
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 Month15 days, 14 hours left to enroll

850 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