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(); ?>
This site is really cool. I have bookmarked it. Do you allow guest post on your blog
? I can provide hi quality articles for you. Let me know.
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.