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