22 August 2013

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


6 comments:

Anonymous said...

Thanks for this - I really like this class.

Anonymous said...

Hi,

I am just learning how to code in PHP, using OOP (i came from Java and C so i like OOP very much).

Thanks for the example, it is simple, self-explaining and really good.

One remark though:
In the function totalCount - why do you run on the result with while if the answer is only one row?
And why do you really need it for count if you can use mysqli function that counts rows?

alper said...

Hello

Sometimes I need a count number like, comments count, with different Where conditions, that s why I use totalCount.

But you are right about the result is only one row.

thank you.

Unknown said...

Also good to keep your database credentials in a separate file so you can place it outside the server root directory. This provides added security in the case where the php module fails. I know from bitter experience that this leads to raw php code being displayed in browsers - including passwords!!

Mark said...

When I try to run your code:

$row = mysqli_fetch_array($result);

I get the following error (yes, it is inside the if statement)

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

How to handle this?

Unknown said...

How to use error handling with this? I've done a try/catch and an if/else, but none seem to work very well. is there a way to add an if the function does not complete, then show me the sql error?