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: (" . $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'];
}
