PHP MySQL Database Connection with MySQLi, MySQLi OOP and PDO

PHP MySQL Connection with MySQLi, MySQLi OOP and PDO

Hi, in this tutorial you will learn how to make PHP Database connection with MySQL Database.

And at the end, you also learn how to close MySQL Database connections in PHP.

In here, to make this PHP MySQL Database Connection we will use all the possible ways such as MySQLi, MySQLi OOP and PHP PDO.

Navigation of PHP MySQL Connections

  • 1 – MySQLi Procedural way to connect database
  • 2 – MySQLi OOP (Object Oriented) way to connect database
  • 3 – Connect database with the PHP PDO
  • 4 – Close Database connection

1 – Procedural way to Connect Database

<?php
$connection = mysqli_connect("localhost","db_user","db_password","db_name");
// CHECK DATABASE CONNECTION
if(mysqli_connect_errno()){
    echo "Connection Failed".mysqli_connect_error();
}
else{
    echo "The database is successfully connected";
}
?>

2 – OOP (Object Oriented) way to Connect Database

<?php
$db_conn = new mysqli("localhost","db_user","db_password","db_name");
// CHECK DATABASE CONNECTION
if($db_conn->error === false){
    echo "Connection Failed - ".$db_conn->connect_error;
}
else{
    echo "The database is successfully connected";
}
?>

3 – Connect database with PHP PDO

<?php
$db_host = 'localhost';
$db_user = 'db_user';
$db_password = 'db_password';
$db_name = 'db_name';

// DSN(Database Source Name)
$dsn = 'mysql:host='.$db_host.';dbname='.$db_name.';charset=utf8';

try{
   $db_conn = new PDO($dsn, $db_user, $db_password);
   // SET THE PDO ERROR MODE TO EXCEPTION
   $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e){
   echo $e->getMessage();
   exit;
}
?>

How to close Database Connections

The database connection will be automatically closed when the script ends. But if you want to close it earlier, then use the following:

Use mysqli_close() function to close the database connection with the MySQLi Procedural way.

<?php
$connection = mysqli_connect("localhost","db_user","db_password","db_name");
// CHECK DATABASE CONNECTION
if(mysqli_connect_errno()){
    echo "Connection Failed".mysqli_connect_error();
}
else{
    echo "The database is successfully connected";
}
// CLOSE THE CONNECTION
mysqli_close($connection);
?>

Close the connection with the MySQLi OOP way.

<?php
$db_conn = new mysqli("localhost","db_user","db_password","db_name");
// CHECK DATABASE CONNECTION
if($db_conn->error === false){
    echo "Connection Failed - ".$db_conn->connect_error;
}
else{
    echo "The database is successfully connected";
}
// CLOSE THE CONNECTION
$db_conn->close();
?>

PHP PDO close the databse connection –

<?php
$db_host = 'localhost';
$db_user = 'db_user';
$db_password = 'db_password';
$db_name = 'db_name';

// DSN(Database Source Name)
$dsn = 'mysql:host='.$db_host.';dbname='.$db_name.';charset=utf8';

try{
   $db_conn = new PDO($dsn, $db_user, $db_password);
   // SET THE PDO ERROR MODE TO EXCEPTION
   $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e){
   echo $e->getMessage();
   exit;
}
// CLOSE THE CONNECTION
//unset the $db_conn value
unset($db_conn);
//OR you can set value null
// $db_conn = null;
?>

Reas also:

Leave a Reply

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