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

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

}

Great code!

awesome, code helped me out.

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

}

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

        }
    }

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