Thursday, 14 April 2016

php - MySQLi prepared statement issue

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:




$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 :-/

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...