Real-World OOP With PHP and MySQL

Sajal Soni Last updated Mar 25, 2023

Read Time: 9 min

Numerous examples from robots to bicycles have been offered as easy explanations of what OOP is. However, I prefer to demonstrate how OOP functions in real-world scenarios, specifically for programmers. One practical example is by creating a MySQL CRUD (Create, Read, Update, and Delete) class. With this, programmers can efficiently manage entries in their projects regardless of the database's design.

Once we determine our requirements, creating the basic structure of our class is relatively straightforward. Firstly, we need to make sure that we can do our basic MySQL operations. In order to achieve it, we need to implement the following functions:

select
insert
delete
update
connect
disconnect

Let's quickly see how our class definition looks:

<?php
class Database 
{
    private $db_host = '';
    private $db_user = '';
    private $db_pass = '';
    private $db_name = '';
    private $con = '';
9
    public function __contruct($db_host, $db_user, $db_pass, $db_name)
    {
        $this->db_host = $db_host;
        $this->db_user = $db_user;
        $this->db_pass = $db_pass;
        $this->db_name = $db_name;
    }

    public function connect() {}
    public function disconnect() {}
    public function select() {}
    public function insert() {}
    public function delete() {}
    public function update() {}
}

Basically it's a skeleton class called Database which represents a database connection and provides methods to interact with the database. We've defined different private properties like $db_host, $db_user, $db_pass, $db_name, and $con. It's used to store the database connection details and the connection object itself.

In the next section, we'll go through the actual implementation of this class.

Implement the Database Class

In this section, we'll implement methods that are required in order to perform different database operations.

The `connect` Method

Go ahead and add the connect method as shown in the following snippet.

<?php
2
public function connect()
{
    if (!$this->con) {
        $this->con = mysqli_connect($this->db_host, $this->db_user, $this->db_pass);
7
        if($this->con) {
            $seldb = mysqli_select_db($this->con, $this->db_name);

            if($seldb) {
                return true; 
            } else {
                return false;
            }
        } else {
            return false;
        }
    } else {
        return true;
    }
}

The purpose of the connect method is to establish a connection to a MySQL database using the mysqli extension. Firstly, we check that if there is already a connection to the database, and If there is, the method simply returns true to indicate that the connection is already established and there is no need to reconnect. If there is no connection, it attempts to connect to the database using the mysqli_connect function.

Once the database connection is successful, it proceeds to select the database by calling the mysqli_select_db function. Finally, If the database selection is successful, it returns true to indicate that the connection is established and the database is selected. If either the connection or the database selection fails, it returns false.

The `disconnect` Method

Let's go through the disconnect method.

<?php
2
public function disconnect() 
{
    if ($this->con) {
        if (mysqli_close($this->con)) {
            $this->con = false; 
            return true;
        } else {
            return false;
        }
    }
}

It checks if there is an active database connection, and if there is, it uses the mysqli_close() function to close the connection and set the $this->con property to false.

The `select` Method

It's one of the most important methods which will be used frequently. Firstly, let's create the tableExists method which is used to check if the table exists or not in the MySQL database.

<?php
2
private function tableExists($table) 
{
    $tablesInDb = mysqli_query($this->con, 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
6
    if($tablesInDb) {
        if(mysqli_num_rows($tablesInDb) == 1) {
            return true;
        } else {
            return false;
        }
    }
}

Next, let's go ahead and implement the select method.

<?php
2
public function select($table, $rows = '*', $where = null, $order = null) 
{
    $q = 'SELECT '.$rows.' FROM '.$table;
6
    if($where != null)
        $q .= ' WHERE '.$where;
9
    if($order != null)
        $q .= ' ORDER BY '.$order;

    if($this->tableExists($table)) {
        $result = $this->con->query($q);

        if($result) {
            $arrResult = $result->fetch_all(MYSQLI_ASSOC);

            return $arrResult;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

It's used to retrieve records from a database. It first builds a SQL query string using the input arguments. It checks if the specified table exists in the database, and if so, executes the query. If the query is successful, the resulting rows are fetched as an associative array and returned. If the query fails, the method returns false. If the specified table does not exist in the database, it also returns false.

The `insert` Method

Let's go through the insert method as shown in the following snippet.

<?php
2
public function insert($table, $values, $rows = null)
{
    if ($this->tableExists($table)) {
        $insert = 'INSERT INTO '.$table;
7
        if ($rows != null) {
            $insert .= ' ('.$rows.')';
        }

        for ($i = 0; $i < count($values); $i++) {
            $values[$i] = mysqli_real_escape_string($this->con, $values[$i]);

            if (is_string($values[$i])) {
                $values[$i] = '"'.$values[$i].'"';
            }
        }

        $values = implode(',', $values);
        $insert .= ' VALUES ('.$values.')';
        $ins = mysqli_query($this->con, $insert);
23
        if ($ins) {
            return true;
        } else {
            return false;
        }
    }
}

It is used to insert data into a table in a MySQL database using the mysqli extension. The function takes three parameters: the table name, the values to be inserted, and optionally the columns to insert into. Firstly, it checks if the specified table exists in the database, and if it does, it constructs the SQL query to insert the data using the provided values and columns. We've used the mysqli_real_escape_string function to sanitizes any string values.

Finally, the query is executed using the mysqli_query() function, and the function returns true if the query was successful, false otherwise.

The `delete` Method

Let's quickly go through the delete method.

<?php
2
public function delete($table, $where = null)
{
    if ($this->tableExists($table)) {
        if ($where == null) {
            $delete = 'DELETE '.$table; 
        } else {
            $delete = 'DELETE FROM '.$table.' WHERE '.$where; 
        }

        $del = $this->con->query($delete);

        if ($del) {
            return true; 
        } else {
           return false; 
        }
    } else {
        return false; 
    }
}

It is used to delete either a table or a row from our database.

The `update` Method

It's also one of the important methods which allows us to update the database information.

The update method implementation should look like this.

<?php
2
public function update($table, $rows, $where)
{
    if ($this->tableExists($table)) {
        // Parse the where values
        // even values (including 0) contain the where rows
        // odd values contain the clauses for the row
        for ($i = 0; $i < count($where); $i++) {
            if ($i % 2 != 0) {
                if (is_string($where[$i])) {
                    if (($i + 1) != null) {
                        $where[$i] = '"' . $where[$i] . '" AND ';
                    } else {
                        $where[$i] = '"' . $where[$i] . '"';
                    }
                }
            }
        }
20
        $where = implode('=', $where);
22
        $update = 'UPDATE ' . $table . ' SET ';
        $keys = array_keys($rows);
25
        $setValues = [];
        foreach ($keys as $key) {
            $value = $rows[$key];
            $setValues[] = "`$key` = '" . mysqli_real_escape_string($this->con, $value)."'";
        }
31
        $update .= implode(',', $setValues);
        $update .= ' WHERE ' . $where;
34
        $query = $this->con->query($update);
36
        if ($query) {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

The update method has three arguments: $table, $rows and $where. The $where array is then parsed to generate the SQL WHERE clause for the update query. The $rows array is parsed to generate the SQL SET clause for the update query. The array keys represent the column names, while the array values represent the new values for the columns. We've used the mysqli_real_escape_string function to sanitizes any string values.

With that, we've completed creating the methods that are required to do database manipulation. You can save it as a Database.php file.

In the next section, we'll see how to use it.

How to Use the `Database` Class

Firstly, let's create a MySQL table in our database so that we can test the CRUD operations on that table.

Go ahead and run the following SQL to create a table.

CREATE TABLE `mysqlcrud` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`email` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
);

It should create the mysqlcrud table in your database.

How to Insert a New Row

Let's see how to insert a new row.

<?php
require "Database.php";
3
$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
5
if ($db->connect()) {
    $db->insert('mysqlcrud', array(1,"Foo Bar","[email protected]"));
} else {
    echo "There was some error connecting to the database.";
}
?>

Firstly, we've created a new Database object with the database credentials passed as parameters. You need to replace it with your credentials. Next, the connect method of the Database class is called to establish a connection to the database. If the connection is successful, the insert method is called with the table name as the first parameter and an array of values for the new row as the second parameter.

If everything goes smoothly, it should create a new row in the mysqlcrud table.

How to Update a Row

Let's see how the update operation works.

<?php
require "Database.php";
3
$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
5
if ($db->connect()) {
    $db->update('mysqlcrud',array('name'=>'Updated Foo Bar'), array('id',1));
} else {
    echo "There was some error connecting to the database.";
}
?>

As you can see, we've called the update method to update the name column of the row.

How to Delete a Row

Next, let's see how to delete a specific row.

<?php
require "Database.php";
3
$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
5
if ($db->connect()) {
    $db->delete('mysqlcrud', "`id` = '1'");
} else {
    echo "There was some error connecting to the database.";
}
?>

It should delete the row with id equals to 1.

How to Retrieve Records

Let's see how you can retrieve all the records from our table.

<?php
require "Database.php";
3
$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
5
if ($db->connect()) {
    $rows = $db->select('mysqlcrud');
} else {
    echo "There was some error connecting to the database.";
}
?>

As you can see, the select method allows us to fetch all the records from the desired table.

So that's how you can perform CRUD operations by using our class.

Real-World OOP With PHP and MySQL

Implement the Database Class

The `connect` Method

The `disconnect` Method

The `select` Method

The `insert` Method

The `delete` Method

The `update` Method

How to Use the `Database` Class

How to Insert a New Row

How to Update a Row

How to Delete a Row

How to Retrieve Records

Recommend

GitHub - voidcosmos/npkill: List any node_modules 📦 dir in your system and how h...

Will AI Take Your Job? How Artificial Intelligence is Shaping the Future of Work

A decade after Elon Musk first began hyping self-driving vehicles, here's why we...

NHL Team Owner Accused of Assaulting Wife, Police Say

燃炸！3月30日DTC出海亿级卖家闭门沙龙重磅来袭，“独站”先机，只等你来

9999元 AOC新款44.5英寸带鱼屏上架：165Hz VA面板

UI设计中的一致性原则,有效提高用户体验

跨境新赛道？万亿级市场需求强劲，高客单价商品平均1分钟出1单

抖音电商“山货上头条”助力海南农特产销售，芒果销量同比增长1370%

Shopee在印尼有多受欢迎？斩获多项第一！

About Joyk

	<?php
2	class Database
3	{
4	private $db_host = '';
5	private $db_user = '';
6	private $db_pass = '';
7	private $db_name = '';
8	private $con = '';
9
	public function __contruct($db_host, $db_user, $db_pass, $db_name)
	{
	$this->db_host = $db_host;
	$this->db_user = $db_user;
	$this->db_pass = $db_pass;
	$this->db_name = $db_name;
	}

	public function connect() {}
	public function disconnect() {}
20	public function select() {}
21	public function insert() {}
22	public function delete() {}
23	public function update() {}
24	}

	<?php
2
3	public function connect()
4	{
5	if (!$this->con) {
6	$this->con = mysqli_connect($this->db_host, $this->db_user, $this->db_pass);
7
8	if($this->con) {
9	$seldb = mysqli_select_db($this->con, $this->db_name);

	if($seldb) {
	return true;
	} else {
	return false;
	}
	} else {
	return false;
	}
	} else {
20	return true;
21	}
22	}

	<?php
2
3	public function disconnect()
4	{
5	if ($this->con) {
6	if (mysqli_close($this->con)) {
7	$this->con = false;
8	return true;
9	} else {
	return false;
	}
	}
	}

	<?php
2
3	private function tableExists($table)
4	{
5	$tablesInDb = mysqli_query($this->con, 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
6
7	if($tablesInDb) {
8	if(mysqli_num_rows($tablesInDb) == 1) {
9	return true;
	} else {
	return false;
	}
	}
	}

	<?php
2
3	public function select($table, $rows = '*', $where = null, $order = null)
4	{
5	$q = 'SELECT '.$rows.' FROM '.$table;
6
7	if($where != null)
8	$q .= ' WHERE '.$where;
9
	if($order != null)
	$q .= ' ORDER BY '.$order;

	if($this->tableExists($table)) {
	$result = $this->con->query($q);

	if($result) {
	$arrResult = $result->fetch_all(MYSQLI_ASSOC);

	return $arrResult;
20	} else {
21	return false;
22	}
23	} else {
24	return false;
25	}
26	}

	<?php
2
3	public function insert($table, $values, $rows = null)
4	{
5	if ($this->tableExists($table)) {
6	$insert = 'INSERT INTO '.$table;
7
8	if ($rows != null) {
9	$insert .= ' ('.$rows.')';
	}

	for ($i = 0; $i < count($values); $i++) {
	$values[$i] = mysqli_real_escape_string($this->con, $values[$i]);

	if (is_string($values[$i])) {
	$values[$i] = '"'.$values[$i].'"';
	}
	}

20	$values = implode(',', $values);
21	$insert .= ' VALUES ('.$values.')';
22	$ins = mysqli_query($this->con, $insert);
23
24	if ($ins) {
25	return true;
26	} else {
27	return false;
28	}
29	}
30	}

	<?php
2
3	public function delete($table, $where = null)
4	{
5	if ($this->tableExists($table)) {
6	if ($where == null) {
7	$delete = 'DELETE '.$table;
8	} else {
9	$delete = 'DELETE FROM '.$table.' WHERE '.$where;
	}

	$del = $this->con->query($delete);

	if ($del) {
	return true;
	} else {
	return false;
	}
	} else {
20	return false;
21	}
22	}

	<?php
2
3	public function update($table, $rows, $where)
4	{
5	if ($this->tableExists($table)) {
6	// Parse the where values
7	// even values (including 0) contain the where rows
8	// odd values contain the clauses for the row
9	for ($i = 0; $i < count($where); $i++) {
	if ($i % 2 != 0) {
	if (is_string($where[$i])) {
	if (($i + 1) != null) {
	$where[$i] = '"' . $where[$i] . '" AND ';
	} else {
	$where[$i] = '"' . $where[$i] . '"';
	}
	}
	}
	}
20
21	$where = implode('=', $where);
22
23	$update = 'UPDATE ' . $table . ' SET ';
24	$keys = array_keys($rows);
25
26	$setValues = [];
27	foreach ($keys as $key) {
28	$value = $rows[$key];
29	$setValues[] = "`$key` = '" . mysqli_real_escape_string($this->con, $value)."'";
30	}
31
32	$update .= implode(',', $setValues);
33	$update .= ' WHERE ' . $where;
34
35	$query = $this->con->query($update);
36
37	if ($query) {
38	return true;
39	} else {
40	return false;
41	}
42	} else {
43	return false;
44	}
45	}

	CREATE TABLE `mysqlcrud` (
2	`id` INT(11) NOT NULL AUTO_INCREMENT,
3	`name` VARCHAR(255) NOT NULL,
4	`email` VARCHAR(255) NOT NULL,
5	PRIMARY KEY (`id`)
6	);

	<?php
2	require "Database.php";
3
4	$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
5
6	if ($db->connect()) {
7	$db->insert('mysqlcrud', array(1,"Foo Bar","[email protected]"));
8	} else {
9	echo "There was some error connecting to the database.";
	}
	?>

Real-World OOP With PHP and MySQL

Implement the Database Class

The connect Method

The disconnect Method

The select Method

The insert Method

The delete Method

The update Method

How to Use the Database Class

How to Insert a New Row

How to Update a Row

How to Delete a Row

How to Retrieve Records

Recommend

About Joyk

The `connect` Method

The `disconnect` Method

The `select` Method

The `insert` Method

The `delete` Method

The `update` Method

How to Use the `Database` Class