Posted on by

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:

1
2
3
 
create database demo;
 

Create Database Table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE TABLE `customers` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(255) NULL DEFAULT NULL,
`last_name` VARCHAR(255) NULL DEFAULT NULL,
`address` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`phone` VARCHAR(255) NULL DEFAULT NULL,
`country` VARCHAR(255) NULL DEFAULT NULL,
`city` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=98
;
 

Insert Some Records to MYSQL Table:

1
2
3
4
5
6
7
8
9
10
11
12
 
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (102, 'Aggi', 'Sandhill', '94896 Mallard Parkway', 'asandhillj@shutterfly.com', '110-327-1151', 'Latvia', 'Ape');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (101, 'Juditha', 'Francecione', '6301 Barby Court', 'jfrancecione3@ebay.com', '947-224-5435', 'Colombia', ' Caicedonia');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (100, 'Luis', 'Johnson', 'Abc house New York', 'evasilik2@de.vu', '425-820-9648', 'Paraguay', 'Benjamín Aceval');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (99, 'Edward', 'Jecks', '53 Main Street', 'pjecks1@blogger.com', '585-624-7083', 'Russia', 'Tëploye');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (98, 'Brok', 'Mc Curlye', '3824 Blackbird Lane', 'bmccurlye0@is.gd', '826-863-5136', 'Sweden', 'Katrineholm');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (97, 'John', 'Gillbey', '338 Sunfield Center', 'ygillbey2@npr.org', '869-198-7138', 'Poland', 'Kamionka Wielka');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (96, 'Will', 'Smith', 'Abc house New York', 'afuge1@rediff.com', '494-246-3738', 'Russia', 'Dzhubga');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (94, 'Ahsan', 'Zameer', 'Abc house', 'ahsan@example.com', '123456789', 'Pakistan', 'Karachi');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (93, 'Conner', 'Krajcik', '91179 Jast Lane\nDesmondfort, KS 22709-7059', 'dbartoletti@example.net', '876.918.8132 x7459', 'Kenya', 'Guidoborough');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (92, 'Edwardo', 'Kling', '688 Araceli Roads\nNorth Charleyville, OR 91371-7211', 'tfarrell@example.org', '(445) 230-9112', 'Solomon Islands', 'Port Garnetville');
 

Create Database Connection:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
<?php
$host = "localhost";
$dbUser = "root";
$password = "";
$database = "demo";
 
$dbConn = new mysqli($host,$dbUser,$password,$database);
 
if($dbConn->connect_error)
{
die("Database Connection Error, Error No.: ".$dbConn->connect_errno." | ".$dbConn->connect_error);
}
?>
 

Fetch all records:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<?php
$allUserStatement = $dbConn->prepare("select * from customers");
$allUserStatement->execute();
$getResult = $allUserStatement->get_result();
 
while($rows = $getResult->fetch_assoc())
{
print_r($rows);
}
 
$dbConn->close();
?>
 

Fetch Single record without bind_result:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
<?php
 
$userName = "Conner";
 
$userStatement = $dbConn->prepare("select id, first_name,last_name from customers where first_name = ?");
 
$userStatement->bind_param("s",$userName);
 
$userStatement->execute();
 
$result = $userStatement->get_result();
$getData = $result->fetch_assoc();
 
print_r($getData);
 
$dbConn->close();
?>
 

Fetch Single record with bind_result:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
<?php
$userName = "Conner";
 
$userStatement = $dbConn->prepare("select id, first_name,last_name from customers where first_name = ?");
 
$userStatement->bind_param("s",$userName);
 
$userStatement->execute();
 
 
$userStatement->bind_result($id, $userName,$lastName);
 
$userStatement->fetch();
 
echo "id: ".$id ."<br>";
echo "first name : ".$userName ."<br>";
echo "last name : ".$lastName ."<br>";
 
$dbConn->close();
?>
 

Fetch Single record with multiple bind_param column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<?php
$id = 90;
$firstName = "Albertha";
$lastName = "Ritchie";
 
$userStatement = $dbConn->prepare("select * from customers where id = ? and first_name = ? and last_name = ? ");
 
$userStatement->bind_param("iss",$id,$firstName,$lastName);
 
$userStatement->execute();
 
 
$result = $userStatement->get_result();
$getData = $result->fetch_assoc();
 
print_r($getData);
 
$dbConn->close();
?>
 

Fetch Multiple records using LIKE Clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
<?php
$firstName = 'c%';
 
$qry = "select * from customers where first_name like ?";
 
$userStatement = $dbConn->prepare($qry);
 
$userStatement->bind_param('s',$firstName);
 
$userStatement->execute();
 
 
$result = $userStatement->get_result();
 
while($rows = $result->fetch_assoc())
{
print_r($rows);
}
 
$dbConn->close();
?>
 

Insert Single Record:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<?php
$firstName = 'Brok';
$lastName = 'Mc Curlye';
$address = '3824 Blackbird Lane';
$email = 'bmccurlye0@is.gd';
$phone = '826-863-5136';
$country = 'Sweden';
$city  = 'Katrineholm';
 
$insertQry = 'insert into customers (first_name, last_name, address, email, phone, country, city) values(?,?,?,?,?,?,?)';
 
$insertStatement = $dbConn->prepare($insertQry);
 
$insertStatement->bind_param('sssssss',$firstName, $lastName, $address, $email, $phone, $country, $city);
 
$insertStatement->execute();
 
$dbConn->close();
?>
 

Insert Multiple Records:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
<?php
$insertQry = 'insert into customers (first_name, last_name, address, email, phone, country, city) values(?,?,?,?,?,?,?)';
$insertStatement = $dbConn->prepare($insertQry);
$insertStatement->bind_param('sssssss',$firstName, $lastName, $address, $email, $phone, $country, $city);
 
$firstName = 'Phylys';
$lastName = 'Jecks';
$address = '53 Main Street';
$email = 'pjecks1@blogger.com';
$phone = '585-624-7083';
$country = 'Russia';
$city  = 'Tëploye';
$insertStatement->execute();
 
 
$firstName = 'Ephrayim';
$lastName = 'Vasilik';
$address = '50 Sheridan Avenue';
$email = 'evasilik2@de.vu';
$phone = '425-820-9648';
$country = 'Paraguay';
$city  = 'Benjamín Aceval';
$insertStatement->execute();
 
 
$firstName = 'Juditha';
$lastName = 'Francecione';
$address = '6301 Barby Court';
$email = 'jfrancecione3@ebay.com';
$phone = '947-224-5435';
$country = 'Colombia';
$city  = ' Caicedonia';
$insertStatement->execute();
 
$dbConn->close();
 
?>
 

Insert Form Data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
<?php
$_POST['first_name'] = 'Aggi';
$_POST['last_name']  = 'Sandhill';
$_POST['address'] = '94896 Mallard Parkway';
$_POST['email'] = 'asandhillj@shutterfly.com';
$_POST['phone'] = '110-327-1151';
$_POST['country'] = 'Latvia';
$_POST['city'] = 'Ape';
 
 
$insertQry = 'insert into customers (first_name, last_name, address, email, phone, country, city) values(?,?,?,?,?,?,?)';
 
$insertStatement = $dbConn->prepare($insertQry);
 
$insertStatement->bind_param('sssssss',$_POST['first_name'], $_POST['last_name'],$_POST['address'], $_POST['email'], $_POST['phone'], $_POST['country'], $_POST['city']);
 
$insertStatement->execute();
 
$dbConn->close();
?>
 

Update Single Column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
<?php
$firstName = "Edward";
$userID = 99;
$updateQry = "update customers set first_name = ? where id = ?";
 
$updateStatement = $dbConn->prepare($updateQry);
 
$updateStatement->bind_param('si',$firstName,$userID);
 
$updateStatement->execute();
 
echo $dbConn->affected_rows;
$dbConn->close();
?>
 

Update Multiple Columns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
<?php
$firstName = "Luis";
$lastName = "Johnson";
$address = 'Abc house New York';
$userID = 100;
$updateQry = "update customers set first_name = ? , last_name = ? , address = ? where id = ?";
 
$updateStatement = $dbConn->prepare($updateQry);
 
$updateStatement->bind_param('sssi',$firstName,$lastName,$address,$userID);
$updateStatement->execute();
 
echo $dbConn->affected_rows;
$dbConn->close();
?>
 

Delete Record:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
<?php
$id = 95;
 
$deleteQry = "delete from customers where id = ?";
$deleteStatement = $dbConn->prepare($deleteQry);
 
$deleteStatement->bind_param('i',$id);
$deleteStatement->execute();
echo $dbConn->affected_rows;
$dbConn->close();
?>
 

 

More from WDB24.com

Posted in PHP

Published by 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.

Leave a Reply

Your email address will not be published. Required fields are marked *