PHP OOP Database class using MySQLI and Singleton pattern. Only one instance of the class will be made, this requires less memory.

class.database.php

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

Great Good !

nice

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.

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);
        }
    }

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

Thanks!

Thanks! bro...

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;');
}
}

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?!

Hi,
How do I add ? charset=utf8

Hi,
How do I add ? charset=utf8

https://gist.github.com/jonashansen229/4534794#gistcomment-2790773

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?!

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