[SOLVED] Syntax error being picked up before no records found page shows

[HowdeeDoodee]

I need to know how to pick up syntax errors as a result of user input from a form. The script works fine if the user enters correct spelling of terms to be searched. However, it the user enters a misspelled word or a term not found in the db, I get a syntax error message like the one below. The no records found page is called at the bottom of various query statements housed in IF_ELSE  structures.

 

Here is a typical syntax error message if teh user types in a series of letter a's.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR `Source` = OR `Source` = ) AND (Topic LIKE '%aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa%' at line 1

 

Above the error page call there are numerous query statements in IF_ELSE structures.

 

Just beneath the query statements is the following code. How can I make the error page show if the user types in what would normally cause a syntax error?

 

   $result = mysql_query($query) or die(mysql_error());
    $num_record = mysql_num_rows($result);
    $sql_num_rows = mysql_num_rows($result); 
    //echo "Number of effected rows: $sql_num_rows";
    if($sql_num_rows < (1))      
    {
header("location: /CP/errorpage_norecords.htm"); 
    exit; 
    } 

    if($num_record > $display) { // Only show 1,2,3,etc. when //there are more records found that fit on 1 page
    // when the page is loaded first...
    if(empty($pagenr)) { 
    $pagenr = 1;
    }
    // some variables
    $display = 10; // number of records to display per page
    $max_pages_to_print = 7; // number of pages to show, if you change this you also have to change the variable 'middlenumber', for example:increase this one with two, increase middlenumber with one
    $startrecord = $pagenr * $display; // f$displayirst record to show from the queryresult
    $num_pages = (ceil($num_record/$display)); //set number of pages
    $loopcounter = 0; // counter for whileloop
    $currentpage = $pagenr; // Page where we are at the moment
    $middlenumber = 3; // Number will be decreased from variable currentpage in order to get the currentpage always in the middle
    $colourcounter = 0; // Variable to change the background-color of the <td>
    $i = 1; // variable that will print 1,2,3,etc..
    $x = 0; // variable i use to put always the current, marked page in the middle

[soycharliente]

Don't put a die statement? Wait and test the result for number of rows or soemthing, then else redirect to the page you want.

[GingerRobot]

The mysql_query() function returns false if there is an error. Therefore, if you remove your or die statement, and then modify your if statement, then you can redirect:

 

<?php
 $result = mysql_query($query);
   //echo "Number of effected rows: $sql_num_rows";
    @$num_record = mysql_num_rows($result);
    @$sql_num_rows = mysql_num_rows($result); 

   if($sql_num_rows < (1) || $result === false)      
   {
header("location: /CP/errorpage_norecords.htm"); 
   exit; 
   }
?>

 

I have placed @ symbols before the mysql_num_rows to supress errors, because these would also produce an error if there is a problem with your query. Alternatively, you can have two if statements, the first checking for success on the query, the second checking to see if any rows are returned.

[HowdeeDoodee]

Thank you Ginger Robot. Your suggested code has solved my immediate issue. Could you expand a little more on the two IF statements. Thank you again for the valuable help.

[GingerRobot]

Sure:

 

<?php
  $result = mysql_query($query);
    //echo "Number of effected rows: $sql_num_rows";
    if($result === false)      
    {
header("location: /CP/errorpage_norecords.htm"); 
    exit; 
    }
    $num_record = mysql_num_rows($result);
    $sql_num_rows = mysql_num_rows($result); 
    if($sql_num_rows < (1))      
    {
header("location: /CP/errorpage_norecords.htm"); 
    exit; 
    }

?>

 

The problem is that if there is an error in your sql, then the mysql_num_rows function is going to give you an error when you try it. Therefore, you either need to supress that error(the first code) or only use the function once you know there is no error in the code(the second).

 

Incidently, why do you use the mysql_num_rows() function twice? Even if you do need two variables with the information in then this would be quicker:

 

    $num_record = mysql_num_rows($result);
    $sql_num_rows = $num_record;

 

The mysql_num_rows function is not all that quick, doing it this way avoids calling the function twice.

 

[HowdeeDoodee]

Thank all of you for the valuable comments.