Using Prepared Statement with PHP and MySQL
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.