Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PHP OOP Database class using MySQLI and Singleton pattern. Only one instance of the class will be made, this requires less memory.
<?php
/*
* Mysql database class - only one connection alowed
*/
class Database {
private $_connection;
private static $_instance; //The single instance
private $_host = "HOSTt";
private $_username = "USERNAME";
private $_password = "PASSWORd";
private $_database = "DATABASE";
/*
Get an instance of the Database
@return Instance
*/
public static function getInstance() {
if(!self::$_instance) { // If no instance then make one
self::$_instance = new self();
}
return self::$_instance;
}
// Constructor
private function __construct() {
$this->_connection = new mysqli($this->_host, $this->_username,
$this->_password, $this->_database);
// Error handling
if(mysqli_connect_error()) {
trigger_error("Failed to conencto to MySQL: " . mysql_connect_error(),
E_USER_ERROR);
}
}
// Magic method clone is empty to prevent duplication of connection
private function __clone() { }
// Get mysqli connection
public function getConnection() {
return $this->_connection;
}
}
?>
@jonashansen229

This comment has been minimized.

Copy link
Owner Author

@jonashansen229 jonashansen229 commented Jan 15, 2013

To make a connection to the database and make a query simple use the lines:

    $db = Database::getInstance();
    $mysqli = $db->getConnection(); 
    $sql_query = "SELECT foo FROM .....";
    $result = $mysqli->query($sql_query);
@agriz

This comment has been minimized.

Copy link

@agriz agriz commented Dec 23, 2013

How to use this code with another class?

class test {
public function getUsers() {
}
}

How do i use your code?

@pawebgate

This comment has been minimized.

Copy link

@pawebgate pawebgate commented May 29, 2014

Hi there, thank you for the code, I think there is a small error in line 31 it should say "mysqli_connect_error" instead of mysql.

Anyway, i have a question: I have three php files One that shows results from a database fetch query, One that inserts data into the database and One that updates data. The first php file goes to the second and the second goes to the third. They all share the same database connection and database. Should I close the database connection with mysqli_close($mysqli); on every file??? I think it should be closed on the third file only. Let me know.

@wiltim

This comment has been minimized.

Copy link

@wiltim wiltim commented Oct 26, 2014

@agriz, for example something like this..:

class Query extends Database {
public static function run($sql) {
return parent::getInstance()->getConnection()->query($sql);
}
}

You can use this static function in your classes

$result = Query::run(query);

@aoeasif

This comment has been minimized.

Copy link

@aoeasif aoeasif commented Apr 11, 2015

Can I close the connection with destruct method?
Just adding this function bellow:

public function __destruct() {
    $this->_connection->close();
}
@xtepwxly

This comment has been minimized.

Copy link

@xtepwxly xtepwxly commented May 10, 2015

I suppose, it will be better:

  1. to use PDO API.
  2. to protect your credentials such as (username, password and etc.) in "config" file.
@skhani

This comment has been minimized.

Copy link

@skhani skhani commented Jul 24, 2015

I just created a pdo version of this class you can view it here https://gist.github.com/skhani/5aebd11015881fb3d288

@cosmosinnovate

This comment has been minimized.

Copy link

@cosmosinnovate cosmosinnovate commented Aug 22, 2015

This is actually a great PHP OOP.
For line 31, I used this one here:

/* Test if connection succeeded */

if (mysqli_connect_errno()) {


die("Database connection failed: " .


mysqli_connect_error() . " (" .


mysqli_connect_errno() . ")"


);


}

@AmgedOsman

This comment has been minimized.

Copy link

@AmgedOsman AmgedOsman commented Mar 1, 2016

I Just want to check if my code is clean and secure - can someone help please
I basically calls my libsClass on every page
the construct loads for the DB Instance using $this->startDB(); then i can call $libClass>mysqli->query($query); basically any where i want.... is this good practice?
I also don't understand if i should do any connection closing?

class libsClass {
protected $db;
public $mysqli;
    public function __construct()
    {
        $this->startDB();
        $this->checkSetup();
    }

public function startDB(){

    $this->db = Database::getInstance();
    $this->mysqli = $this->db->getConnection(); 
}



public function loadMember($id=0){ 
    //include DB_FILE;
    $query = "SELECT * FROM users WHERE twitter_id=$id";
    if ($result = $this->mysqli->query($query)) {
         while ($obj = $result->fetch_object()) {
            return $obj;
        }
    }
     return false;
}

}
@serjnazarov

This comment has been minimized.

Copy link

@serjnazarov serjnazarov commented Mar 18, 2016

Great code!

@gruff1991

This comment has been minimized.

Copy link

@gruff1991 gruff1991 commented Apr 17, 2016

awesome, code helped me out.

@sumariyanto

This comment has been minimized.

Copy link

@sumariyanto sumariyanto commented May 4, 2016

i'm use mysql 👍
class database
{
var $koneksi ;
var $selectDb;
var $query ;
var $result ;
var $row;
var $jumlah ;

function database()
{
     $nameSer = ""; //Nama Host
    $username =""; //username Host
    $pass = ""; //Passwrod Host
    $dbname = ""; //Database Name
    $koneksi = mysql_connect($names,$username,$pass)or die('error'.mysql_errno());

  $this->selectDb = mysql_select_db($dbname,$koneksi);
  if ( !$this->selectDb )
  {
    echo "gagal";
  }
}

function query($query)//fungsi query
{
  $this->result = mysql_query($query);
}

function tampilkan()//fungsi menampilkan data
{
  $this->row = mysql_fetch_array($this->result);
  return $this->row;
}

function view()//fungsi menampilkan data
{
  $this->row = mysql_fetch_object($this->result);
  return $this->row;
}

function getJumlah()//fungsi menampilkan jumlah data
{
  $this->jumlah = mysql_num_rows($this->result);
  return $this->jumlah;
}

 function get($table)//fungsi select
{
  $this->result = mysql_query("SELECT * FROM ".$table);
}

function getJumlahFromTable($table)
{
  $this->get($table);
  return $this->getJumlah();
}

 function insert( $database , $data)//fungsi insert
{
    $row = array();
    $nilai = array();
    foreach ( $data as $kolom =>$value )
           {
        $row[] = $kolom;
        $nilai[] = "'".$value."'";
    }

    $this->result = $this->query("INSERT INTO ". $database ."(". implode(',' ,$row) .")
                    VALUES (". implode(',' , $nilai) .")");
 }

  function update($table , $data , $where)//fungsi update
  {
    foreach ( $data as $kolom => $row )
    {
        $set[]= $kolom."='".$row."'" ;
    }
    $set = implode(',',$set);
    $query = "UPDATE ".$table." SET ".$set." WHERE ".$where ;
    $this->query($query);
}

function delete($table , $where)//fungsi delete
{
    $this->query("DELETE FROM ".$table." WHERE ".$where);
}

}

@aligoren

This comment has been minimized.

Copy link

@aligoren aligoren commented Jul 14, 2016

Maybe you can use PDO. Like below:

private function __construct() {
        try {

            $this->_connection = new PDO("mysql:host=$this->_host;dbname=$this->_database", 
                       "$this->_username", "$this->_password");

        } catch (PdoException $e) {

            echo 'Hata: '.$e->getMessage();

        }
    }
@alira4d

This comment has been minimized.

Copy link

@alira4d alira4d commented Jul 17, 2016

Great Good !

@Eyongkevin

This comment has been minimized.

Copy link

@Eyongkevin Eyongkevin commented Sep 26, 2016

nice

@kazitanvirahsan

This comment has been minimized.

Copy link

@kazitanvirahsan kazitanvirahsan commented Nov 21, 2016

Great Code!

One design suggestion from usage ref@jonashansen229
$db = Database::getInstance();
$mysqli = $db->getConnection();
$sql_query = "SELECT foo FROM .....";
$result = $mysqli->query($sql_query);

you don't need to extract mysqli object($mysqli = $db->getConnection(); ) from Database class when you want to use it as a wrapper of mysqli.

@samuel-fonseca

This comment has been minimized.

Copy link

@samuel-fonseca samuel-fonseca commented Aug 30, 2017

When checking for an error; wouldn't it be better - and more consistent - to use the mysqli object-oriented method to catch the error? This is a small suggestion.

    private function __construct()
    {
        $this->_connection = new mysqli($this->_host, $this->_username, $this->_password, $this->_database);
        
        // error
        if ($this->_connection->connect_error)
        {
            trigger_error("Connection Error: " . $this->_connection->connect_error(), E_USER_ERROR);
        }
    }
@nzubkov

This comment has been minimized.

Copy link

@nzubkov nzubkov commented Jul 13, 2018

Thanks a lot! It is very handy and usefull class!

@dmaldonado8

This comment has been minimized.

Copy link

@dmaldonado8 dmaldonado8 commented Aug 9, 2018

Thanks!

@bhq

This comment has been minimized.

Copy link

@bhq bhq commented Nov 20, 2018

Thanks! bro...

@akak0r

This comment has been minimized.

Copy link

@akak0r akak0r commented Dec 20, 2018

To set the default charset and collation, like this: http://php.net/manual/en/mysqli.set-charset.php#122858

private function __construct() {
$this->_connection = new mysqli($this->_host, $this->_username, $this->_password, $this->_database);
// error
if ($this->_connection->connect_error) {
trigger_error("Connection Error: ".$this->_connection->connect_error(), E_USER_ERROR);
} else {
// set charset & collation
$this->_connection->set_charset('utf8mb4');
$this->_connection->query('SET collation_connection = @@collation_database;');
}
}

@samartzis

This comment has been minimized.

Copy link

@samartzis samartzis commented Apr 30, 2019

I get error

Fatal error: Uncaught Error: Call to a member function query() on null in C:\xampp\htdocs\wp-top-promo\index.php:127 Stack trace: #0 {main} thrown in C:\xampp\htdocs\wp-top-promo\index.php on line 127

` <?php
//-- Custom Constantinos Accessories MySQLi Connection Class --//
class ca_db {
private $ca_conn;
private static $ca_instance;
private $ca_servername = "localhost";
private $ca_username = "root";
private $ca_password = "";
private $ca_dbname = "coupons_db";

    public static function getInstance() {
      if(!self::$ca_instance) { // If no instance then make one
        self::$ca_instance = new self();
      }
      return self::$ca_instance;
    }

    private function __construct() {
       $ca_conn = new mysqli($this->ca_servername, $this->ca_username, $this->ca_password, $this->ca_dbname);
       // Check connection
       if(mysqli_connect_error()) {
         trigger_error("Failed to conencto to MySQL: " . mysql_connect_error(),
            E_USER_ERROR);
      }
    }

    // Magic method clone is empty to prevent duplication of connection
    private function __clone() { }

    // Get mysqli connection
    public function getConnection() {
    return $this->ca_conn;
    }
  }

$db = ca_db::getInstance();
$mysqli = $db->getConnection();

    $sql = "SELECT id FROM wp_posts WHERE post_type = 'shop_coupon' AND post_title != 'deals' AND post_status = 'publish'";
    $active = $mysqli->query($sql);
    echo $active;`

Im beginner, so I'm stuck!

Any suggestions?!

@VictoryTR

This comment has been minimized.

Copy link

@VictoryTR VictoryTR commented Jul 27, 2019

Hi,
How do I add ? charset=utf8

@akak0r

This comment has been minimized.

Copy link

@akak0r akak0r commented Nov 16, 2019

@v86

This comment has been minimized.

Copy link

@v86 v86 commented Mar 24, 2020

Why this whole new self() and getInstance() plus getConnection() thing? Wouldn't it be much easier to just do this:

class Database {
  // Database credentials
  private static $config = [
    'host' => '',
    'base' => '',
    'user' => '',
    'pass' => ''
  ];
  
  protected static $instance = null;
  
  private function __construct() {} 
  private function __clone() {} 
  
  public static function getInstance() { 
    if(!self::$instance) {
      self::$instance = new mysqli(
        self::$config['host'], 
        self::$config['user'], 
        self::$config['pass'], 
        self::$config['base']
      );
    }
    return self::$instance; 
  }
}

If you want you can now add a __callStatic($method, $arguments) method that calls self::getInstance(), and use it as easy as this:
foreach (Database::query("_your query_") as $row) ...

Or do I get this whole thing wrong?!

@baxt01

This comment has been minimized.

Copy link

@baxt01 baxt01 commented Sep 22, 2020

I feel like i should know this stuff already I think i have used it before but right now i have had some time away from coding due to life happening and now i can't seem to get my brain to cooperate with what i am trying to get done,
so I started putting together a simple user system and with a simple connection everything worked fine once i changed to this DB class file and included it simple things became broken and i just can not seem to work it out so my original sql select query was a prepared statement,
$sql = "SELECT * FROM users WHERE username=? AND password=? AND user_type=?"; $stmt = $mysqli->prepare($sql); $stmt->bind_param("sss",$username,$password,$userType); $stmt->execute(); $result = $stmt->get_result(); $row = $result->fetch_assoc();

this was working just fine with the standard inline connection, now with the class file included i get

"Fatal error: Uncaught Error: Call to a member function prepare() on null in /homepages/15/d842734408/htdocs/MURB/index.php:22 Stack trace: #0 {main} thrown in"

can anyone please correct my line of thinking on this so i can move on and remember what i need here please

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment