#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,455
    Rep Power
    594
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by gw1500se
    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.


    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,455
    Rep Power
    594
    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>"
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Thank you Jacques1 and gw1500se.
    I echoed both $query and $row and it is displaying this:

    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
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by ximenao
    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?
    The MySQL manual:
    http://dev.mysql.com/doc/refman/5.5/...xt-search.html
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Okay thanks again for the help Jacques1. Really appreciate it.

IMN logo majestic logo threadwatch logo seochat tools logo