1

Lets say I have 3 questions:

QuestionNo   Video
1            
2            wildlife.mp4
3
4            colors.mp4
             sports.mp4

Now what I am doing is insert each question into database giving each question its own automatic QuestionId so it now looks like this technically:

QuestionId    QuestionNo  Video
123           1
124           2           wildlife.mp4
125           3
126           4           colors.mp4 
                          sports.mp4

Now below is the Question Table in db:

QuestionId (pk auto)  QuestionNo
123                   1
124                   2
125                   3
126                   4

Now each video is stored in its own id in the Video Table:

VideoId (auto pk)   VideoFile
12                  wildlife.mp4
13                  colors.mp4
14                  sports.mp4

Now finally and this is where the issue is, I have a Video_Question Table which we use to know which video goes into which question. Now the issue is that the way I have set up the insert for this is that it retrieves the last QuestionId insert and then find the first videoId it finds and displays it with that QuestionId. This is incorrect as that it means video is stored in wrong question. In db for above example it will display it like this:

Video_Question Table:

VideoQuestionId (auto pk)  QuestionId  VideoId
4                          123         12
5                          124         13
6                          124         14

The above is incorrect as that VideoId 12 should not belong in QuestionId 123, it should be in 124 and VideoId 13 and 14 should be in 126, not 124 like below:

Video_Question Table:

VideoQuestionId (auto pk)  QuestionId  VideoId
4                          124         12
5                          126         13
6                          126         14

My question is that looking at above example, how can I get the videoid to be inserted with the correct QuestionId? I believe the issue is that it does not recognise that a question has possibility of no video but I am not sure and hence why I am asking for help>

Below is code where it does the insert Question table and Video_Question tables:

// Prepare your statements ahead of time
$questionsql = "INSERT INTO Question (SessionId, QuestionNo,
    QuestionContent, NoofAnswers, ReplyId, QuestionMarks, OptionId) 
    VALUES (?)";
if (!$insert = $mysqli->prepare($questionsql)) {
    // Handle errors with prepare operation here
    echo __LINE__.': '.$mysqli->error;
}

$videoquestionsql = "INSERT INTO Video_Question (VideoId, QuestionId)  
    VALUES (?, ?)"; 

if (!$insertvideoquestion = $mysqli->prepare($videoquestionsql)) { 
    // Handle errors with prepare operation here 
    echo __LINE__.': '.$mysqli->error; 
} 

//make sure both prepared statements succeeded before proceeding
if( $insert && $insertvideoquestion)
{
    $c = count($_POST['numQuestion']);
    $question_ids = array();

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

        $questionNo = $_POST['numQuestion'][$i];

        $insert->bind_param("i", $questionNo);

        $insert->execute();

        if ($insert->errno) 
        {
            // Handle query error here
        echo __LINE__.': '.$insert->error;
        break 1;
        }

        $questionId = $mysqli->insert_id;

        $question_ids[$questionNo] = $questionId;
    }

    $vidresults = $_POST['vidid'];

    foreach($vidresults as $videoid => $vidvalue) 
    {

         $video = $vidvalue;

         $vidquesid = (int)$question_ids[$videoid];  

         foreach($vidvalue as $video) 
         {

             $insertvideoquestion->bind_param("ii",$video, $vidquesid); 

             $insertvideoquestion->execute(); 

             if ($insertvideoquestion->errno) { 
                 // Handle query error here 
                 echo __LINE__.': '.$insertvideoquestion->error;
                 break 5;
             } 

         }
    }

    $insertvideoquestion->close(); 
    $insert->close();

}

UPDATE:

Below are the var_dumps which match with the example mentioned above, only difference is the file names and id numbers. There are 4 questions as mentioned in example mentioned above:

var_dump($question_ids);

    array(4) { 
[1]=> int(277) 
[2]=> int(278) 
[3]=> int(279) 
[4]=> int(280) 
} 

var_dump($_POST['vidid']);

array(3) { 
[1]=> array(1) { 
[0]=> string(3) "148" 
} 
[2]=> array(1) { 
[0]=> string(3) "149" 
} 
[3]=> array(1) { 
[0]=> string(3) "150" 
} }

Combined the above var_dumps together looks like this:

array(4) { 
[1]=> int(289) 
[2]=> int(290) 
[3]=> int(291) 
[4]=> int(292) 
} 
array(3) { 
[1]=> array(1) { 
[0]=> string(3) "148"
} 
[2]=> array(1) { 
[0]=> string(3) "149" 
} 
[3]=> array(1) { 
[0]=> string(3) "150" 
} } 

EXTRA DETAILS:

By using this line of code:

$('.hiddenvid').append('<input type="hidden" name="vidid[' + videocounter + '][]" id="'+videoID+'" value="' + videoID + '" />');

It is able to retrieve and display the VideoId inserted into the VideoTable for each uploaded video. So for example if Wildlife.mp4 has VideoId which is 32, then in the hidden input it will displat the number 32 in the hidden input for that video in the application.

Then by using a multidimensional array and using videocounter which is a variable which starts with 0 and increments for each question using ++, - name="vidid[' + videocounter + '][]", each input belongs to a question number depending on which question the video was uploaded in. So for example if the video was uploaded in question 2, the input recgnises that the video id retrieved belongs to question 2. If I do it for question 4, then it will recognise that input belongs to question 4 etc.

HERE IS A DEMO I JUST MEAD UP WHICH YOU CAN USE TO SEE HOW IT WORKS (I changed the hidden input to text input so you can see what is happening): DEMO

UPDATE:

At moment it is displaying 0 for QuestionId when inserting data in Video_QuestionId, is the code below correct:

function stopVideoUpload(success, videoID, videofilename){

      var result = '';
      videocounter++;


      var questionNum = $(this).closest('td').siblings('.qid').find('input.num_questionsRow').val();

      if (success == 1){
         result = '<span class="videomsg'+videocounter+'">The file was uploaded successfully</span>';
            $('.hiddenvid').append('<input type="text" name="vidid['+questionNum+'][]" id="'+videoID+'" value="' + videoID + '" />');
            $('.listVideo').eq(window.lastUploadVideoIndex).append('<div>' + htmlEncode(videofilename) + '<button type="button" class="deletefilevideo" data-videoID="'+videoID+'"  data-video_file_name="' + videofilename + '" value="'+videoID+'">Remove</button><br/><a href="previewvideo.php?filename='+ videofilename +'" title="Click to view in New window" target="_blank">Test Video<hr/></div>');
          }

....

}
7
  • What is you're trying to present..? Describe the content you're working with and, if possible, don't use jQuery to manipulate the data on every page load at the client end, change the mark-up on the server side. Commented Feb 2, 2013 at 12:43
  • 1
    So, the Video table is already populated beforehand? Could you show, how $_POST is structured, especially $_POST['vidid']? Commented Feb 5, 2013 at 1:08
  • @fab Yes when the user uploads a video into the server, it already inserts the video details into database. Do you want me to var_dump($_POST['vidid']);? Is that what you are asking Commented Feb 5, 2013 at 1:53
  • @fab I included the var dump for vidid and question_ids in question Commented Feb 5, 2013 at 2:52
  • This looks completely different from your original question (about replacing divs w jquery). If you had a new question it would have been better to create another one. Now there's a really confusing comment on here because it's in reference to the original question not this one. Commented Feb 5, 2013 at 3:19

2 Answers 2

1
+50

I don't see anywhere in your front-end that connects the uploaded videos to the question they're meant to be related to. So when you submit the form it basically just sends two unrelated lists numQuestion: (q1, q2, q3...) and vidid: (videos1[], videos2[], videos3[]...). The array keys are just the order they were added which is not what you wanted.

There are many different ways you could approach this but the most direct is to change the way you build those hidden fields for the videos so that they are keyed on the questions' ids.

Instead of:

<input type="text" name="vidid[]" id="'+videoID+'" value="' + videoID + '" />

You want this to be something like

<input type="text" name="vidid['+questionNum+'][]" id="'+videoID+'" value="' + videoID + '" />

Where questionNum is the question the video is meant for.

EDIT:

You won't be able to determine this at call-time for your stopVideoUpload() function so you need to make a new parameter to that function and pass this value in. That means of course the PHP code for video upload needs to know what value to pass to stopVideoUpload(). You should be able to do this easily enough by changing the Add Question behavior to put the question number in a field in the video upload form.

Sign up to request clarification or add additional context in comments.

4 Comments

Can I ask what the correct jquery line is for grabbing the questionNum, I tried something like this var questionNum = $(this).closest('td').siblings('.qid')
I will test this in about 30 mins, just working on something, thanks for update
Please see update when you have chance, it seems like it is just inserting 0 for questionId when inserting into Video_Question
Thanks, boutny awarded in 6 hours
0

It maybe trying to insert two values as primary keys. Set database fields to non-auto-increment if this is the case. Then---parent as videoid to match each question with its own individual id. You cant properly insert into a row that's already been created if this is the case.,,use update for them. Also some tables will not do as you wish when using auto-incrementing. If this is the case turn off auto-incrementing and place--max(id2)+1--in place of all your Primary $id's value-entry's. ---------------select max(id)+1, "'.$AnswerId.'", etc. Also Each table should have each corresponding parent-id.

    video-table
    ------------pid------
    questions-table
    ------------qid-----pid----id
    video-question-table
    ------------id------qid----pid


    if(mysql_query('insert into questions (id, parent, questionContent, NoofAnswers,
    ReplyId, AnswerId) select "'.$id.'", "'.$res['parent'].'", "'.$questionCont.'",
    "'.$Noofanswers.'", "'.$ReplyId.'", "'.$AnswerId.'" from questions where
    id="'.$id.'"') and mysql_query('update videos_questions set VideoId="'.$vid.'"and set
    QuestionId="'.$qid where id="'.$id.'" and parent=1'))  etc. 

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.