Home > Article > Backend Development > Real-world OOP with PHP and MySQL
Many examples from robots to bicycles serve as simple explanations of OOP. However, I prefer to demonstrate how OOP works in real-life scenarios, especially for programmers. A practical example is creating MySQL CRUD (Create, Read, Update, and Delete) classes. This way, programmers can effectively manage entries in a project, regardless of the database's design.
Once we determine the requirements, creating the basic structure of the class is relatively simple. First, we need to make sure we can perform basic MySQL operations. In order to achieve this, we need to implement the following functions:
Let’s take a quick look at what our class definition looks like:
<?php class Database { private $db_host = ''; private $db_user = ''; private $db_pass = ''; private $db_name = ''; private $con = ''; 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 is a skeleton class called Database
that represents a database connection and provides methods for interacting with the database. We define different private properties such as $db_host
, $db_user
, $db_pass
, $db_name
and $con
. This class is used to store database connection details and the connection object itself.
In the next section, we will detail the actual implementation of this class.
In this section, we will implement the methods required to perform different database operations.
connect
MethodContinue to add the connect
method, as shown in the following code snippet.
<?php public function connect() { if (!$this->con) { $this->con = mysqli_connect($this->db_host, $this->db_user, $this->db_pass); 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; } }
connect
The purpose of the method is to establish a connection to the MySQL database using the mysqli extension. First, we check whether there is already a connection to the database. If it exists, the method just returns true
indicating that the connection has been established and no need to reconnect. If there is no connection, it will try to connect to the database using the mysqli_connect
function.
Once the database connection is successful, the database will be selected by calling the mysqli_select_db
function. Finally, if the database selection is successful, true
is returned, indicating that the connection is established and the database is selected. If the connection or database selection fails, false
is returned.
disconnect
MethodLet's take a look at the disconnect
method.
<?php 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 so, closes the connection using the
mysqli_close()
function and sets the $this->con
property to false
.
select
MethodThis is one of the most important and frequently used methods. First, we create the tableExists
method to check whether the table exists in the MySQL database.
<?php private function tableExists($table) { $tablesInDb = mysqli_query($this->con, 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"'); if($tablesInDb) { if(mysqli_num_rows($tablesInDb) == 1) { return true; } else { return false; } } }
Next, we continue to implement the select
method.
<?php public function select($table, $rows = '*', $where = null, $order = null) { $q = 'SELECT '.$rows.' FROM '.$table; if($where != null) $q .= ' WHERE '.$where; 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 is used to retrieve records from the database. It first builds a SQL query string using the input parameters. It checks whether the specified table exists in the database and, if so, executes the query. If the query is successful, the result rows are fetched and returned as an associative array. If the query fails, this method returns false
. If the specified table does not exist in the database, false
is also returned.
insert
MethodLet's take a look at the insert
method as shown in the following code snippet.
<?php public function insert($table, $values, $rows = null) { if ($this->tableExists($table)) { $insert = 'INSERT INTO '.$table; 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); if ($ins) { return true; } else { return false; } } }
It is used to insert data into a table in a MySQL database using the mysqli extension. This function takes three parameters: the table name, the value to be inserted, and (optional) the column to be inserted. First, it checks if the specified table exists in the database and, if so, constructs a SQL query to insert data using the provided values and columns. We use the mysqli_real_escape_string
function to sanitize any string values.
Finally use the mysqli_query()
function to execute the query. If the query is successful, the function returns true
, otherwise it returns false
.
delete
MethodLet’s take a quick look at the delete
method.
<?php 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 tables or rows from the database.
update
MethodThis is also one of the important ways for us to update database information.
update
The method implementation should look like this.
<?php 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] . '"'; } } } } $where = implode('=', $where); $update = 'UPDATE ' . $table . ' SET '; $keys = array_keys($rows); $setValues = []; foreach ($keys as $key) { $value = $rows[$key]; $setValues[] = "`$key` = '" . mysqli_real_escape_string($this->con, $value)."'"; } $update .= implode(',', $setValues); $update .= ' WHERE ' . $where; $query = $this->con->query($update); if ($query) { return true; } else { return false; } } else { return false; } }The
update
method has three parameters: $table
, $rows
, and $where
. The $where
array is then parsed to generate the SQL WHERE
clause for the update query. Parses the $rows
array to generate the SQL SET
clause for the update query. The array keys represent the column names, and the array values represent the new values of the columns. We use the mysqli_real_escape_string
function to sanitize any string values.
至此,我们完成了数据库操作所需方法的创建。您可以将其另存为 Database.php 文件。
在下一节中,我们将了解如何使用它。
数据库
类首先,我们在数据库中创建一个 MySQL 表,以便我们可以测试该表上的 CRUD 操作。
继续运行以下 SQL 来创建表。
CREATE TABLE `mysqlcrud` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) );
它应该在您的数据库中创建 mysqlcrud
表。
让我们看看如何插入新行。
<?php require "Database.php"; $db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME"); if ($db->connect()) { $db->insert('mysqlcrud', array(1,"Foo Bar","foobar@yahoo.com")); } else { echo "There was some error connecting to the database."; } ?>
首先,我们创建了一个新的 Database
对象,并将数据库凭据作为参数传递。您需要将其替换为您的凭据。接下来调用Database
类的connect
方法来建立与数据库的连接。如果连接成功,则调用 insert
方法,并将表名作为第一个参数,将新行的值数组作为第二个参数。
如果一切顺利,它应该在 mysqlcrud
表中创建一个新行。
让我们看看更新操作是如何工作的。
<?php require "Database.php"; $db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME"); if ($db->connect()) { $db->update('mysqlcrud',array('name'=>'Updated Foo Bar'), array('id',1)); } else { echo "There was some error connecting to the database."; } ?>
如您所见,我们调用了 update
方法来更新该行的 name
列。
接下来,让我们看看如何删除特定行。
<?php require "Database.php"; $db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME"); if ($db->connect()) { $db->delete('mysqlcrud', "`id` = '1'"); } else { echo "There was some error connecting to the database."; } ?>
它应该删除 id 等于 1
的行。
让我们看看如何从表中检索所有记录。
<?php require "Database.php"; $db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME"); if ($db->connect()) { $rows = $db->select('mysqlcrud'); } else { echo "There was some error connecting to the database."; } ?>
如您所见,select
方法允许我们从所需的表中获取所有记录。
这就是您如何使用我们的类执行 CRUD 操作。
The above is the detailed content of Real-world OOP with PHP and MySQL. For more information, please follow other related articles on the PHP Chinese website!