PHP Database Class MySQLi

Almost in every project I need a database class behind my BLL classes. I keep my SQL queries in BLL classes and use the database class to run the queries. This is kind of engine used by all classes in common. I will explain how to implement and how to use step by step in comments code.

Note that this class does not use the deprecated MySQL library, it uses MySQLi library. Here is the complete PHP Database Class source code;

First php is the configuration file for use in database class.
I call it dbconfig.php

<?php
define("DB_SERVER", "localhost");
define("DB_USER", "db_username");
define("DB_PASS", "db_user_password");
define("DB_NAME", "db_scheme_name");
?>

I think it is better to keep configuration data in a seperate php file, because if change the database name, username or password, i just change the dbconfig.php file.

MySQLi Database Class

<?php

// My database Class called myDBC
class myDBC {

// our mysqli object instance
public $mysqli = null;

// Class constructor override
public function __construct() {
 
include_once "dbconfig.php";        
       
$this->mysqli = 
   new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

if ($this->mysqli->connect_errno) {
    echo "Error MySQLi: ("&nbsp. $this->mysqli->connect_errno 
    . ") " . $this->mysqli->connect_error;
    exit();
 }
   $this->mysqli->set_charset("utf8"); 
}

// Class deconstructor override
public function __destruct() {
   $this->CloseDB();
 }

// runs a sql query
    public function runQuery($qry) {
        $result = $this->mysqli->query($qry);
        return $result;
    }

// runs multiple sql queres
    public function runMultipleQueries($qry) {
        $result = $this->mysqli->multi_query($qry);
        return $result;
    }

// Close database connection
    public function CloseDB() {
        $this->mysqli->close();
    }

// Escape the string get ready to insert or update
    public function clearText($text) {
        $text = trim($text);
        return $this->mysqli->real_escape_string($text);
    }

// Get the last insert id 
    public function lastInsertID() {
        return $this->mysqli->insert_id;
    }

// Gets the total count and returns integer
public function totalCount($fieldname, $tablename, $where = "") 
{
$q = "SELECT count(".$fieldname.") FROM " 
. $tablename . " " . $where;
        
$result = $this->mysqli->query($q);
$count = 0;
if ($result) {
    while ($row = mysqli_fetch_array($result)) {
    $count = $row[0];
   }
  }
  return $count;
}

}

?>

Other Descriptions
Because I always use UTF-8 encoding I set my database engine connection to utf8 too.

   $this->mysqli->set_charset("utf8");

Actually you dont need to close the connection, as the connection closes it self after the query executions completted. But sometimes I run lots of queries in same session and I just close it.

public function __destruct() {
   $this->CloseDB();
 }

Usage Examples

---

SQL Insert Example

include "mydbclass.php";

$mydb = new myDBC();

$firstname = $mydb->clearText($firstname);
$lastname  = $mydb->clearText($lastname);

$sql = "INSERT INTO users (usr_firstname,usr_lastname) 
VALUES ('$firstname','$lastname'); ";

$mydb->runQuery($sql);

SQL SELECT Example

include "mydbclass.php";

$mydb = new myDBC();

$id = $mydb->clearText($id);
$sql = "SELECT * FROM users WHERE usr_id='$id' LIMIT 1;";

$result = $mydb->runQuery($sql);

if(isset($result)) 
{
  $row = mysqli_fetch_array($result);
  $firstname = $row['usr_firstname']; 
  $lastname = $row['usr_lastname']; 
}