How to use a foreach loop to dynamically execute a mysql query?

Question

I am doing a simple database builder function that takes a question id, 4 options an id and a count variable.

All I want to do is in the foreach, take the options and carry out a mysql_query() on all the seperate options variables. As you can see I have put them in an array so I can loop through them in the foreach.

I think the $var variable i.e. the option value is chucking the error. The error mysql threw back to me is:

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 'option, order) VALUES ('e3397df9241278bfcad1945420398baa817acd7a', '005e913c9c1a' at line 1

Heres my code:

$question_id = $_POST['question_id'];
$option1 = $_POST['option_1'];
$option2 = $_POST['option_2'];
$option3 = $_POST['option_3'];
$option4 = $_POST['option_4'];

$vars = array($option1, $option2, $option3, $option4);

$count = 1;
foreach ($vars as $var){

    $id = sha1(microtime());
    echo "ID: ".$id."<br/>Q ID: ".$question_id."<br/>Option: ".$var."<br/>Order: ".$count."<br/><br/>";

    $result = mysql_query("INSERT INTO question_options (id, question_id, option, order) VALUES ('$id', '$question_id', '$var', '$count') ") or die(mysql_error());
    $count++;
}

Really appreciate if you could help show me what is wrong. Cheers.

Logan Serman · Accepted Answer · 2012-10-08 00:58:44Z

4

Order is a MySQL keyword. You need to escape it:

$result = mysql_query("INSERT INTO question_options (`id`, `question_id`, `option`, `order`) VALUES ('$id', '$question_id', '$var', '$count') ") or die(mysql_error());

Also you should take a look at MySQLi or PDO to parameterize your queries. The mysql_ functions are deprecated.

answered Oct 8, 2012 at 0:58

Logan Serman

30k28 gold badges107 silver badges144 bronze badges

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

2 Comments

cwiggo Over a year ago

Yes, lifesavers, not a clue that they were keywords, thanks, i'll keep keywords in mind from now on! Thanks Again!

cwiggo Over a year ago

plus, yes i've used pod and mysqli, keep it in mind, and probably convert soon

zachjs · Accepted Answer · 2012-10-08 01:01:01Z

2

The terms option and order are special names for MySQL.

Use the following statement instead:

$result = mysql_query("INSERT INTO question_options (id, question_id, `option`, `order`) VALUES ('$id', '$question_id', '$var', '$count') ") or die(mysql_error());

answered Oct 8, 2012 at 1:01

zachjs

1,7481 gold badge11 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to use a foreach loop to dynamically execute a mysql query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related