Using Prepared Statement with PHP and MySQL

Posted in Tutorials

Tweet This Share on Facebook Bookmark on Delicious Digg this Submit to Reddit

In the previous tutorial, we used mysqli API that comes with PHP to query a MySQL database.  In this tutorial, instead of sending a SQL query statement, we are going to use prepared statement to query the database.  By using prepared statements we no longer need to do mysqli_real_escape_string as we did in the update database tutorial.

Basing off of the same code as previous tutorial linked here, we continue as before with opening a database connection with mysqli_connect() and getting an database handle $db…

prepared statements

prepared statements

Like before, we construct a query string called $query.  But this time, we have a question mark instead of ‘publish’.  The question mark is a placeholder for binding params.  You can have more than one question marks.  Using the question mark, we also don’t need the quotes around the string param.

We pass that $query string into mysqli_prepare and it gives us back a “prepared statement” which we store in $stmt.

We then pass that prepared statement into mysqli_stmt_bind_param(), telling it the string type “s”, and the variable $status which holds the value to be passed into the “question mark” placeholder in the statement.

The string type “s” means that the question mark placeholder is supposed to take a string.  And we a passing in variable $status by reference.

Because it is by reference, we can then assign a value “publish” to the variable “$status” and it will use the value in the query string where the question mark is — as long as you make the assignment before calling mysqli_stmt_execute().

When we call mysqli_stmt_execute(), it will return false if the query fails.  But other wise, we can retrieve the resultset by binding the preparted statement and the fields of the resultset with mysqli_stmt_bind_result().

The two fields returned in the result set, will come out as $post_title and $post_date which we display in a loop …

display resultset

display resultset

mysqli_stmt_fetch will return false when it can no longer retrieve a record. And that is when we end the while loop.

Finally, close the statement and close the db handle with mysqli_stmt_close and mysqli_close.