Multiple MYSQL INSERT statement

[javedna]

Hi Im doing a questionnaire in PHP and MYSQL

I am trying to do a multiple insert using the following code

[PHP]$query1 = "INSERT INTO answers (answer_score,question_id,user_id) VALUES ('$item1','$question[0]','$user_id_foreign'), ('$item2','$question[1]','$user_id_foreign')";[/PHP]

But it will only insert the first value and not the second. I have tested the mysql bit of coding in mysql itself with some values I manuall input i.e.: -

(4, 1, 1), (3, 2, 1) and this inserted both sets of values. I have tried splitting them up into seperate queries but the exact same thing happens.

From what I can tell I believe it is to do with my values for the user_id, I am inserting the user_id with a variable I have called $user_id_foreign. Due to the lack of proper foreign keys in the versions I have (v 4.1) I am having to count up the number of users in the table put them in DESC order and then add 1 for the next user_id. This works like a charm but as there are 75 questions I need this same ID to be repeated 75 times.

Another possibility is that of the answers (1 to 7) which are stored as strings in $item1, $item2 and so on till $item75

Any help would be gratelly appreciated

Thanks

Nabz

[vpmurdan]

Hi.

It seems that the '$foreign_user_id' has the same value in both records. So 1st record is inserted while 2nd is not.

As you are adding 1 to your user id for each record you should do something like

 
$query1 = "INSERT INTO answers (answer_score,question_id,user_id) VALUES"
 
 for each record
 
$item = item of question num
$Question = question num
etc
 
 
 
$query1  =$query1 . " ('$item','$question','$user_id_foreign'), "
 
 increment user_id_foreign
 
end for
 
 
 

[javedna]

Hi.

It seems that the '$foreign_user_id' has the same value in both records. So 1st record is inserted while 2nd is not.

As you are adding 1 to your user id for each record you should do something like

 
$query1 = "INSERT INTO answers (answer_score,question_id,user_id) VALUES"
 
 for each record
 
$item = item of question num
$Question = question num
etc
 
 
 
$query1  =$query1 . " ('$item','$question','$user_id_foreign'), "
 
 increment user_id_foreign
 
end for
 
 
 

Cheers mate

That worked like a charm

Thanks

Nabz