CODEWITHSUNDEEP
phpmysqlarraysvariablesforeach
cwiggo
cwiggo

Reputation: 2599

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

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.

Upvotes: 0

Views: 928

Answers (2)

zachjs
zachjs

Reputation: 1748

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());

Upvotes: 2

Logan Serman
Logan Serman

Reputation: 29880

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.

Upvotes: 4

Related Questions