Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Search with no match generates Query Prep Failed error

    Hello all,

    Hopefully this is a simple one...

    I have a search routine that works perfectly until a non-existent search element is entered. When no record is matched, the Query Prep Failed message is displayed and processing stops. There is probably standard treatment for this problem, but so far I haven't found the solution.

    I have highlighted the code below that generated the error. I took this code from the online PHP manual and I believe it is doing its job properly as coded. How can I capture that result, generate a graceful "in house" error message, and continue the program without interruption?

    Code:
    $library->connectDB(&$mysqli,&$stmt);
    	if ($mysqli->connect_errno) {echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}
    $stmt = $mysqli->prepare($sql);		
    	if (false === $stmt) {die('Query Prep Failed' . $mysqli->error);}
    $stmt->bind_param('s',$nameSearch);   //string
    $stmt->execute();
    	if (false===$stmt) {die('execute() failed: '.$stmt->error);}
    $stmt->store_result()

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    A query that doesn't match any rows does NOT produce the error you are getting. In fact, since the query isn't even executed until the $stmt->execute(); statement, you won't know until after that point if the query ran without any errors or if it matched any rows.

    What information did $mysqli->error provide as to why the prepare() statement failed?
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Yeppers.
    $stmt is false since the preparation has failed which is normal behaviour (and how you programmed it). The error will tell you why. The $mysqli is good, otherwise you'd have landed against a fatal error, so that means your $sql is no good.
    My presumption is that the search criteria includes which properties to search. So when nothing is entered a query results in having a WHERE = 'ACRITERIA' or whatever in it, effectively comparing against nothing (and therefore syntactically broken).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #4
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I did some additional testing, which only added more intrigue to the mystery...

    Using a prior search of "gen" produced five results. I deleted all five records and the program displayed an empty list without throwing an error. Yet if I use a random 3-letter search "bli" it throws the error.

    $mysqli->error did not generate a message.

    Output from: echo $SQL, ": ",$nameSearch: SELECT * WHERE Name LIKE CONCAT('%', ? , '%') : bli Query Prep Failed

    This query works for all kinds of searches that exist within the database -- I have many records containing "z" and they pop up as expected. But when I tried "zick" it crashes. What is going on???

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Well, the only thing I see is you are not selecting from anything. So that, will probably be your problem.
    You should have an error there. What's in the errno?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    The query string you posted has no FROM term and is invalid. The posted query does produce a $mysql->error output of - 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 'WHERE Name LIKE CONCAT('%', ? , '%')' at line 1

    If your's didn't you are either not posting your actual offending code or the place you are outputting the error message it is hiding it in the source of the page or the error output is a mix from more than one query/variables...

    Finally, what's this method doing - $library->connectDB(&$mysqli,&$stmt);? There's no good reason to have any code other than your ->prepare() statement touching the $stmt variable.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    I have a likely guess as to what is causing your symptoms (i.e. a "Query Prep Failed" message when a query doesn't match any rows and no $mysqli->error output.)

    Have you copy/pasted your if ( ) {die('Query Prep Failed' . $mysqli->error);} code, so that it is also after and trying to test the result from a _fetch() statement? If so, A) you didn't change the message in it, resulting in confusion, and B) when there's no row to fetch from a result set, you get a false value that would trigger the die() statement. No rows from a query that runs is not a mysql error and there's nothing in $mysqli->error
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #8
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I found my problem, and I think I have been staring at this thing way too long.....

    As it happens, there are two queries: One generates a count all of the records from the query, and the second appends a limit statement to the original query for pagination. It was the second one that got me, and had I only scrolled down a few more lines I would have seen the problem. I have now labeled the error messages as "1" and "2" etc, to give me a heads up when it's late and I am not at my sharpest...

    Thanks for your diligent work, and sorry that I did not spot that one myself!

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    Finally, what's this method doing - $library->connectDB(&$mysqli,&$stmt);? There's no good reason to have any code other than your ->prepare() statement touching the $stmt variable.
    There's one more thing to point out here as well.
    Reference passing via call-time pass by reference in PHP is gone as of PHP 5.4.0. If you run the above code in a 5.4.0 environment, it will issue a fatal error so you'll need to get rid of any call-time pass-by-reference calls you have in your code. If you require reference passing all you do is modify the signature of the function to accept a reference.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    I have now labeled the error messages as "1" and "2" etc, to give me a heads up when it's late and I am not at my sharpest...
    If you write/use your own query method/function with error display/logging (i.e. using trigger_error()) in it, you can use debug_backtrace() to get the file name and line number where your code called the query method/function at.

    The following is not specific to prepared queries, but the error handling in it could be used in any case -

    Code:
    function xmysqli_query($mysqli,$query){
    	$result = $mysqli->query($query);
    	if($result){return $result;}
    	// error handling
    	$trace = debug_backtrace();
    	$trace = $trace[0];
    	trigger_error(
    		"Query error - Query: $query, Error: $mysqli->error, in {$trace['file']} on line {$trace['line']}",
    		E_USER_NOTICE);
            return false;
    }
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •