Multi Inserts

[moiseszaragoza]

I have been trying to inserts multiple records on a loop

but I am getting a error

totalQuestions=10;
		$i=0;
		while ($i <= $totalQuestions-1):
			// GET VARS 
			$question_ID 	= $_POST['question_ID'.$i];
			$answers 		= $_POST['answers'.$i];


		// INSERT 
		$MySQL_InsertUsers = mysql_query("
		INSERT INTO UsersAnswers   	(
			QuizID, UserID, question_ID, answers
		) VALUES (
			'$Quiz_ID'	, '$UserID', '$question_ID', '$answers'
		)"
	, $ctech_con);



	$i++;
	endwhile;

ERROR

Error
SQL query:

INSERT INTO UsersAnswers( QuizID, UserID, question_ID, answers )
VALUES (

'17', '22', '24', '0'
)
INSERT INTO UsersAnswers( QuizID, UserID, question_ID, answers )
VALUES (
'17', '22', '23', '0'
)
INSERT INTO UsersAnswers( QuizID, UserID, question_ID, answers )
VALUES (
'17', '22', '20', '0'
)

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO UsersAnswers ( QuizID, UserID, question_ID, answers ) VALUES ( '17' ' at line 3

[laserlight]

The statement output makes it clear that you constructed 3 SQL statements, not 1. This means that you would need to use a semi-colon to terminate each SQL statement. However, more likely you want to construct 1 SQL statement:

INSERT INTO UsersAnswers( QuizID, UserID, question_ID, answers )
VALUES ('17', '22', '24', '0'), ('17', '22', '23', '0'), ('17', '22', '20', '0')

either that, or use prepared statements.

[djjjozsi]

your mysql syntax is correct, but laserlight's mthod is better!

but i don't know what you want to do with these things:
totalQuestions ?

endwhile; ?

try this if you like the while... (and close the while with } please... )

$MySQL_InsertUsers = mysql_query("INSERT INTO UsersAnswers (QuizID, UserID, question_ID, answers) VALUES ('$Quiz_ID', '$UserID', '$question_ID', '$answers')" , $ctech_con);

/ replace the enters into spaces. /

[laserlight]

djjjozsi wrote:

but i don't know what you want to do with these things:
totalQuestions ?

endwhile; ?

try this if you like the while... (and close the while with } please... )

That is the alternative syntax for control structures, though it is rarely used when there is no embedding of PHP in the client side code.

[djjjozsi]

thanks.

could this error comes from the version of the mysql server?

becouse this sql insert query works fine on mine.

[laserlight]

djjjozsi wrote:

becouse this sql insert query works fine on mine.

Looking more carefully at the code, you are right: the 3 separate SQL statements are executed separately, so what I pointed out is not significant (other than that an actual multiple insert or prepared statements would be more efficient).

Maybe moiseszaragoza's database does not correspond to the SQL statements he or she is trying to run (e.g., incorrect table or column names).