Help improving code

[dan_bsb]

Hey, I made the following code that checks the answers of a form:

<?PHP
include 'connect.php';
$points = 0;

foreach($_POST as $id => $answer){
$result = mysql_query("SELECT id, ans FROM questions WHERE id = '".$id."'");
$row = mysql_fetch_array($result);
    if($answer == $row['ans']) {
        $points++;
}
}
echo "Your score: $points";
?>

The problem is that it makes a sql query for every question, which makes the script a bit slow...

 

Is there a way to make a single sql query that select all the answers?

[ginerjm]

try using the in clause as in:

 

$result = mysql_query("SELECT id, ans FROM questions WHERE id in ['$id']");

(Note how I used single quotes)

 

where you build $vals in your loop and execute the query outside the loop.

 

and $vals is a comma-separated string of values.

[dan_bsb]

try using the in clause as in:

 

$result = mysql_query("SELECT id, ans FROM questions WHERE id in ['$id']");

(Note how I used single quotes)

 

where you build $vals in your loop and execute the query outside the loop.

 

and $vals is a comma-separated string of values.

 

@ginerjm, thanks for your answer!

 

But can you show an example of how to do this? I'm relatively new to php...

Edited January 7, 2014 by dan_bsb

[Psycho]

First, change your form so all the 'answers' are in a sub-key of the $_POST array, e.g. $_POST['answers'], rather than putting them in the root of $_POST. This way if there was any other information included in the POST data (for example a user ID) it will be logically separated from the answers.

 

Then, you can easily get the list of the questions and the answers.

<?php
 
//Get the POSTed answers
$answersAry = $_POST['answers'];
//Get the array of question IDs
$questionIDsAry = array_keys($_POST['answers']);
//Convert all ID values to integers (make safe for SQL query)
$questionIDsAry = array_map('intval', $questionIDsAry);
//Remove invalid values
$questionIDsAry = array_filter($questionIDsAry);
//Convert remaining IDs to comma separated string for use in query
$questionIDsSQL = implode(', ', $questionIDsAry);

//Create and run query
$query = "SELECT id, ans FROM questions WHERE id IN({$questionIDsSQL})";
$result = mysql_query($query);

//Iterate through the query results and compare to the POSTed answers
$points = 0;
while($row = mysql_fetch_assoc($result))
{
    if($answersAry[$row['id']] == $row['ans'])
    {
        $points++;
    }
}
echo "Your score: $points";
?>

Edited January 7, 2014 by Psycho

[Barand]

Single query to read all the answers into an array

$res = $db->query("SELECT id,ans FROM questions");
while ($row = $res->fetch_assoc()) {
    $ans[$row['id']] = $row['ans'];
}

Gives something like

$ans = array (
    1 => 'A',
    2 => 'C',
    3 => 'D'
    );

Then you have your POST array like

$post = array (
    1 => 'A',
    2 => 'B',
    3 => 'D'
    );

Now use array_intersect_assoc()

$correct = array_intersect_assoc($ans,$post);

/* $correct array **********
Array
(
    [1] => A
    [3] => D
)
****************************/

Edited January 7, 2014 by Barand

[dan_bsb]

 

First, change your form so all the 'answers' are in a sub-key of the $_POST array, e.g. $_POST['answers'], rather than putting them in the root of $_POST. This way if there was any other information included in the POST data (for example a user ID) it will be logically separated from the answers.

 

Then, you can easily get the list of the questions and the answers.

<?php

 

//Get the POSTed answers

$answersAry = $_POST['answers'];

//Get the array of question IDs

$questionIDsAry = array_keys($_POST['answers']);

//Convert all ID values to integers (make safe for SQL query)

$questionIDsAry = array_map('intval', $questionIDsAry);

//Remove invalid values

$questionIDsAry = array_filter($questionIDsAry);

//Convert remaining IDs to comma separated string for use in query

$questionIDsSQL = implode(', ', $questionIDsAry);

 

//Create and run query

$query = "SELECT id, ans FROM questions WHERE id IN({$questionIDsSQL})";

$result = mysql_query($query);

 

//Iterate through the query results and compare to the POSTed answers

$points = 0;

while($row = mysql_fetch_assoc($result))

{

    if($answersAry[$row['id']] == $row['ans'])

    {

        $points++;

    }

}

echo "Your score: $points";

?>

 

 

 

My form is like this:

<?PHP
$select = "SELECT * FROM questions";
$result = mysql_query($select);
    echo "<form id='questions' method='post' action='check.php'>";
while($result2 = mysql_fetch_array($result)) {
    echo "<p>Question: $result2[question]</p>
    <p>a) <input name='$result2[id]' type='radio' value='1'> $result2[option1]</input></p>
    <p>b) <input name='$result2[id]' type='radio' value='2'> $result2[option2]</input></p>
    <p>c) <input name='$result2[id]' type='radio' value='3'> $result2[option3]</input></p>
    <p>d) <input name='$result2[id]' type='radio' value='4'> $result2[option4]</input></p>";
}
    echo "<input type='submit' name='Submit' value='Check!' />
    </form>
  </div>";
 ?>

So how do I "change my form so all the 'answers' are in a sub-key of the $_POST array"?

 

Thanks!

[Barand]

Instead of

name = '$result2[id]'

have

name = 'answers[$result2[id]]'