PHP MYSQLi Procedural Prepared Statements for beginners

PHP MYSQLi Prepared Statement is one of the best way to execute same statement repeatedly with high efficiency. But it requires additional steps and functions to execute query which sometimes confuse most of the php beginners.

PHP provides MYSQLi procedural, MYSQLi OOP and PHP PDO to perform database operation like select, insert, update and delete. Prepared Statement works with all three of them. But in this tutorial I will cover MYSQLi Procedural.

So What is prepared statement and how it is work?

According to Wikipedia:

In database management systems (DBMS), a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.”

Now you can guess that prepared statement executes in 3 steps:

  1. Prepare: Create statement template with placholders(‘?’) and send it to DBMS. Like "Select * from users where email = ?"
  2. Compile: Then DBMS parse(compile) the statment template and store the result without executing it.
  3. Execute: In this step data is bind or passed against the placeholders(‘?’) and database execute the statement. User can execute many statement with different values

Also read:

MYSQLi Prepared Statement:

If you worked with simple mysql functions to query database then you know that to execute query you used mysqli_query() function. And then you used mysqli_fetch_* functions to get your desired result. This means you get your desired result in 2 steps. But in prepared statement you have to perform minimum 4 steps in selection and 3 steps in insert, update, delete to get your desired result. This means, now you will use additional mysqli functions to perform CRUD operations.

Function Description
mysqli_prepare($connection, $query) Prepares the SQL query, and returns a statement handle to be used for further operations on the statement.
It takes 2 parameters. $connection is a connection string and $query is a sql query.
mysqli_stmt_bind_param($stmt,$types. $variable1) Binds variables to a prepared statement as parameters.$stmt is a statement identifier. $types is a string that contain string type for corresponding bind variables. List of string type is given below. $variable1 is a number of variable and length of a string $types.

Character Description
i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets
mysqli_stmt_execute($stmt) Executes a prepared Query
mysqli_stmt_bind_result($stmt,$variable1) Binds variables to a prepared statement for result storage. $stmt is a statement identifier. $variable1 is a variable to be found.
mysqli_stmt_get_result($stmt) Gets a result set from a prepared statement. $stmt is a statement identifier
mysqli_stmt_fetch($stmt) Fetch results from a prepared statement into the bound variables.$stmt is a statement identifier

Now I think you have enough knowledge of prepared statement and functions which are used in query to the database. So now let’s do it in practical.


Create Database:

Create Database Table:

Insert Some Records to MYSQL Table:

Create Database Connection:

Fetch all records:

Fetch Single record without bind_result:

Fetch Single record with bind_result:

Fetch Single record with multiple bind_param column:

Fetch Multiple records using LIKE Clause:


Insert Single Record:

Insert Multiple Records:

Insert Form Data:


Update Single Column:

Update Multiple Columns:


Delete Record:

 

Posted in PHP

Author: Ahsan Zameer

I am a web developer and love to search new stuff on web. Trying different approaches and ideas to make web developing more interesting and enjoyable.