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
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;
}
}
?>
@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