Is there anything wrong with this MySql Query?

Dev Shed Forums Sponsor:

December 11th, 2012, 09:39 AM

ximenao

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 45 m 23 sec
Reputation Power: 0

PHP-DB - Is there anything wrong with this MySql Query?

Hello
I really need help with this problem! I'm sorry if the the solution is rather simple but I'm still kinda new to all this.

I'm coding an online quiz for a client. The person must enter the correct answers into the input textboxes coded below:

Code:

<li><input type="text" name="uno" size="25" maxlength="25" align="baseline" /><br /><br /></li>
                            <li><input type="text" name="dos" size="25" maxlength="25" align="baseline" /><br /><br /></li>
                            <li><input type="text" name="tres" size="25" maxlength="25" align="baseline" /><br /><br /></li>

Once they submit the answers they are sent to the processing script shown below:

Code:

<?php
  $uno = $_POST['uno'];
  $dos = $_POST['dos'];
  $tres = $_POST['tres'];
  
$query="SELECT 
MATCH (q1) AGAINST ('$uno' IN BOOLEAN MODE) as ans1,
MATCH (q2) AGAINST ('$dos' IN BOOLEAN MODE) as ans2,
MATCH (q3) AGAINST ('$tres' IN BOOLEAN MODE) as ans3,
 FROM db_4_test";
$data=@mysql_query($query) or die(mysql_error()); 

	echo "<p align=\"justify\">2. In the passage you have just read there are seven Spanish Speaking countries. List them in the spaces provided.</p>";
if($data["ans1"]!='0' && $data["ans1"]!='') {
        $a = 1;
		echo "<p><font color=\"#7E4B01\" size=\"+1\">\"<b>$uno</b> is correct!\"</font></p>";
} else {
        $a = 0;
		echo "<p><font color=\"#F00\" size=\"+1\">\"<b>$uno</b> is NOT a Spanish Speaking country found in the passage you have just read!</font></p>";
}

if($data["ans2"]!='0' && $data["ans2"]!='') {
        $b = 1;
		echo "<p><font color=\"#7E4B01\" size=\"+1\">\"<b>$dos</b> is correct!\"</font></p>";
} else {
        $b = 0;
		echo "<p><font color=\"#F00\" size=\"+1\">\"<b>$dos</b> is NOT a Spanish Speaking country found in the passage you have just read!</font></p>";
}

if($data["ans3"]!='0' && $data["ans3"]!='') {
        $c = 1;
		echo "<p><font color=\"#7E4B01\" size=\"+1\">\"<b>$tres</b> is correct!\"</font></p>";
} else {
        $c = 0;
		echo "<p><font color=\"#F00\" size=\"+1\">\"<b>$tres</b> is NOT a Spanish Speaking country found in the passage you have just read!</font></p>";
}


$ex1sum = $a + $b + $c;
$ex1percent = ($ex1sum/3)*100;

	echo "<p>You scored <b>$ex1sum</b> out of 13 total marks in Exercise IV.</p>";
	
	if ($ex1percent >= 0 && $ex1percent <= 50)
  echo "<p><img src=\"images/exam_sorry_01.jpg\" width=\"287\" height=\"25\" alt=\"\" border=\"0\"><a href=\"quiz.php\"><img src=\"images/exam_sorry_02.jpg\" width=\"63\" height=\"25\" alt=\"\" border=\"0\"></a></p>";  
    
	
	if ($ex1percent >= 51 && $ex1percent <= 84)
  echo "<p><img src=\"images/exam_tryagain_01.jpg\" width=\"210\" height=\"25\" alt=\"\" border=\"0\"><a href=\"quiz.php\"><img src=\"images/exam_tryagain_02.jpg\" width=\"68\" height=\"25\" alt=\"\" border=\"0\"></a></p>";
	
	if ($ex1percent >= 85 && $ex1percent <= 100)
  echo "<p><img src=\"images/exam_muybueno.jpg\" width=\"80\" height=\"25\" alt=\"\" border=\"0\"></p>";

  
?>

The script is a fulltext search which searches a series of columns in a database table and is supposed to find the correct answer. For example if the student enters "Cuba" it is supposed to return the answer as correct in other words display "Cuba is correct!". If the person enters say England it is supposed print "England is NOT a Spanish Speaking country found in the passage you have just read!"
However no matter what the answer is it always gives the answer wrong even if it is present in the database. If I use just one argument (e.g.: if($data["ans1"]!='0' ) ) it gives every answer correct even it is not in database.
Can someone please help me? Is there anything wrong with this script that I am missing?

Thanks in advance
ximenao

December 11th, 2012, 09:55 AM

gw1500se

Contributing User

Join Date: Jul 2003

Posts: 2,885

Time spent in forums: 1 Year 2 Weeks 3 Days 8 h 17 m 9 sec
Reputation Power: 581

First of all you should not be using the MySQL extensions. Switch to PDO.

Second, mysql_query returns a resource not an array. You need to follow that with something like mysql_fetch_row.

Third, please enclose your code in [ PHP ] tags not [ CODE ] tags. See the sticky at the top of the forum.

__________________
There are 10 kinds of people in the world. Those that understand binary and those that don't.

December 12th, 2012, 08:06 AM

ximenao

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 45 m 23 sec
Reputation Power: 0

Quote:

Originally Posted by gw1500se

Thank you for responding. Sorry about the PHP tags thing; new to this column.
I tried mysql_fetch_row but I get the same result.
The code:

PHP Code:


		
			
 $query="SELECT 

MATCH (q1) AGAINST ('$uno' IN BOOLEAN MODE) as ans1,

MATCH (q2) AGAINST ('$dos' IN BOOLEAN MODE) as ans2,

MATCH (q3) AGAINST ('$tres' IN BOOLEAN MODE) as ans3,

 FROM db_4_test";

$data=@mysql_query($query) or die(mysql_error()); 

$row = mysql_fetch_row($data);



echo "<p align=\"justify\">2. In the WORD SLEUTH there are seven Spanish Speaking countries. List them in the spaces provided.</p>";

if($row["ans1"]!='1') {

        $a = 1;

        echo "<p><font color=\"#7E4B01\" size=\"+1\">\"<b>$uno</b> is correct!\"</font></p>";

} else {

        $a = 0;

        echo "<p><font color=\"#F00\" size=\"+1\">\"<b>$uno</b> is NOT a Spanish Speaking country found in the WORD SLEUTH!</font></p>";

}

Thanks in advance

December 12th, 2012, 08:28 AM

gw1500se

Contributing User

Join Date: Jul 2003

Posts: 2,885

Time spent in forums: 1 Year 2 Weeks 3 Days 8 h 17 m 9 sec
Reputation Power: 581

The next step is to make sure the query returned what you expect. I'd add this before the 'if/else' block:

PHP Code:


		
			
echo "$query<br />";

echo "<pre>";

print_r($row);

echo "</pre>";

December 12th, 2012, 08:43 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,879

Time spent in forums: 1 Month 2 Weeks 2 Days 7 h 20 m 28 sec
Reputation Power: 813

Hi,

there are several problems with your code that you should fix before moving on:

The mysql_ functions are obsolete, as gw1500se already said. They are no longer maintained and will eventually die out. Choose one of the contemporary extensions
Your code is wide open to SQL injections, because you just dump the POST parameters into your query strings. This allows any attacker to manipulate the queries and possibly fetch secret data, change or delete data etc. So don't do that! Use prepared statements, which are available through the above mentioned extensions.
Do not display internal error messages. They help attackers and irritate legitimate users. I know this "or die(mysql_error())" pattern still floats around everywhere on the Internet, but that doesn't make it right.
Don't repeat the same code for every question, just make that a loop.
Your database design is wrong. In the relational model, data sets are stored in rows, not in column groups and not in multiple tables. You might wanna ask the MySQL guys on how to propery design your database for your specific purpose.

December 12th, 2012, 02:27 PM

ximenao

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 45 m 23 sec
Reputation Power: 0

Thank you Jacques1 and gw1500se.
I echoed both $query and $row and it is displaying this:

Quote:

SELECT *, MATCH (q1) AGAINST ('Cuba' IN BOOLEAN MODE) as ans1, MATCH (q2) AGAINST ('Chile' IN BOOLEAN MODE) as ans2, MATCH (q3) AGAINST ('Argentina' IN BOOLEAN MODE) as ans3 FROM db_4_test
Array
(
[0] => Cuba Chile Peru Panama Argentina Spain Nicaragua
[1] => Cuba Chile Peru Panama Argentina Spain Nicaragua
[2] => Cuba Chile Peru Panama Argentina Spain Nicaragua
)

It really looks like I may have to overhaul this database; I believe I can figure that out. I have already begun looking into PDO as an alternative. I am not that familiar with it can either of you recommend any other online resources that can help me out more directly with fulltext searching?

Thanks once again
ximenao