Creating a MySqli Database Class in PHP
Creating a MySqli Database Class in PHP
I have been working with PHP PDO since the start of PHP 5.x. But in this post we will be using MySqli as an example.
You will learn how to create a basic OOP MySQLi class using PHP. You'll also learn how to bind the MySQLi parameters dynamically.
Quick Explanation
What is MySQLi? It's derived from the abbreviation "My" (Co-founder Michael Widenius' daughter), "SQL" (Structured Query Language), and "i" (Improved version from MySQL)
Here's a brief explanation of some MySQLi codes that we will be using.
$instance = new mysqli( host , username , password, databasename )
- host [Required] - The Data Source Name that contains information required to connect to the database
- username [Required] - MySQL Username
- password [Required] - MySQL Password
- databasename [Required] - MySQL Database name
Returns a MySLQi object or false
MySQLi::prepare(statement);
- statement [Required] - An SQL statement to execute
Example:
$instance->prepare('Select * from tableName');
Returns a mysqli_stmt (MySQLi Statement)
mysqli_stmt::execute(); $stmt = $instance->prepare('Select * from tableName');
$stmt->execute(); Executes the prepared statement.
mysqli_stmt::bind_param( types , columnValue1 , columnValue2, ....... );
- types [Required] - String types of corresponding columnValues
Values:
i - integer
b - blob
d - double
s - string
- columnValue^ [Required] - Values to bind to our prepared statements.
Example:
$stmt = $instance->prepare('
Insert into TableName
(
IntegerColumn ,
StringColumn1 ,
StringColumn2,
DoubleColumn
)
values( ?, ?, ?, ? )');
$stmt->bind_param("issd" , 1 , "Hello" , "World" , 12.34)
Notice we have some
? in our Query String. Those will be replaced later on automatically by MySQLi and you don't need to worry about escaping the strings.
So we have 4
?, this will then be replaced by the following in our
$stmt->bind_param i which is our
1 s which is our
Hello s which is our
World and
d which is our
12.34
mysqli_stmt::get_result(); Gets all data from a successful "Select" Query.
mysqli_result::fetch_all(); Converts a
mysqli_result to a readable object/array. so we can loop it.
fn
Function to call
mixed_array
Arguments to pass dynamically to fn
Example: call_user_func_array('str_replace',["World","User","Hello World"]); is also the same as str_replace("World","User","Hello World");
Creating the Class:
Preparing our Class name, variables and functions.
class DatabaseClass{
private $connection = null;
// this function is called everytime this class is instantiated
public function __construct(){
}
// Insert a row/s in a Database Table
public function Insert( ){
}
// Select a row/s in a Database Table
public function Select( ){
}
// Update a row/s in a Database Table
public function Update( ){
}
// Remove a row/s in a Database Table
public function Remove( ){
}
// execute statement
private function executeStatement( ){
}
} Now that we have a simple design for our Database class. Lets fill the functions with some codes.
Establish the MySQL connection in the costructor // this function is called everytime this class is instantiated
public function __construct( $dbhost = "localhost", $dbname = "myDataBaseName", $username = "root", $password = ""){
try{
$this->connection = new mysqli($dbhost, $username, $password, $dbname);
if( mysqli_connect_errno() ){
throw new Exception("Could not connect to database.");
}
}catch(Exception $e){
throw new Exception($e->getMessage());
}
}
The constructor will have 4 parameters
- $dbhost - The database host.
- $dbname - The database name.
- $username The database User.
- $password - The database password for the User.
A Function that will execute all statements // execute statement
private function executeStatement( $query = "" , $params = [] ){
try{
$stmt = $this->connection->prepare( $query );
if($stmt === false) {
throw New Exception("Unable to do prepared statement: " . $query);
}
if( $params ){
call_user_func_array(array($stmt, 'bind_param'), $params );
}
$stmt->execute();
return $stmt;
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
} We will be passing our SQL Statements to this function (Insert, Select, Update and Remove).
Returns mysqli_stmt or throws an exception if it get's an error.
The challenge here is that we have to make bind_param in our class dynamically accept any number of parameters.
We can do this by using call_user_func_array(); of course we have to also make our parameters to look like the bind_param itself.
Example: $stmt->bind_param("issd" , 1 , "Hello" , "World" , 12.34)
Then we will pass to this function DatabaseClass::executeStatement(
"Insert ........ values ( ?, ?, ?, ? )",
[
"issd" ,
1 ,
"Hello" ,
"World" ,
12.34
]
)
Insert Function // Insert a row/s in a Database Table
public function Insert( $query = "" , $params = [] ){
try{
$stmt = $this->executeStatement( $query , $params );
$stmt->close();
return $this->connection->insert_id;
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
} Insert will add a row and will return an integer of the last ID inserted or throws an exception if it get's an error.
Select Function // Select a row/s in a Database Table
public function Select( $query = "" , $params = [] ){
try{
$stmt = $this->executeStatement( $query , $params );
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
return $result;
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
} Select will return all row/s or throws an exception if it get's an error.
Update Function // Update a row/s in a Database Table
public function Update( $query = "" , $params = [] ){
try{
$this->executeStatement( $query , $params )->close();
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
} Update will update a row/s or throws an exception if it get's an error.
Remove Function // Remove a row/s in a Database Table
public function Remove( $query = "" , $params = [] ){
try{
$this->executeStatement( $query , $params )->close();
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
} Remove will remove a row/s or throws an exception if it get's an error.
Our Database Class example:
class DatabaseClass{
private $connection = null;
// this function is called everytime this class is instantiated
public function __construct( $dbhost = "localhost", $dbname = "myDataBaseName", $username = "root", $password = ""){
try{
$this->connection = new mysqli($dbhost, $username, $password, $dbname);
if( mysqli_connect_errno() ){
throw new Exception("Could not connect to database.");
}
}catch(Exception $e){
throw new Exception($e->getMessage());
}
}
// Insert a row/s in a Database Table
public function Insert( $query = "" , $params = [] ){
try{
$stmt = $this->executeStatement( $query , $params );
$stmt->close();
return $this->connection->insert_id;
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
}
// Select a row/s in a Database Table
public function Select( $query = "" , $params = [] ){
try{
$stmt = $this->executeStatement( $query , $params );
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
return $result;
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
}
// Update a row/s in a Database Table
public function Update( $query = "" , $params = [] ){
try{
$this->executeStatement( $query , $params )->close();
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
}
// Remove a row/s in a Database Table
public function Remove( $query = "" , $params = [] ){
try{
$this->executeStatement( $query , $params )->close();
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
return false;
}
// execute statement
private function executeStatement( $query = "" , $params = [] ){
try{
$stmt = $this->connection->prepare( $query );
if($stmt === false) {
throw New Exception("Unable to do prepared statement: " . $query);
}
if( $params ){
call_user_func_array(array($stmt, 'bind_param'), $params );
}
$stmt->execute();
return $stmt;
}catch(Exception $e){
throw New Exception( $e->getMessage() );
}
}
}
Using the Database Class:
Create/Instantiate the Database Class. $db = new Database(
"MySQLHost",
"myDatabaseName",
"myUserName",
"myUserPassword"
);
Insert Example $id = $db->Insert("Insert into `TableName`( `column1` , `column2`) values ( ? , ? )", [
'ss', 'column1 Value', 'column2 Value',
]);
Select Example $db->Select("Select * from TableName"); Update Example $db->Update("Update TableName set `column1` = ? where id = ?",[
'si', 'a new column1 value', 1
]); Remove Example $db->Remove("Delete from TableName where id = ?",[
'i' , 1
]);
Tips:
- Minimize connections to your server.
Take this as an example: for( $x = 1; $x <= 1000; $x++ ){
$db = new Database(
"MySQLHost",
"myDatabaseName",
"myUserName",
"myUserPassword"
);
$data = $db->Select("Select * from TableName where id = ?",["i",$x]);
// do something with $data
} The above code will create 1000 connections and this could lead to your server to slowing down.
A better way to do this is to create the DatabaseClass object before the looping: $db = new Database(
"MySQLHost",
"myDatabaseName",
"myUserName",
"myUserPassword"
);
for( $x = 1; $x <= 1000; $x++ ){
$data = $db->Select("Select * from TableName where id = ?",["i",$x]);
// do something with $data
} The above code will create 1 connection and will use it inside the loop.
I also have created a PHP PDO version of this class. You can read how i created it here PHP OOP Database Class Example
Replies (2)
Job well done, please can I have a project that consume all aspect you covered in your wrapper class
Regards,
Good work! This is a clean work. I love it!!!
Reply