A Computer Engineer

Now a days in PHP and Mysql connection after deprecation of Mysql, PDO [PHP Data Objects] and Mysqli is the best choice. In that PDO is widely used in new frameworks because it has more benefits than Mysql and Mysqli. Here is the list of features of PDO.

• Have dirvers for 12 different databases [list them using print_r(PDO::getAvailableDrivers());]
• OOP approach
• Named parameters
• Object mapping
• Prepared statements
• Fast performance
• Stored procedures

Now here I am showing you the class of database connection that I used for my work. In that I use singleton pattern that eliminate the chances of having more than one instance of the class object in memory.

<?php

class database
{
    public $dbc;
    private static $instance;

    private function __construct()
    {
        $this -> dbhc= new PDO('mysql:host=localhost;dbname=example', "root", "");
    }

    //singleton pattern
    public static function getInstance()
    {
        if (!isset(self::$instance))
        {
            $object = __CLASS__;
            self::$instance = new $object;
        }
        return self::$instance;
    }
}

?>

You can use above class using following example code.

//get database connector by calling static method of database class [singleton pattern]
$db = database::getInstance();


        
//insert operation
$sql = "insert into `user` (`username`, `password`, `fullname`, `email`) values (:username, :password, :fullname, :email)";    //named parameters

$query = $db -> dbc -> prepare($sql);

$result = $query -> execute(array(":username" => $username, ":password" => $password, ":fullname" => $fullname, ":email" => $email));    //named parameters value
             
if($result)
    echo "Insert Successfull.";
else
    echo "Insert Fail."


        
//update operation
$sql = "update `user` set `username` = ?,  `password` = ?, `fullname` = ?, `email` = ? where `user_id` = ?";    //prepared statements

$query = $db -> dbc -> prepare($sql);

$result = $query -> execute(array($username, $password, $fullname, $email, $user_id));   //prepared statements value
        
if($result)
    echo "Update Successfull.";
else
    echo "Update Failed.";



//delete operation
$sql = "delete from `user` where `user_id` = ?";   //prepared statements

$query = $db -> dbc -> prepare($sql);

$result = $query -> execute(array($user_id));   //prepared statements value
        
if($result)
    echo "Delete Successfull.";
else
    echo "Delete Failed.";



//select operation
$sql = "select * from `user` where `user_id` = ?";   //prepared statements

$query = $db -> dbc -> prepare($sql);
        
$query -> execute(array($user_id));   //prepared statements value
        
$query -> setFetchMode(PDO::FETCH_ASSOC); // [PDO::FETCH_NUM for integer key value]
        
$result = $query -> fetchAll();

Download database class file here : https://www.mediafire.com/view/lmmlosnf26v5lla/db_config.php

References :

• Why you use PDO
• PDO Vs. Mysqli
• Singleton Pattern