PHP MySqli Database connection with example

PHP Database connection tutorial with simple example is written here.

PHP works with a virtual database, including Oracle and Sybase but the most commonly used database is MySQLi.

MySql is very popular and widely used database system used with PHP.

MySQL is an open-source relational database management system (RDBMS).

It is freely available and easy to install.

Now a days, MySQL is most popular database platform for connect and manipulate database in PHP.

MySQL is the ideal database system for small or large web applications.

MySQL compiles on various platforms and free to download and use.

PHP 5 and later versions can work with MySQL database using these 2 following alternatives :

1. MySQLi extension
2. PDO (PHP Data Objects)

MySQLi works only with MySQL database system.

PDO works with different 12 database systems.

If the user wants to change to another database then PDO makes it easier.

Both are object oriented but MySQLi also offeres procedural API.

Connection using MySQLi :

We can connect database using 2 ways in MySQLi.

– MySQLi (Object-orientd)

– MySQLi (Procedural)

MySQLi (Object-orientd) :

Establish database connection by using MySQLi object orientd procedure.

<?php
$servername = "localhost";
$dbusername = "root";
$dbpassword = "";
$dbname = "dbconnect":

// Creating connection
$connection = new mysqli($servername, $dbusername, $dbpassword, $dbname);

// Checking connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
} 
echo "Databse connected successfully";
?>

If the connection establishes it returns “Database connected successfully” otherwise returns connection error.

Databse connected successfully

MySQLi (Procedural) :

Now we establish a database connection by using procedural approach.

<?php
$servername = "localhost";
$dbusername = "root";
$dbpassword = "";
$dbname = "dbconnect";

// Creating connection
$connection = new mysqli($servername, $dbusername, $dbpassword, $dbname);

// Checking connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Database connected successfully";
?>

Same way if the connection successfully established will return the “Database connected successfully” otherwise gives a connection error.

Database connected successfully

Connection using PDO (PHP Data Objects)

As name suggests PDO stands for PHP data objects.

We are using data objects in PHP.

Here we have the example for establish connection by PDO.

<?php
$servername = "localhost";
$dbusername = "root";
$dbpassword = "";

try {
    $connection = new PDO("mysql:host=$servername;dbname=dbconnect", $dbusername, $dbpassword);
    // setting the PDO error mode to exception
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Database Connected successfully"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

If the database connected successfully returns the “Database Connected successfully” message otherwise gives a connection error.

Database Connected successfully

Connection Close

When we establish database connection using PHP script, it also need to close the connection.

If we didn’t declare the closing of database connection it will automatically disconnect the database after the execution of script.

Here we have all 3 methods to disconnect the connection of above methods.

We need to declare this code into PHP script.

Here $connection is the reference variable for connection.

MySQLi (Object-orientd) :

$connection->close();

MySQLi (Procedural) :

mysqli_close($connection);

PDO (PHP Data Objects) :

$connection = null;