PHP MYSQLi Object Oriented Prepared Statements for beginners

In my last tutorial we have covered prepared statement using procedural PHP MYSQLi. In this tutorial I am going to cover prepared statement using mysqli object oriented.

If you are beginner and want to learn basics of MYSQLi OOP then visit my post PHP MYSQLi Object Oriented Tutorial for Beginners

Lets start with what is prepared statement and how its work.

According to w3schools.com:

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

 

  1. Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled “?”). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
  2. The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it
  3. Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values

MYSQLi Prepared Statement:

In a simple PHP MYSQLi, you perform only 2 steps (query(),fetch_*()) and get database result. But in prepared statement you have to perform 3 or 4 steps to get your desired database record. So this means now more than two method will use for fetching mysqli records which are showing below.

Function Description
prepare($query) Prepares the SQL query, and returns a statement handle to be used for further operations on the statement.

mysqli_stmt_bind_param($types, $variable1) Binds variables to a prepared statement as parameters. $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
execute() Executes a prepared Query
bind_result($variable1) Binds variables to a prepared statement for result storage. $variable1 is a variable to be found.
get_result() Gets a result set from a prepared statement.
fetch() Fetch results from a prepared statement into the bound variables.

Now you know the basic methods which are used in prepared statement. Now let’s see all of them in action.


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.