[RESOLVED] Help needed with getting an answer array from db results

[dmacman1962]

Hi All,

I have several posts here that are resolved and I am starting a new post since this is a different challenge.

In my quiz database I store the answers like this...

answers <-- table name
answer_id------ quiz_id ----- question_id -------- answer ------ correct <-- field names
1----------------1---------------1----------------1--------------0
2----------------1---------------1----------------2--------------1
3----------------1---------------1----------------3--------------0
4----------------1---------------1----------------4--------------0
5----------------1---------------2----------------1--------------0
6----------------1---------------2----------------2--------------0
7----------------1---------------2----------------3--------------1
8----------------1---------------2----------------4--------------0

And I have a function written that compares the answers supplied from my form to an array...

//check the answers and also see if they missed any questions
$a=0;
$ar=0;
//$ans = Total questions answered
$ans = 0;
for ($k=1; $k<=$num_questions; $k++) {
$answers = array(NULL,'C', 'D', 'D', 'C', 'A', 'B', 'A', 'D', 'B', 'C', 'C', 'C', 'B'); //this is the correct answers, the null is because arrays start at 0
if(@$_POST['q'.$k] == $answers[$k]){ //check each answer against its value in the array
$a++;
} else {
//continue
}

$question = $k; //assign each value in the array to avariable
if(!isset($_POST['q'.$k])){
echo "<br><font face='Arial' size='2' color='#FF0000'>Question: <b>" . $k . "</font></b> was not answered. <a href='javascript:history.go(-1)'>Please go back and answer all the questions</a>";
}else{
echo "<br><font face='Arial' size='2' color='#FF0000'>Question: <b>" . $k . '</font></b>. You answered ' . @$_POST['q'.$k] . ', which was ' . ((@$_POST['q'.$k] == $answers[$k])?'correct':'incorrect');
$ans++;
}
$ar++;
}

and what I am trying to do is get the

$answers = array(NULL,'C', 'D', 'D', 'C', 'A', 'B', 'A', 'D', 'B', 'C', 'C', 'C', 'B'); //this is the correct answers, the null is because arrays start at 0

data from my table instead of the 'hard coded' data.

I tried to apply the suggestions from another post...

$query="
SELECT question_id, correct
  FROM answers
  WHERE quiz_id='1'
  order by question_id, correct";

$result = mysql_query($query) or die(mysql_error());
if($result)
  {
  // make an array of alphabet keys to be used in answer array
  $AlphKeys=array('A','B','C','D');

  while($row = mysql_fetch_array($result))
    {
   // count the number of answers already processed with the same 'question_id'
   $count = count ( $answer[$row['question_id']]);  

   // use the count to set a new alphabet key from the $AlphaKeys array  

   $answer[$row['question_id']][$AlphKeys[$count]] = stripslashes($row['correct']);
   }
} 	

But that does not seem to get me what I need.

I am still trying to wrap my brain around multi dim arrays.

I really need some guidance/advise here.

Thanks again,
Don

[Roger_Ramjet]

Given your query you are going to get Qs and As for 1 quiz. Easy to do with that using the question number as the key in the answers array.

// run the query etc
$correct = array();
while ($row = mysql_fetch_array($result)) {
   $q = $row['question_id'];
   $a = $row['correct'];
   $correct[$q] = $a;
}

// now you have the correct answers indexed by question id

// find the correct answers a student has
$correct_answers = array_intersect_assoc($answers,$correct);
// the wrong answers
$wrong_answers = array_diff_assoc($answers,$correct);

Loads of array functions to use here: too many for some people, it gets confusing which one to use and what the returned results actually signify.

[dmacman1962]

Hi RR,

I am trying to follow your lead and here is what I think you are leading me to...

mysql_select_db ($dbname, $conn) or die (mysql_error());
$query="
SELECT question_id, answer
  FROM answers
  WHERE quiz_id='1'
  order by question_id, answer";		
  $result = mysql_query($query) or die(mysql_error());

$answer = array();
while ($row = mysql_fetch_array($result)) {
	$z= $row['question_id'];
	$b = $row['answer'];
	$answer[$z] = $b;
	}

mysql_select_db ($dbname, $conn) or die (mysql_error());
$query="
SELECT question_id, correct
  FROM answers
  WHERE quiz_id='1'
  order by question_id, correct";		
  $result = mysql_query($query) or die(mysql_error());

// run the query etc
$correct = array();
while ($row = mysql_fetch_array($result)) {
   $q = $row['question_id'];
   $a = $row['correct'];
   $correct[$q] = $a;
}

// now you have the correct answers indexed by question id

// find the correct answers a student has
$correct_answers = array_intersect_assoc($answers,$correct);
// the wrong answers
$wrong_answers = array_diff_assoc($answers,$correct); 							
echo '<pre>';
print_r($correct);
echo '</pre>';	

But, I get this back...

Warning: array_intersect_assoc() [function.array-intersect-assoc]: Argument #1 is not an array in D:\Program Files\Apache Group\Apache2\htdocs\izzit\forms\quiz\array_test.php on line 90

Warning: array_diff_assoc() [function.array-diff-assoc]: Argument #1 is not an array in D:\Program Files\Apache Group\Apache2\htdocs\izzit\forms\quiz\array_test.php on line 92

Array
(
[1] => 1
[2] => 1
[3] => 1
[4] => 1
[5] => 1
[6] => 1
[7] => 1
[8] => 1
[9] => 1
[10] => 1
[11] => 1
[12] => 1
[13] => 1
)

I take it you wanted me to query the database twice and get an answers array and a correct array.

Am I even close to what you were thinking???

Thanks
Don

[Roger_Ramjet]

Yes and it will work if you call the answers array by the same name throughout
answer:answers

Learn to debug: when it tells you that something does not exist then go look for the typo, cos that's what it is 9 out of 10 times.

By the way, are all the correct answers '1'? Be a bit better if your test data varied more than that.

[dmacman1962]

I appreciate your help, but I thought I was debugging, an example of where I did not would help.

I changed the array name to answers like you have...

$answers = array();
while ($row = mysql_fetch_array($result)) {
	$z= $row['question_id'];
	$b = $row['answer'];
	$answers[$z] = $b;
	}

and I no longer get the error, but when I print_r (correct, answers, correct_answers, and wrong_answers) I get...

Array
(
[1] => 1
[2] => 1
[3] => 1
[4] => 1
[5] => 1
[6] => 1
[7] => 1
[8] => 1
[9] => 1
[10] => 1
[11] => 1
[12] => 1
[13] => 1
)

Array
(
[1] => Because it’s deceptively simple
[2] => Mining
[3] => Because your gain is my loss
[4] => Because your gain is my loss
[5] => Poor quality and scarcity of goods
[6] => The US consumer
[7] => The zero-sum game
[8] => The zero-sum game
[9] => Increase production to gain more profit
[10] => Shift resources to more profitable areas
[11] => Milton Friedman
[12] => There cannot be more than 2 parties in a transaction
[13] => The Price System does not promote global cooperation
)

Array
(
)

Array
(
[1] => Because it’s deceptively simple
[2] => Mining
[3] => Because your gain is my loss
[4] => Because your gain is my loss
[5] => Poor quality and scarcity of goods
[6] => The US consumer
[7] => The zero-sum game
[8] => The zero-sum game
[9] => Increase production to gain more profit
[10] => Shift resources to more profitable areas
[11] => Milton Friedman
[12] => There cannot be more than 2 parties in a transaction
[13] => The Price System does not promote global cooperation
)

Thanks,
Don

[Roger_Ramjet]

So! I thought this was to mark the answers given by someone who sat the test.

What you have here are
list of correct answer keys: all 1 for some reason
list of the text, what and why? because you queried for that
an empty array because nothing is 'correct'
a full list of the text again because everything is 'wrong'

So the logic works, it is just the input that need changing. Get someone to sit the test and then use this to mark the answers.

[Roger_Ramjet]

Oh I see. So what is your table structure??
something like this was what I expected

id
question text
choice 1 text
choice 2 text
choice 3 text
choice 4 text
correct answer as choice index 1-4

student
id
question no
answer selected as choice index 1-4

This student answer array comes from the post vars as explained to you elsewhere. Normally you would store this in a database for record keeping purposes and I assumed that was what you were doing. You can also use this method to mark the results as soon as the quetion form is submitted.

[dmacman1962]

I am sorry, I was not very specific, so I will explain.

I have a quiz that students take, the questions and answers are stored in my database. ( The quiz_id = 1, currently only 1 quiz. And there are 13 questions each have 4 possible answers [questions 1-13 = question_id 1-13 ).
questions <--table name
ID---quiz_id-----question_id --------------- question <-- field names
1------1---------------1 ------------------ Why does...
2------1---------------2 ------------------ What types of ...
3------1---------------3 ------------------ What is the ...
4------1---------------4 ------------------ Why does ...

And in the answers table, if the answer is the correct one, I have a 1 in the correct field. I am open to suggestions if this is not the correct way to do this.

answers <-- table name
answer_id ---- quiz_id ----- question_id ------- answer ------ correct <-- field names
1-------------1---------------1----------------1--------------0
2-------------1---------------1----------------2--------------1
3-------------1---------------2----------------1--------------1
4-------------1---------------2----------------2--------------0

And what I am trying to do is get an array of the correct answers, currently I have them hard-coded ($answers = array(NULL,'C', 'D', 'D', 'C', 'A', 'B', 'A', 'D', 'B', 'C', 'C', 'C', 'B') which I need to then translate A, B, C, D into question_id 1, answer 1, 2, 3, 4 etc.

Does that make it clearer and am I even taking the correct approach?

FYI this is the first time I am attempting to normalize my data on such a scale. Before I would have saved the records, pretty much in one table.

Thanks,
Don

[Roger_Ramjet]

Well with your current setup you write a different query

"select .... WHERE correct = TRUE"

Horrible setup though, too easy for more than 1 answer to be marked as true for any given question, or none.

quizes table
quiz_id autoinc primary key
quiz_name

questions table
question_id autoinc primary key
question
correct_answer_id foreign key unique not null

quiz_questions
quiz_id
question_id

answers table
answer_id
question_id
answer

Questions have only 1 correct answer so Correct Answer is an attribute of Question and belongs with the questions table. That way only 1 answer id can ever be true for any given question. You can also extend or vary the number of possible answers for any question without problem.

You could even have it set up so that random wrong answers were selected for each question but not now.

Your quiz form has the unique answer_id alongside each answer checkbox so what you get back is an array of answer_ids chosen which you compare with the correct answer ids from the questions table. Saves all that hacking around with multi-dimensional arrays in the form just to find out that they chose option 4 for question 7 , urgh.

[dmacman1962]

Ok, I did a horrible job. I have not made this live yet.

How should I do it?

I know you are light years ahead of me and always will be. But I am not going to get this right without specifics. I understand the concept just dont see where I am off and how to get back to 'non-horrible.'

Please advise.

Thanks,
Don

[dmacman1962]

OK, I have all the tables just as you suggested and populated with the data.

But I am stuck on the query of the questions.

Here is what I thought would work, but doesn't.

$query="SELECT questions.question, questions.question_id, FROM questions, quiz_questions
WHERE quiz_questions.quiz_id='".$quiz_id."' AND questions.question_id=quiz_questions.question_id";

Can you give me some guidance on the query.

Thanks,
Don

[Roger_Ramjet]

Post the tables or we'll just be talking at cross purposes.

[dmacman1962]

FYI, I had a comma in that query that did not belong.

thanks for the help, I will eventually understand the logic.

Don

[dmacman1962]

I don't know if this was the correct way, but I got the questions to work with this query. Now I am working on getting the answers, but I am a little grey on getting the correct answer later.

$query="
SELECT question
  FROM  questions
  JOIN quiz_questions ON quiz_questions.question_id = questions.question_id
  WHERE quiz_questions.quiz_id='".$quiz_id."'
  order by questions.question_id";

Thanks,
Don

[Roger_Ramjet]

Ok you can simplify that a lot.

Get the questions and answers for a quiz in 1 query

"SELECT * FROM questions INNER JOIN answers USING(question_id) WHERE quiz_id=1 ORDER BY question_id, answer_id";

This will return eg
q_id, question, correct_answer_id, a_id, answer

Now I ain't unpicked your display logic cos it will have to change with this new query anyway. To display it you use loop control, search for it to get all you need to know.

Here, you will store the current question id as you loop through the naswers.

$curQ = 0;

while ($row = mysql_fetch_array($result)) {
   // is it a new question?
   if ($row['q_id'] != $curQ) {
      // display the question
      ........
       // store the new q_id
      $curQ = $row['q_id'];
   }
   // display the answer
   echo '<label for "' . $row['a_id'] . '">' . $row['answer'] . '</label>';
   echo '<input type="checkbox" id="' . $row['a_id'] . '" name="' $row['a_id'] . '" />';
}

Now, each answer id checkbox has it's value set to the unique answer id, not some unrelated and non-unique A,B,C, or D so you have much less to manipulate when processing the results.

[dmacman1962]

Thanks, but the query is looking for 'quiz_id' and that is only in the 'quiz_questions' table. I used your table structure.

$query = "SELECT * FROM questions INNER JOIN answers USING(question_id) WHERE quiz_id=2 ORDER BY question_id, answer_id"; 

so how do I add the quiz_questions table to the query and not mess up your logic?

Thanks,
Don

[Roger_Ramjet]

This poxy board software and its 9 error javascript. Just tried to post a reply and it froze FF.

So here it is in brief

"SELECT question_id as qid, question as q, answer_id as aid, answer as a FROM quiz_questions INNER JOIN questions USING(question_id) INNER JOIN answers USING(question_id) WHERE quiz_id=1 ORDER BY question_id, answer_id"; 

$curq = 0;
while ($row = mysql_fetch_array($result)) {
   if ($row['qid'] != $curq) {
      echo $row['q'] . '<br />';
      $curq = $row['qid'];
   }
   echo '<label for "' . $row['aid'] . '">' . $row['answer'] . '</label>';
   echo '<input type="radio" id="' . $row['aid'] . '" name="' $row['qid'] . '" value="' . $row['aid'] . '" />';
}

// process it
$answers = array();
foreach ($_POST as $key=>$val) {
   if ($key != 'submit') {
      $answers[$key] = $val;
   }
}

[Weedpacket]

Roger Ramjet wrote:

This poxy board software and its 9 error javascript. Just tried to post a reply and it froze FF.

What errors are these? I don't see 'em.

[Roger_Ramjet]

Well I've got Firebug installed and it shows a count in the status bar. Probably caused by my ad blocking filters though.

I just got frustrated when I lost my long post and had to retype an answer cos FF locked up and I had to kill it.

[Weedpacket]

Roger Ramjet wrote:

Probably caused by my ad blocking filters though.

That would be it; in the absence of those ads OAS_RICH has nothing to refer to. But from experience that on its own wouldn't be enough to choke FF.

But now to return to your regularly scheduled thread....