Skip to content

Instantly share code, notes, and snippets.

@jonashansen229
Last active June 20, 2023 08:41
  • Star 92 You must be signed in to star a gist
  • Fork 48 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jonashansen229/4534794 to your computer and use it in GitHub Desktop.
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;
}
}
?>
@kazitanvirahsan
Copy link

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
Copy link

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
Copy link

nzubkov commented Jul 13, 2018

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

@dmaldonado8
Copy link

Thanks!

@bhq
Copy link

bhq commented Nov 20, 2018

Thanks! bro...

@akak0r
Copy link

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
Copy link

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
Copy link

Hi,
How do I add ? charset=utf8

@akak0r
Copy link

akak0r commented Nov 16, 2019

@v86
Copy link

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
Copy link

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