An OOP Way to Work with MySQL Database in PHP
This is my learning note of PHP and OOP(Object Oriented Programming) from Brian Muse’s tutorial, if you are new to OOP, you may find something helpful in this post. If you are a professional, I would appreciate it if you can give me some further advices or refer me for further tutorials.
Create a Demo Database with a users table
Basically, an oop way to work with database means to create a class for database. Let’s create a new file named DB.class.php for our database class. We also need a demo database with a users table, here is the SQL:
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `password` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `join_date` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The DB Class and its Properties
We have a class named DB in DB.class.php, at the very beginning we can write the follow codes:
<?php
//DB.class.php
class DB{
}
?>
And then, we need to consider about the properties and functions of the class. In this case, we need the following properties:
- $db_name: database name
- $db_user: database username
- $db_pass: database password
- $db_host: host of the database
Because these variables are not necessary to be accessed from outside the class, we will define them as “protected”:
<?php
//DB.class.php
class DB{
protected $db_name = 'yourdatabasename';
protected $db_user = 'databaseusername';
protected $db_pass = 'databasepassword';
protected $db_host = 'localhost';
}
?>
They can be used inside the class by using $this->db_name, $this->db_user, etc. That’s all properties we need, and now we need to think about what functions we need.
Functions of DB Class
While using database, the first thing we need to do is to connect to the database, so the first function should be connect(). There are several common operations we also need: insert, update, select and delete. One more thing we need to think about is that the select function will pull some data out from the database, the database normally returns row set, it is better that we convert row set into associative array for later use. So we add a function processRowSet() for this. Let’s code the first function, connect():
//Connect to the database.
//You need to call this on every page using database
public function connect() {
$connection = mysql_connect($this->db_host, $this->db_user, $this->db_pass);
mysql_select_db($this->db_name);
return true;
}
Not thing special in this function, just connect to the database using mysql_connect(), select database by using mysql_select_db(), and then return true telling the function executed successfully. The next function is insert(), here is codes:
//Inserts a new row into the database.
//takes an array of data, where the keys in the array are the column names
//and the values are the data that will be inserted into those columns.
//$table is the name of the table.
public function insert($data, $table) {
$columns = "";
$values = "";
foreach ($data as $column => $value) {
$columns .= ($columns == "") ? "" : ", ";
$columns .= $column;
$values .= ($values == "") ? "" : ", ";
$values .= $value;
}
$sql = "insert into $table ($columns) values ($values)";
mysql_query($sql) or die(mysql_error());
//return the ID of the user in the database.
return mysql_insert_id();
}
The foreach loop is to combine each column and value in $data into columns and values, separating each other by comma, so that we can use one query to insert all data. After inserting, we return the id of the new row(which should be user in this tutorial).
Before we go to select(), we need to write the codes of processRowSet():
//takes a mysql row set and returns an associative array, where the keys
//in the array are the column names in the row set. If singleRow is set to
//true, then it will return a single row instead of an array of rows.
public function processRowSet($rowSet, $singleRow=false)
{
$resultArray = array();
while($row = mysql_fetch_assoc($rowSet))
{
array_push($resultArray, $row);
}
if($singleRow === true)
return $resultArray[0];
return $resultArray;
}
This function loops through each row in $rowSet, converts each row into an associative array($row) using mysql_fetch_assoc() function, and then pushes each array into one array($resultArray) which will be returned in the end of the function. If the $singleRow is set to true, only the first array will be returned.
Now let’s see the select() function:
//Select rows from the database.
//returns a full row or rows from $table using $where as the where clause.
//return value is an associative array with column names as keys.
public function select($table, $where) {
$sql = "SELECT * FROM $table WHERE $where";
$result = mysql_query($sql);
if(mysql_num_rows($result) == 1)
return $this->processRowSet($result, true);
return $this->processRowSet($result);
}
This function select some data from the database, converts row set into associative array using processRowSet(), here you can see how the $singleRow parameter is used.
The next function is update():
//Updates a current row in the database.
//takes an array of data, where the keys in the array are the column names
//and the values are the data that will be inserted into those columns.
//$table is the name of the table and $where is the sql where clause.
public function update($data, $table, $where) {
foreach ($data as $column => $value) {
$sql = "UPDATE $table SET $column = $value WHERE $where";
mysql_query($sql) or die(mysql_error());
}
return true;
}
This function loops through $data, update each $column with $value according to $where, if everything goes well, return true, otherwise, die with MySQL error message.
The final one is the delete() function:
public function delete($table, $where) {
$sql = "DELETE * FROM $table WHERE $where";
mysql_query($sql) or die(mysql_error());
return true;
}
This may be the simplest one, just run the query and return true or die with error message.
Using DB Class
As a result, the DB class should be like this:
<?php
//DB.class.php
class DB{
protected $db_name = 'yourdatabasename';
protected $db_user = 'databaseusername';
protected $db_pass = 'databasepassword';
protected $db_host = 'localhost';
//Connect to the database.
//You need to call this on every page using database
public function connect() {
$connection = mysql_connect($this->db_host, $this->db_user, $this->db_pass);
mysql_select_db($this->db_name);
return true;
}
//Inserts a new row into the database.
//takes an array of data, where the keys in the array are the column names
//and the values are the data that will be inserted into those columns.
//$table is the name of the table.
public function insert($data, $table) {
$columns = "";
$values = "";
foreach ($data as $column => $value) {
$columns .= ($columns == "") ? "" : ", ";
$columns .= $column;
$values .= ($values == "") ? "" : ", ";
$values .= $value;
}
$sql = "insert into $table ($columns) values ($values)";
mysql_query($sql) or die(mysql_error());
//return the ID of the user in the database.
return mysql_insert_id();
}
//takes a mysql row set and returns an associative array, where the keys
//in the array are the column names in the row set. If singleRow is set to
//true, then it will return a single row instead of an array of rows.
public function processRowSet($rowSet, $singleRow=false)
{
$resultArray = array();
while($row = mysql_fetch_assoc($rowSet))
{
array_push($resultArray, $row);
}
if($singleRow === true)
return $resultArray[0];
return $resultArray;
}
//Select rows from the database.
//returns a full row or rows from $table using $where as the where clause.
//return value is an associative array with column names as keys.
public function select($table, $where) {
$sql = "SELECT * FROM $table WHERE $where";
$result = mysql_query($sql);
if(mysql_num_rows($result) == 1)
return $this->processRowSet($result, true);
return $this->processRowSet($result);
}
//Updates a current row in the database.
//takes an array of data, where the keys in the array are the column names
//and the values are the data that will be inserted into those columns.
//$table is the name of the table and $where is the sql where clause.
public function update($data, $table, $where) {
foreach ($data as $column => $value) {
$sql = "UPDATE $table SET $column = $value WHERE $where";
mysql_query($sql) or die(mysql_error());
}
return true;
}
public function delete($table, $where) {
$sql = "DELETE * FROM $table WHERE $where";
mysql_query($sql) or die(mysql_error());
return true;
}
}
?>
Here is a sample of how to use the DB class:
//create an instance of the DB class
$db = new DB();
//connect to the database
$db->connect();
$data = array(
"username" => "'Jack'",
"email" => "'jack@email.com'"
);
//insert a new user
$db->insert($data, 'users');
//change the email of Jack
$data = array(
"username" => "Jack",
"email" => "jack.gmail@gmail.com"
);
//update Jack
$db->update($data, 'users', 'id = 1');
//delete Jack
$db->delete('users', 'id = 1');
.
.
Pingback: Zack Live