count the number of record according to a query in MySql

[aneeka]

good day!

Please help me here, I need to count the record according to my condition but with my code it only counts the last record in the table in MySql. Here is my code.. Thanks!

<?php
//establishing connection
mysql_connect("localhost", "abc", "bbb") or die(mysql_error());
$submit = $_POST['submit'];
$cancel = $_POST['cancel'];			
$answer = $_POST['answer'];	

//connecting to the database
mysql_select_db("xxx") or die(mysql_error());

			$result = mysql_query("SELECT * FROM qqq") or die(mysql_error()); 
			echo "<center><font size=2 face=Arial><b>qwerty</b></font></center><br/>";
			echo "<font size=1 face=Arial><b>Type in True if the statement is correct and False if otherwise.</b></font><br/><br/>";		
			echo "<table cellpadding = 1><tr><th><font size = 1>Answer</th><th><font size = 1>Question</th></tr>";
			while($row = mysql_fetch_array($result))
			{
				echo "<tr><td>";  
				echo "<font size = 1><input name='answer' type='text'>";		
         		echo "</font></td><td><font size = 1>";	
				$question = $row['question'];
				$answer = $row['answer'];
				$id = $row['questionid'];
				echo $question;
      			echo "</td></tr>"; 
			}
			echo "</table>";
			echo "</font></center>";
if ($cancel == 'Cancel')
	{
		echo "<META HTTP-EQUIV=REFRESH CONTENT='0; URL=http://abc/qwerty/adggd/dgdg.php'>";
		exit;
	}
	
else if ($submit == 'Submit')
	{
	$date = date("F d, Y");
	$count = mysql_query("SELECT COUNT(*) FROM qqq WHERE question = '$question' && answer = '$answer' && questionid = '$id'") or die(mysql_error());
	$score = mysql_result($count,0,0);
	
	mysql_query("INSERT INTO results (idnumber, fullname, score, exam_title, exam_dept, exam_date) 
					VALUES ('$pangalanUser','$fullname', '$score','Awareness and Reminders', 'ECM Production', '$date')") 
						or die(mysql_error());  					
	echo "<script>alert('Your score is $score! Score is already saved.')</script>";
	echo "<META HTTP-EQUIV=REFRESH CONTENT='0; URL=http://abc/qwerty/adggd/dgdg.php'>";
	}
?>

[cwarn23]

Try this.

$count = mysql_query("SELECT * FROM qqq WHERE question = '$question' && answer = '$answer' && questionid = '$id'") or die(mysql_error());
	$score = mysql_num_rows($count);

[aneeka]

i tried your code but it always display that the score is 1.

what shall i do?

[cwarn23]

I see the bug. Try the following.

$count = mysql_query("SELECT * FROM qqq WHERE question = '$question' AND answer = '$answer' AND questionid = '$id'") or die(mysql_error());
	$score = mysql_num_rows($count);

[aneeka]

same output goes..the score is 1 :(

[cwarn23]

Well your where clause is filtering it to 1 row. I would suggest changing the where clause to match what you need. At the moment it looks like the where clause is designed to match 1 row and that could be your problem.

[aneeka]

do you have any suggestion of how will i solve this.

im really having a hard time..

thanks.

[cwarn23]

Well how are you trying to filter it. Are you trying to filter it to one row because that is how it is currently set up. The items after the word WHERE will need changing to match your needs. What is the basic theory behind how it needs to be filtered and what is the column structure. If you can explain those two things I may be able to piece together a mysql query for ya.

[aneeka]

this code is for an online exam, so i need to display all the questions from the mysql table. from that i inserted a text box where the examinee will input his answer. the exam is identification in type. i named the text box, answer and the displayed question, question.

[aneeka]

when the examinee submits the answers, the code will count the correct answers that the examinee got.

[cwarn23]

Then is this what you need?

$count = mysql_query("SELECT * FROM qqq") or die(mysql_error());

[aneeka]

im writing the where clause so i can count the correct answers that the examinee got, his answers must match to the answers from the table.

[cwarn23]

Perhaps one of the following two.

$count = mysql_query("SELECT * FROM qqq WHERE question = '$question' AND answer = '$answer'") or die(mysql_error());

$count = mysql_query("SELECT * FROM qqq WHERE answer = '$answer'") or die(mysql_error());

[aneeka]

i think the problem is the code doesn't get the value of each answers from the text box and check the if it matches in the table.

[aneeka]

it displays the same...

i tried everthing..

[cwarn23]

I just checked your script and found $question, $answer and $id all equals the value of the last row. This is because of the following section.

while($row = mysql_fetch_array($result))
			{
				echo "<tr><td>";  
				echo "<font size = 1><input name='answer' type='text'>";		
         		echo "</font></td><td><font size = 1>";	
				$question = $row['question'];
				$answer = $row['answer'];
				$id = $row['questionid'];
				echo $question;
      			echo "</td></tr>"; 
			}

Now is $answer meant to be $_POST or $_GET etc. That would be your problem.

[aneeka]

from that, what do i need to do?

is there an alternative of that code?

[cwarn23]

Well $answer. it equals $row in the loop of the table resulting of $answer being the value of the last row of the table. But what I'm saying is shouldn't $answer and it's surrounding variables be replaced with the user input?

[aneeka]

so how do i get the input of the user from the loop?

[cwarn23]

I think you should read a forms tutorial as some of the things your unsure about are the most vital things to know in php.

[aneeka]

ok thanks! :)