Posted on by

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:

1
2
3
4
5
6
7
8
 
$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

1
2
3
4
5
6
 
if($dbConn->connect_error)
{
die("Database Connection Error, Error No.: ".$dbConn->connect_errno." | ".$dbConn->connect_error);
}
 


Get Current Database Charset:

1
2
3
 
$dbConn->get_charset();
 

Check Mysqli Client Version:

1
2
3
 
$dbConn->client_version;
 

Select Statement:

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

Get Num Rows:

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

Get Fields Count:

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

Get Affected Rows:

1
2
3
 
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

1
2
3
4
5
6
 
$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);
$row = $rs->fetch_row();
print_r($row);
 

Output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
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:

1
2
3
4
5
6
7
8
9
10
11
 
$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);
while ($row = $rs->fetch_assoc())
{
echo $row['first_name'];
echo "<br>";
$row['last_name'];
}
 

Fetch Object:

1
2
3
4
5
6
7
8
9
10
11
 
$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:

1
2
3
 
$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.

1
2
3
4
5
6
 
$selectQry = "Select * from customers";
$rs = $dbConn->query($selectQry);
$row = $rs->fetch_row();
$dbConn->free($rs);
 

Insert record in Mysql Table:

1
2
3
4
 
$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:

1
2
3
 
echo $dbConn->insert_id;
 

Insert record using real escape string:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
$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:

1
2
3
4
5
6
 
$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:

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

Close Mysqli Database Connection:

1
2
3
 
$dbConn->close();
 

Also read:

 

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 *