Using Prepared Statement with PHP and MySQL

Posted in Tutorials

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…

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 …

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.

 

---