MySQLi prepared statement issue

Question

I'm (trying to) to move my queries from unprepared statements to MySQLi prepared statement. What I learned so far this should happen in the below pattern:

$post_id = $mysqli->real_escape_string($_GET['id']);

$query_blog = 'SELECT * FROM blog WHERE id = ?';

$result_blog = $mysqli->prepare($query_blog);
$result_blog->bind_param('i', $post_id);
$result_blog->execute();
$result_blog->bind_result();
$result_blog->close();
$mysqli->close();

If I echo $post_id and $query_blog I'm getting the correct results. But when it comes to bind_param it gets stuck. No idea what could be wrong. Any idea how to run this?

In total my code looks like this:

<?php

$post_id = $mysqli->real_escape_string($_GET['id']);

$query_blog = 'SELECT * FROM blog WHERE id = ?';

$result_blog = $mysqli->prepare($query_blog);
$result_blog->bind_param('i', $post_id);
$result_blog->execute();
$result_blog->bind_result();
$result_blog->close();
$mysqli->close();

if ($result_blog):
    if(mysqli_num_rows($result_blog)>0):
       while($blog_entry = mysqli_fetch_assoc($result_blog)):

         //Do something

        endwhile;
     endif;
endif;

Many thanks!

Edit: If I print_r after prepare the statement I get:

mysqli_stmt Object ( [affected_rows] => 0 [insert_id] => 0 [num_rows] => 0 [param_count] => 1 [field_count] => 7 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 1 )

If I print_r after bind_param the statement I get:

mysqli_stmt Object ( [affected_rows] => 0 [insert_id] => 0 [num_rows] => 0 [param_count] => 1 [field_count] => 7 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 1 )

If I print_r after execute the statement I get:

mysqli_stmt Object ( [affected_rows] => -1 [insert_id] => 0 [num_rows] => 0 [param_count] => 1 [field_count] => 7 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 1 )

So affected_rows changes to -1 Does it mean that there is no row found with ID=1, because that is definitely in my database.

Edit2:

If I add the below statements I get data from the query.

$result_blog->bind_result($id, $headline);
$result_blog->fetch();
print_r($headline);

But how can I loop trough the results?

if ($result_blog):
        if(mysqli_num_rows($result_blog)>0):
           while($blog_entry = mysqli_fetch_assoc($result_blog)):

             //Do something

            endwhile;
         endif;
    endif;

This seems not to work :-/

Do you get any errors? Btw: no need to mangle (escape) your data when using prepared and parameterized queries — JimL
The error could be at bind_result and not bind_param, because bind_result expects atleast one param. — hungersoft
I print_r after every step and pasted the results in my first entry — Beji
Actually not "no need" to use real_escape_string, but complete nonsense altogether! It has absolutely no place here, you are mangling your input data with this. (In case of a 1 that's not the real error reason here, but it is fundamentally wrong nevertheless. Remove it from your code, and your brain when it comes to prepared statements, otherwise this will trip you up next time the input data is of a more complex type.) — CBroe
Got the results now from the query. I edited my question, so that you can see what happend. I also will remove the real_escape_string. Thanks for the advice! Now I'm stuck when it comes to the loop part. Also any idea how to do that? I think the if statements are not needed any longer, but even without the while loop does not work. — Beji

SaschaP SaschaP · Accepted Answer · 2018-03-25T13:00:36

A simple example for a prepared statement could look like this:

// Set query
$query = "SELECT field_1, field_2 FROM table WHERE field_3 = ?";

// prepare statement
$stmt = mysqli->prepare($query);

// bind param (marked with '?' in $query) and define type
// look [http://php.net/manual/de/mysqli-stmt.bind-param.php] for further information for this function
$stmt->bind_param('i', $foo);

// define variables which will contain content after fetching the result
$stmt->bind_result($field_1, $field_2);

// execute query and check if successful
if($stmt->execute() === false)
{
    // an error occurred, do something!
    // throw new Exception('error!');
}

// loop through results
while($stmt->fetch())
{
    // this part will be executed for every result
    // do something with $field_1 and $field_2
}

// release memory
$stmt->close();

for your code this could look like this:

$query = "SELECT id, headline FROM blog WHERE id = ?";
$stmt = mysqli->prepare($query);
// assuming that $_GET['id'] is an integer
$stmt->bind_param('i', $_GET['id']);
$stmt->bind_result($id, $headline);
if($stmt->execute() === false)
{
    // an error occurred, do something!
}
while($stmt->fetch())
{
    // do something with $id and $headline
}
$stmt->close();

MySQLi prepared statement issue

1 Answers