Site icon WDB24

PHP MYSQLi Procedural Prepared Statements for beginners

Ahsan Zameer
php featured image

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:

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=95
;
 

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 (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 (95, 'Cassandry', 'Croxon', '90 Shelley Terrace', 'ccroxon0@ustream.tv', '797-800-2312', 'China', 'Dongling');
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');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (91, 'Rosalinda', 'Lubowitz', '2124 Kemmer Drives\nWest Vernaland, MI 64378', 'fhettinger@example.org', '240.601.4749', 'Antigua and Barbuda', 'Lake Stewarttown');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (90, 'Albertha', 'Ritchie', '7611 Gleason Gateway\nEast Willie, MT 59038', 'mcglynn.flavie@example.org', '287-241-7595', 'Ethiopia', 'East Sophia');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (89, 'Malcolm', 'Feest', '930 Hayes Lane Apt. 932\nBashirianstad, CT 81574', 'braun.nakia@example.org', '704-484-1277', 'Saint Lucia', 'Priceville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (88, 'Tremaine', 'Ortiz', '60507 Loyce Lodge\nPort Earline, DC 81599', 'qcollier@example.org', '1-597-867-0182 x8315', 'Brazil', 'New Melany');
 

Create Database Connection:

1
2
3
4
5
6
7
8
9
10
 
<?php
$conn = mysqli_connect($host,$dbUser,$password,$database);
 
if(!$conn)
{
die('unable to connect to database'.mysqli_error());
}
?>
 

Fetch all records:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
<?php
 
$qry = 'select * from customers';
 
$allUserStatement = mysqli_prepare($conn,$qry);
 
mysqli_stmt_execute($allUserStatement);
 
$getResult = mysqli_stmt_get_result($allUserStatement);
 
while($rows = mysqli_fetch_assoc($getResult))
{
print_r($rows);
}
?>
 

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
20
21
22
23
 
<?php
$userName = "Conner";
 
$qry = 'select id, first_name,last_name from customers where first_name = ?';
 
$userStatement = mysqli_prepare($conn, $qry);
 
mysqli_stmt_bind_param($userStatement, 's',$userName);
 
mysqli_stmt_execute($userStatement);
 
$result = mysqli_stmt_get_result($userStatement);
$getData = mysqli_fetch_assoc($result);
 
echo "id: ".$getData['id'];
echo "<br>";
echo "First name: ".$getData['first_name'];
echo "<br>";
echo "Last name: ".$getData['last_name'];
echo "<br>";
?>
 

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
23
24
 
<?php
$userName = "Conner";
 
$qry = 'select id, first_name,last_name from customers where first_name = ?';
 
$userStatement = mysqli_prepare($conn, $qry);
 
mysqli_stmt_bind_param($userStatement, 's',$userName);
 
mysqli_stmt_execute($userStatement);
 
mysqli_stmt_bind_result($userStatement, $id, $first_name, $last_name);
 
mysqli_stmt_fetch($userStatement);
 
echo "id: ".$id;
echo "<br>";
echo "First name: ".$first_name;
echo "<br>";
echo "Last name: ".$last_name;
echo "<br>";
?>
 

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
 
<?php
$id = 93;
$firstName = "Conner";
$lastName = "Krajcik";
 
$qry = 'select * from customers where id = ? and first_name = ? and last_name = ? ';
 
$userStatement = mysqli_prepare($conn, $qry);
 
mysqli_stmt_bind_param($userStatement, 'iss',$id,$firstName,$lastName);
 
mysqli_stmt_execute($userStatement);
 
$result = mysqli_stmt_get_result($userStatement);
$getData = mysqli_fetch_assoc($result);
 
print_r($getData);
?>
 

Fetch Multiple records using LIKE Clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
<?php
$firstName = 'c%';
 
$qry = "select * from customers where first_name like ?";
 
$userStatement = mysqli_prepare($conn,$qry);
mysqli_stmt_bind_param($userStatement,'s',$firstName);
 
mysqli_stmt_execute($userStatement);
$result = mysqli_stmt_get_result($userStatement);
 
while($rows = mysqli_fetch_assoc($result))
{
print_r($rows);
}
?>
 

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 = 'Ahsan';
$lastName = 'Zameer';
$address = 'Abc house';
$email = 'ahsan@example.com';
$phone = '123456789';
$country = 'Pakistan';
$city  = 'Karachi';
 
$insertQry = 'insert into customers (first_name, last_name, address, email, phone, country, city) values(?,?,?,?,?,?,?)';
 
$insertStatement = mysqli_prepare($conn,$insertQry);
 
mysqli_stmt_bind_param($insertStatement,'sssssss',$firstName, $lastName, $address, $email, $phone, $country, $city);
 
mysqli_stmt_execute($insertStatement);
 
mysqli_close($conn);
?>
 

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
 
<?php
$insertQry = 'insert into customers (first_name, last_name, address, email, phone, country, city) values(?,?,?,?,?,?,?)';
$insertStatement = mysqli_prepare($conn,$insertQry);
mysqli_stmt_bind_param($insertStatement,'sssssss',$firstName, $lastName, $address, $email, $phone, $country, $city);
 
$firstName = 'Cassandry';
$lastName = 'Croxon';
$address = '90 Shelley Terrace';
$email = 'ccroxon0@ustream.tv';
$phone = '797-800-2312';
$country = 'China';
$city  = 'Dongling';
mysqli_stmt_execute($insertStatement);
 
 
$firstName = 'Ariela';
$lastName = 'Fuge';
$address = '2725 Manufacturers Hill';
$email = 'afuge1@rediff.com';
$phone = '494-246-3738';
$country = 'Russia';
$city  = 'Dzhubga';
mysqli_stmt_execute($insertStatement);
 
 
$firstName = 'Yolande';
$lastName = 'Gillbey';
$address = '338 Sunfield Center';
$email = 'ygillbey2@npr.org';
$phone = '869-198-7138';
$country = 'Poland';
$city  = 'Kamionka Wielka';
mysqli_stmt_execute($insertStatement);
 
mysqli_close($conn);
?>
 

Insert Form Data:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
<?php
$insertQry = 'insert into customers (first_name, last_name, address, email, phone, country, city) values(?,?,?,?,?,?,?)';
 
$insertStatement = mysqli_prepare($conn,$insertQry);
 
mysqli_stmt_bind_param($insertStatement,'sssssss',$_POST['first_name'], $_POST['last_name'],$_POST['address'], $_POST['email'], $_POST['phone'], $_POST['country'], $_POST['city']);
 
mysqli_stmt_execute($insertStatement);
 
mysqli_close($conn);
?>
 

Update Single Column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
<?php
$firstName = "John";
$userID = 97;
$updateQry = "update customers set first_name = ? where id = ?";
 
$updateStatement = mysqli_prepare($conn,$updateQry);
 
mysqli_stmt_bind_param($updateStatement, 'si',$firstName,$userID);
mysqli_stmt_execute($updateStatement);
 
echo mysqli_affected_rows($conn);
 
mysqli_close($conn);
 
?>
 

Update Multiple Columns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
<?php
$firstName = "Will";
$lastName = "Smith";
$address = 'Abc house New York';
$userID = 96;
$updateQry = "update customers set first_name = ? , last_name = ? , address = ? where id = ?";
 
$updateStatement = mysqli_prepare($conn,$updateQry);
 
mysqli_stmt_bind_param($updateStatement, 'sssi',$firstName,$lastName,$address,$userID);
mysqli_stmt_execute($updateStatement);
 
echo mysqli_affected_rows($conn);
 
mysqli_close($conn);
?>
 

Delete Record:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
<?php
$id = 98;
 
$deleteQry = "delete from customers where id = ?";
$deleteStatement = mysqli_prepare($conn,$deleteQry);
 
mysqli_stmt_bind_param($deleteStatement, 'i',$id);
mysqli_stmt_execute($deleteStatement);
 
echo mysqli_affected_rows($conn);
?>
 

Exit mobile version