quiz with database

[Aronn00]

hello. i have to make a quiz with database questions and answers and i got stucked. I have a database with 2 tables: quiz_q(with ID_question and Question) abd quiz_r(with ID_answer and Answer). I use ID_question and ID_answer to know the answers for one question.
Example:
ID_question:"1",Question:"What HTML stands for?",
ID_answer:"1", Answer:"Home Tool Markup Language" ,
ID_answer:"1" Answer:"Hyper Text Markup Language" ,
ID_question:"2", Question:"Who is making the Web standards?"
ID_answer:"2", Answer:"Microsoft"
ID_answer:"2" Answer:"The World Wide Web Consortium" ETC.
What i want to make is to display the question followed by the answers. Here is the code i made but is only showing the first question with it's answers:

<?php 
$con = mysql_connect("localhost","root",""); 
mysql_select_db("quiz", $con); 
$result=mysql_query("SELECT * FROM quiz_q");
$result2=mysql_query("SELECT * FROM quiz_r");

$i=1;
while($row = mysql_fetch_array($result)){
  echo $row['Question'];
  echo "<br>";
  while($row2= mysql_fetch_array($result2)){
  	if($row['ID_question']==$row2['ID_answer'])
		echo $row2['Answer'];
		echo "<br>";
	}
  echo "<br />";
}


?>

Need help and Thanks!

[bradgrafelman]

First and foremost: Stop Using the MySQL Extension!

It's only showing the first question and its answers because once you finishing looking at every row (more on this in a bit) in the quiz_r table in the first iteration of the outer while loop, you've consumed all of the rows in the result set for the query resource stored in $result2. The internal pointer does not "reset" itself automatically back to the beginning of the set once you reach the end.

However, rather than doing that manually (which is certainly possible), what you should (IMHO) do is use the SQL server in a more intelligent manner. For example, why SELECT all of the rows in quiz_r when you really only care about the ones that match the ID of the question you're currently outputting? Why not only SELECT the answers that are relevant instead?

Now, you might be tempted to agree with that and instead execute more refined SELECT queries inside the loop that processes quiz_q - queries that only SELECT answers from quiz_r WHERE the ID column matches the ID value of the current question. However, note that this would result in a SQL query being executed inside of a loop that's processing another SQL query - this is a huge red flag (IMHO) that indicates a JOIN would be more appropriate.

And now we arrive at my final suggestion: Consider using a single SQL query that JOINs the quiz_q and quiz_r tables together and ORDERs the results by the question ID (or the actual text, whichever seems more appropriate to you). You'd then process the results by keeping track of the question ID you previously encountered (in order to know when you've crossed the boundary from one question to the next). Something like:

$prev_questoin = -1;
while( /* retrieve the next row from the result set as normal here */ )
    {
    // assuming $row was assigned in the while() condition
    if( $row['ID_question'] != $prev_question )
        {
        // this is a new question - output line breaks, headers, etc. as desired

    $prev_question = $row['ID_question'];
    }

// output the answer
}

[Aronn00]

I use MySQL Extension because there are the only ive learned. We started PHP several week ago and what i try to do we didn't learned at school yet. And about your suggestion is too ambiguous for me but i will try to see what i can do

[bradgrafelman]

Aronn00;11022875 wrote:

I use MySQL Extension because there are the only ive learned.

You might want to stop learning (or unlearn, if possible 🙂) it then, for the reasons outlined in the thread linked above. (It's a similar argument to why one should not "learn" that the Earth is flat in the present age.)

[Aronn00]

So after some strugle I understanted what u meant. So I did this:

$result=mysql_query("Select quiz_q.ID_question,quiz_q.Question,quiz_r.Answer FROM quiz_q INNER JOIN quiz_r ON quiz_q.ID_question = quiz_r.ID_answer ORDER BY quiz_q.ID_question");


$i=1;
$prev_question=-1;
while($row = mysql_fetch_array($result)){
 if($row['ID_question']!=$prev_question){
 	echo $row["Question"] . "<br>";
	$prev_question=$row['ID_question'];
	}
	echo $row['Answer'] . "<br>";

}

And it worked i guess, but i have lots of answers with <> tags and they are written as html tags and i need to change to special characters. Anyways thanks for help!

[bradgrafelman]

Aronn00;11022883 wrote:

i need to change to special characters.

I hope you don't mean change the actual data stored in your DB? Instead, you should be properly escaping that data after retrieving it from the DB and before you echo it into an HTML document (in other words, in cases where you don't intend '<x' to look like an HTML tag).

To that end, consider applying a function like [man]htmlentities/man on the data you retrieve from the DB.

[Aronn00]

I thought there was a Sql command that i could update the tables to change the < or > into entities but I don't think it does. So i will use your adivice

[Aronn00]

Yep, it worked. Thank you very much. You saved me a lot of time. I was just starting to change them manually.

[Aronn00]

One more question: is this code corect if I want to use radio buttons for answers and the name will be the same as the question number and their values with be 1, 2, 3 and recount when a new question apear?

$i=0;
$j=1;
$prev_question=-1;
while($row = mysql_fetch_array($result)){
 	if($row['ID_question']!=$prev_question){
 		echo htmlentities($row["Question"]) . "<br>";
		$prev_question=$row['ID_question'];
		$i++;
		$j=1;
	}
	echo "<input type='radio' name='$i' value='$j'>";
	echo htmlentities($row['Answer']) . "<br>";
 	$j++;
}

[bradgrafelman]

Aronn00;11022887 wrote:

I thought there was a Sql command that i could update the tables to change the < or > into entities

There is - you could do a REPLACE() in an UPDATE query.

However, don't do that (or change them manually), because that violates the notion of data integrity. If you want a less-than symbol in your data, then store a less-than symbol in your database. Storing "&lt;" makes less sense, because you wouldn't walk up to someone and ask a question like "Is 14 ampersand 'L' 'T' semicolon 15?" rather than "Is 14 less-than 15?" (where "less-than" is the English interpretation of '<' in mathematical context).

Aronn00;11022891 wrote:

One more question: is this code corect if I want to use radio buttons for answers and the name will be the same as the question number and their values with be 1, 2, 3 and recount when a new question apear?

IMHO, no, that code could be written much better simply by using the ID values you already have - the question IDs and the answer IDs.

The problem with your current method is that it requires you to later re-construct the entire result set in exactly the same order as it was when you originally generated the Q/A page. You can't reliably do that right now, because you never ORDER the answers in any particular way. But even if you did, that still leaves you with the work of re-SELECT'ing and re-ORDER'ing everything all over again.

Instead, here are three suggestions I have to make things a little easier:

1. For the name, use the format answers[###], where '###' is a placeholder for a unique value (integers are nice - I'm assuming that's what your ID columns are in your DB?) that identifies the question.

2. Use the 'ID_question' value from your DB as the unique value above. Again, I say this because I assume that a) this is an integer column, and b) you have actually created a PRIMARY or UNIQUE key on this column.

3. For the value of the radios, I personally would add an integer column to your quiz_r table such that it is unique for any given value of ID_answer. In other words, it would allow you to say "answer #3 for question #1". You could then use that "3" value as the value of the radios. I would then also create a UNIQUE (or, even better, a PRIMARY) key on the (ID_Answer, This_New_Field) columns.

   Otherwise, you need to add an ORDER BY clause to your SELECT statement such that the answers are reliably ordered each time. Even then, you're making the assumption that no one will change/add answers after someone has viewed the Q/A form (since the "third" answer could change by the time the form is submitted).

Note that #1 was only suggested since it makes processing the submitted answers easier - you'd just do a [man]foreach/man loop over the $_POST['answers'] array.