Today I will discuss MySQLi Prepared Statement and its Benefits.
Prepared statements provide a better way to execute SQL queries. Apart from being safe, the prepared statements allow the reusability of code. Since the statement need to be prepared once and can be executed any number of times by just replacing the parameters, these statements execute faster.
Basically, the prepared statement works as follows. At first we create a string representing the SQL query that contains certain placeholders as the parameters. When the prepare() function is called with the query, the database compiles the query. However, the query is not executed yet. In order to execute the query, the function bind_param() need to be called. This function binds the values to the query parameters. Finally, we need to call the execute() method for executing the query. Hence, the prepared statements make it possible to execute the query as many times as required by just replacing the parameters and calling the execute() method.
MySQLi Prepared Statement and its Benefits – PHP Functions
The following functions available in the mysqli class allow us to create the prepared statements.
prepare() – This function takes the query string as a parameter and returns the statement object.
bind_param() – We use this method of the statement object to bind the parameter value wit a parameter in the query.
execute() – In order to execute the prepared statement, we nee to call the execute() method of the statement object.
Benefits
The following list indicates the benefits that we achieve using prepared statements.
- Since the database prepares the query only once, they are faster and require less bandwidth.
- These statements are reusable.
- Furthermore, they prevent SQL Injection attacks, if we use these statements correctly.
The following example shows how to use prepared statements for inserting records. When the user enters the data in the form and clicks on the insert button, the PHP script executes that runs the insert command as a prepared statement.
Further Reading
Examples of Array Functions in PHP