Using MySQLi in PHP as OOPS

With newer version PHP5.5 mysql_*() methods are deprecated. Normally most of developers are using this method for database operations. After PHP5.5 there are two options for performing database operations:

  • Use MySQLi
  • Use PDO

PDO stands for PHP Data Objects. PDO allow us to use various different types of database with same syntax. PDO is suggested when you are working with different types of database such as MySQL, SQLite, Oracle etc.
In MySQLi, i stands for improved means those are mysql’s improved methods for database operations. With MySQLi we can perform operation in two ways. i.e. Object oriented approach and procedural approach. In this article i am using Object oriented approach.

Connect to Database
For establishing connection to database we need to specify Host, User, Password, and Database name in constructorof mysqli class.

<?php
	// Assign Config
	$host = ''localhost;
	$user = 'root';
	$pass = '';
	$db = 'my_db';

	// Create instance
	$mysqli = new mysqli($host, $user, $pass, $db);

	// Checking for error
	if ($mysqli->connect_errno)
	{
		die('Connect Error: ' . $mysqli->connect_error);
	}

	// Perform Operations on DB

	// Close connection
	$mysqli->close();
?>

Executing Query
For executing query we need to use query() method of mysqli class’s object that we are created. query() method take one parameter, which is MySQL query statement. For select query it will return resultset on successful evaluation of sql query. For other query it will return true on successful execution. For failure it will return false.

// For Select/Show/Desc/Explain Query
$sql = "SELECT * FROM my_table;"
if($result = $mysqli->query($sql))
{
	// Retriving rows
}

// For other operations includes Insert/Update/Delete/Drop etc
$sql = "CREATE DATABASE new_db;"
if($mysqli->query($sql) === true)
{
	// Show message
}

Using MySQLi Resultset
After retrieving resultset we need to manipulate it for showing in tabular form or performing other operations. We can retrive data in form of associative array, relative array, as well as in form of object from resultset. Bellow snippet shows how to retrieve data in the form of object.

$sql = "SELECT * FROM my_table;"
if($result = $mysqli->query($sql))
{
	// Show No. of rows
	echo "Total Rows: " . $result->num_rows;

	// Retrive as object
	while($obj = $result->fetch_object())
	{
		echo "Code: " . $obj->Code . ", Name: " . $obj->Name;
	}

	// Free result set
	$result->close();
}

Full Fledged Program
Below snippet contains entire operation for establishing connection, and performing operation, retrieving records.

<?php
	// Assign Config
	$host = ''localhost;
	$user = 'root';
	$pass = '';
	$db = 'my_db';

	// Create instance
	$mysqli = new mysqli($host, $user, $pass, $db);

	// Checking for error
	if ($mysqli->connect_errno)
	{
		die('Connect Error: ' . $mysqli->connect_error);
	}

	// For Select/Show/Desc/Explain Query
	$sql = "SELECT * FROM my_table;"
	if($result = $mysqli->query($sql))
	{
		// Show No. of rows
		echo "Total Rows: " . $result->num_rows;

		// Retrive as object
		while($obj = $result->fetch_object())
		{
			echo "Code: " . $obj->Code . ", Name: " . $obj->Name;
		}

		// Free result set
		$result->close();
	}

	// Close connection
	$mysqli->close();
?>


2 thoughts on “Using MySQLi in PHP as OOPS

    • First of all thanks for reading, now i am only person who makes post for that blog but if i have some people like you then i will start public post too.

Leave a Reply

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