MySQL query prepared statement not returning any result

Question

When I am running this query it is not returning any results. I believe there must be a syntax error as it is stopping the code from running later down the page. Anyway, I can't see where I am going wrong.

If nothing seems to be the problem with the syntax I can post my database structure, if necessary.

    $tag = $_GET['tag'];
    $stmt = $mysqli->prepare('SELECT trips.trip_id FROM tags JOIN trips ON trips.post_id = tags.post_id WHERE tag = ?');
    $stmt->bind_param('s', $tag);
    $stmt->execute();
    $stmt->bind_result($trip_id); ?>

    <div id="map_canvas<?php echo $trip_id ?>" style="height:400px;"></div><?php

Update: I have ran the error reporting script and this is what is comes out with...

Fatal error: Call to a member function bind_param() on a non-object in /Users/.../server/inc/feed-algorithm.php on line 37

Increase your error_reporting and set display_errors to 1. `error_reporting(E_ALL | E_NOTICE)` and `ini_set('display_errors', 1)`. — lxg, Oct 06 '14 at 20:26
@JohnRuddell The connection will be closed implicitly. It is not often necessary to do it manually except as a micro-optimization. — Michael Berkowski, Oct 06 '14 at 20:29
In your query the parameter is represented by `?` and you're binding it as `s`. How about to change your query to: `'SELECT trips.trip_id FROM tags JOIN trips ON trips.post_id = tags.post_id WHERE tag = :s'`? — Caffé, Oct 06 '14 at 20:29
@MichaelBerkowski its still generally a good practice to close it though — John Ruddell, Oct 06 '14 at 20:29
@Caffé that's a PDO named placeholder. MySQLi only supports `?`. This code is correct, except for the missing `fetch()`. — Michael Berkowski, Oct 06 '14 at 20:30
@Caffé Then he would have to change `bind_param('s'` to `bindParam(':s'` because this is mysqli and not pdo. — Ohgodwhy, Oct 06 '14 at 20:30
@Caffé That is PDO syntax/placeholders. `WHERE tag = :s` will not work. OP is using `mysqli_` — Funk Forty Niner, Oct 06 '14 at 20:32
@David If you believe that there is a syntax error, then you're not checking for them. — Funk Forty Niner, Oct 06 '14 at 20:33
Ah, now you updated with the error. Your `prepare()` call didn't succeed, so check `echo $mysqli->error;` to find out why. Always test for success. `if (!$stmt) echo $mysqli->error;` — Michael Berkowski, Oct 06 '14 at 20:33
Based on your other question http://stackoverflow.com/q/26197031/ - it seems like it's just that, a "non-object", therefore your query failed. Check your JOIN syntax. You may need a LEFT JOIN. — Funk Forty Niner, Oct 06 '14 at 20:54
bind_result($trip_id); ?> check weather "trip_id" your binding is available first. Or you may need to move that line ahead of the others and check query was successful — Mubo, Oct 06 '14 at 21:35

Wes Grant · Accepted Answer · 2014-10-06T21:04:37.423

Look at PHP manual http://php.net/manual/en/mysqli.quickstart.prepared-statements.php and set up your error statements properly. Your prepare statement should look like this:

if(!$stmt = $mysqli->prepare('SELECT trips.trip_id FROM tags JOIN trips ON trips.post_id = tags.post_id WHERE tag = ?')){
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Of course, alternatively, throw and handle exceptions if you would like to be a little more elegant and keep your errors from users.

Also follow error checking in the Example 2 section. It appears your problem is going to be in the prepare statement though because if it was successful, it would not return a 'non-object'.

MySQL query prepared statement not returning any result

1 Answers1