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>');
}
....
}
$_POSTis structured, especially$_POST['vidid']?var_dump($_POST['vidid']);? Is that what you are askingvididandquestion_idsin question