PHP MYSQLi Object Oriented Tutorial for Beginners

If you are following my blog you notice that in all my posts I use procedural mysqli instead of using PDO or mysqli object oriented class. I know working with OOP is quite difficult for beginners but if you try to understand and learn basics of object oriented programming I am sure you will be start using php mysqli class extension.

In this tutorial I am going to show you how to connect, select, insert, update and delete records using built-in php mysqli class with basic examples.

I will not cover prepared statement in this post. If you have basic understanding of MYSQLi and want to learn prepared statement then read my post “PHP MYSQLi Object Oriented Prepared Statements for beginners”

PHP MYSQLi Object Oriented

Create Database Connection:

$host = "localhost";
$dbUser = "root";
$password = "";
$database = "demo";

$dbConn = new mysqli($host,$dbUser,$password,$database)

$host = "localhost";

$dbUser = "root";

$password = "";

$database = "demo";

$dbConn = new mysqli($host,$dbUser,$password,$database)

If database is not connected then I will Show error number and error message and stop script execution

if($dbConn->connect_error)
{
	die("Database Connection Error, Error No.: ".$dbConn->connect_errno." | ".$dbConn->connect_error);
}

if($dbConn->connect_error)

{

die("Database Connection Error, Error No.: ".$dbConn->connect_errno." | ".$dbConn->connect_error);

}

Get Current Database Charset:

$dbConn->get_charset();

1	$dbConn->get_charset();

Check Mysqli Client Version:

$dbConn->client_version;

1	$dbConn->client_version;

Select Statement:

$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);

1 2	$selectQry = "Select * from customers"; $rs = $dbConn->query($selectQry);

Get Num Rows:

echo "Num rows: ".$rs->num_rows;

1	echo "Num rows: ".$rs->num_rows;

Get Fields Count:

echo "Fields count: ".$rs->field_count;

1	echo "Fields count: ".$rs->field_count;

Get Affected Rows:

echo "Affected rows: ".$dbConn->affected_rows;

1	echo "Affected rows: ".$dbConn->affected_rows;

Note: Num rows and fields count values will get from result object and Affected Rows value will get from connection object.

Fetch Single Row:

fetch_row() get result as an enumerated array

$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);
$row = $rs->fetch_row();
print_r($row);

$selectQry = "Select * from customers";

$rs = $dbConn->query($selectQry);

$row = $rs->fetch_row();

print_r($row);

Output:

Array
(
    [0] => 1
    [1] => Alvina
    [2] => Champlin
    [3] => 3605 Larson Meadow
Casandraville, VT 73844-0293
    [4] => schiller.thomas@example.net
    [5] => +15718182740
    [6] => Congo
    [7] => North Brody
)

Array

(

[0] => 1

[1] => Alvina

[2] => Champlin

[3] => 3605 Larson Meadow

Casandraville, VT 73844-0293

[4] => schiller.thomas@example.net

[5] => +15718182740

[6] => Congo

[7] => North Brody

)

Fetch Associative Array:

$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);
while ($row = $rs->fetch_assoc())
{
	echo $row['first_name'];
	echo "<br>";
	$row['last_name'];
	
}

$selectQry = "Select * from customers";

$rs = $dbConn->query($selectQry);

while ($row = $rs->fetch_assoc())

{

echo $row['first_name'];

echo "<br>";

$row['last_name'];

}

Fetch Object:

$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);

while ($obj = $rs->fetch_object())
{
	echo $obj->first_name;
	echo "<br>";
	echo $obj->last_name;
}

$selectQry = "Select * from customers";

$rs = $dbConn->query($selectQry);

while ($obj = $rs->fetch_object())

{

echo $obj->first_name;

echo "<br>";

echo $obj->last_name;

}

Fetch All Records:

$rs->fetch_all(MYSQLI_ASSOC);

1	$rs->fetch_all(MYSQLI_ASSOC);

fetch_all method return all records found in query. It takes 1 argument with 3 different types. MYSQLI_ASSOC return associative array, MYSQLI_NUM returns numeric array and MYSQLI_BOTH returns both associative and numeric array.

Free Results:

Free() method frees the memory associated with a result.

$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);
$row = $rs->fetch_row();
$dbConn->free($rs);

$selectQry = "Select * from customers";

$rs = $dbConn->query($selectQry);

$row = $rs->fetch_row();

$dbConn->free($rs);

Insert record in Mysql Table:

$qryInsert = "insert into customers (first_name, last_name, address, email, phone, country, city) values('Ahsan','Zameer','abc house, karachi','ahsan@abc.com','0000 0000','Pakistan','Karachi')";
$rsInsert = $dbConn->query($qryInsert);

1 2	$qryInsert = "insert into customers (first_name, last_name, address, email, phone, country, city) values('Ahsan','Zameer','abc house, karachi','ahsan@abc.com','0000 0000','Pakistan','Karachi')"; $rsInsert = $dbConn->query($qryInsert);

Get Inserted Id:

echo $dbConn->insert_id;

1	echo $dbConn->insert_id;

Insert record using real escape string:

$firstName 	= $dbConn->real_escape_string("Ahsan");
$lastName 	= $dbConn->real_escape_string("Zameer");
$address 	= $dbConn->real_escape_string("abc house");
$email 		= $dbConn->real_escape_string("ahsan@abc.com");
$phone 		= $dbConn->real_escape_string("987654321");
$country 	= $dbConn->real_escape_string("Pakistan");
$city 		= $dbConn->real_escape_string("Karachi");

$qryInsert = "insert into customers (first_name, last_name, address, email, phone, country, city) values('$firstName','$lastName','$address','$email','$phone','$country','$city')";

$rsInsert = $dbConn->query($qryInsert);

$firstName = $dbConn->real_escape_string("Ahsan");

$lastName = $dbConn->real_escape_string("Zameer");

$address = $dbConn->real_escape_string("abc house");

$email = $dbConn->real_escape_string("ahsan@abc.com");

$phone = $dbConn->real_escape_string("987654321");

$country = $dbConn->real_escape_string("Pakistan");

$city = $dbConn->real_escape_string("Karachi");

$qryInsert = "insert into customers (first_name, last_name, address, email, phone, country, city) values('$firstName','$lastName','$address','$email','$phone','$country','$city')";

$rsInsert = $dbConn->query($qryInsert);

Update record in Table:

$updateQry = "update customers set country = 'UK' where country = 'United Kingdom' ";
$rsUpdate = $dbConn->query($updateQry);
echo "Updated Rows: ".$dbConn->affected_rows;
echo "<br>";

$updateQry = "update customers set country = 'UK' where country = 'United Kingdom' ";

$rsUpdate = $dbConn->query($updateQry);

echo "Updated Rows: ".$dbConn->affected_rows;

echo "<br>";

Delete record in Table:

$deleteQry = "delete from customers where id = 94";
$rsDelete = $dbConn->query($deleteQry);
echo "Deleted Rows: ".$dbConn->affected_rows;

$deleteQry = "delete from customers where id = 94";

$rsDelete = $dbConn->query($deleteQry);

echo "Deleted Rows: ".$dbConn->affected_rows;

Close Mysqli Database Connection:

$dbConn->close();

1	$dbConn->close();

PHP MYSQLi Object Oriented Tutorial for Beginners

PHP MYSQLi Object Oriented

Create Database Connection:

Get Current Database Charset:

Check Mysqli Client Version:

Select Statement:

Get Num Rows:

Get Fields Count:

Get Affected Rows:

Fetch Single Row:

Output:

Fetch Associative Array:

Fetch Object:

Fetch All Records:

Free Results:

Insert record in Mysql Table:

Get Inserted Id:

Insert record using real escape string:

Update record in Table:

Delete record in Table:

Close Mysqli Database Connection:

Published by Ahsan Zameer

Leave a Reply Cancel reply