PHP CRUD class treats a specific database you try to access to as a encapulation. Therefore, any Create/Read/Update/Delete operation by PHP on the tables in the specific database should make a function call to the public methods it has.
We develop it based on PDO(PHP Data Object). PDO lets you connect to MySQL database, and moreover, perform operations in MySQL specially with exception handling.
In addition to this, PHP CRUD class friendly relates the array of parameters to the placeholders in SQL statement one by one, so the scripts or programs you write will be readable.
All codes here are not complicated, so you can easily understand even though you are still students in school. To benefit your learning, we will provide you download link to a zip file thus you can get all source codes for future usage.
Estimated reading time: 9 minutes
EXPLORE THIS ARTICLE
TABLE OF CONTENTS
BONUS
Source Code Download
We have released it under the MIT license, so feel free to use it in your own project or your school homework.
Download Guideline
- Prepare HTTP server such as XAMPP or WAMP in your windows environment.
- Download and unzip into a folder that http server can access.
SECTION A
The Basics
You will see a small CRUD class with even less PHP codes than others, but it has complete functions or say methods to use. Let us explain its 3 methods fetch(), fetchAll(), and execute() from the view of design. Moreover, we will write about the important issue of exception handling.
PHP CRUD Class
<?php
/* A Simple DB Class
e.g.
$conf=array(
'host' => 'localhost',
'dbname' => 'mydb',
'username' => 'myuser',
'password' => '12345678',
'charset' => 'utf8',
);
*/
class DB {
private $conn = null;
public $error = "";
public $rowCount;
function __construct($conf) {
// initiate by connect to db
try {
$str = "mysql:host=" . $conf['host'] . ";dbname=" . $conf['dbname'] . ";charset=" . $conf['charset'];
$this->conn = new PDO($str, $conf['username'], $conf['password'], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false
]);
return true;
}
catch (Exception $ex) {
print_r($ex);
die();
}
}
function execute($sql, $params=null) {
// insert, update or delete
try {
$stmt = $this->conn->prepare($sql);
$stmt->execute($params);
$this->rowCount = $stmt->rowCount();
return true;
}
catch (Exception $ex) {
$this->error = $ex->getMessage();
return false;
}
}
function fetchAll($sql, $params=null, $all=true) {
// select
try {
$stmt = $this->conn->prepare($sql);
$stmt->execute($params);
$this->rowCount = $stmt->rowCount();
if ($all)
return $stmt->fetchAll();
else
return $stmt->fetch();
}
catch (Exception $ex) {
$this->error = $ex->getMessage();
return false;
}
}
function fetch($sql, $params=null) {
// select
return $this->fetchAll($sql, $params, false);
}
} // DB class
?>
The following statements clarify several features.
- In
function __construct($conf),$confarray defineshost,dbname,username,password,charsetfor PHP CRUD class to initiate an object along with immediate connection to MySQL. - If exception happens, PHP codes
try .. catchwill dispatch errors to show why a connection attempt fail. - Based PDO functions,
function execute(...),function fetchAll(...)andfunction fetch(...)cooperatesprepare($sql)withexecute($params)to complete operations of insert, update, delete, and inquery. - In addition to that, inquery operation use other PDO functions such as
$stmt->fetchAll()and$stmt->fetch(), by which conditions of finding one row or finding more rows can be distinguished.
MySQL Connection in PHP CRUD Class
<?php
/* config database */
$dbconfig=array(
'host' => 'localhost',
'dbname' => 'mydb',
'username' => 'myuser',
'password' => '12345678',
'charset' => 'utf8',
);
/* connect database */
$db = new DB($dbconfig);
?>
Just complete the array $dbconfig and initiate an object by $db = new DB($dbconfig), you can connect to MySQL and get back a handle $db for subsequent usage.
Connection Error in PHP CRUD Class
The example picture show an error condition often occurred while trying to connect to MySQL at the beginning of running a program. However, this class can capture and display it to avoid silent situation that won’t make people know what happens.
Table Schema
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`price` int(11) COLLATE utf8_unicode_ci NOT NULL,
`photo` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `products`
ADD PRIMARY KEY `id` (`id`);
| field | description |
|---|---|
| id | product identification |
| name | product name |
| price | product price |
| proto | file name of product photo |
For clarification, we prepare a shop product example to illustrate how the database object carries database operations on MySQL into effect. Here is its table schema.
SECTION B
Create
When a CREATE attempt comes to AJAX controller, the INSERT operation in PHP CRUD class will relate parameters in array to question mark placeholders in SQL statement, and add a new row to database.
Create a Row
Enter 4 pieces of data as above and submit them, AJAX controller will reply whether it is successful or not. Subsequently, we will write about how AJAX controller does it.
INSERT Usage in PHP CRUD Class
function actionCreate ($d) {
global $db, $tblname;
$error = "";
/* database */
$sql = "INSERT {$tblname} (id, name, price, photo) VALUES (?, ?, ?, ?)";
$ary = array($d['id'], $d['name'], $d['price'], $d['photo']);
if(!$db->execute($sql, $ary)) $error = $db->error;
elseif($db->rowCount == 0) $error = "CREATE Nothing";
/* response */
echo json_encode([
"sts" => $error=="" ? "succ" : "fail",
"err" => $error,
"msg" => "CREATE success",
]);
}
In SQL statement, question marks placeholders (?, ?, ?, ?) denote placeholders for incoming parameters in array array($d['id'], $d['name'], $d['price'], $d['photo']) to be related to data fields in database table.
Create Error in PHP CRUD Class
If changing primary key to an existing one with ID=A005, then you can see how the dataobject throws an error message with SQLSTATE code.
SECTION C
Read
To read content means to call fetch() or fetchAll() method in PHP CRUD class, and if one row or more rows are found, then HTML lists them with desired layout.
Read Content
The shop example depicts products mainly with pictures, secondly along with id, name, and price.
SELECT Usage in PHP CRUD Class
<?php
require "config.php";
$sql = "SELECT * FROM products";
$products = $db->fetchAll($sql);
?>
<!DOCTYPE html>
<html>
...
Notice that without requesting AJAX controller, require "config.php" connect to database hiddenly, and then $db->fetchAll($sql) gets products data. Of course, if you want to add parameters in SQL statement, question marks placeholders can be put as the second argument in fetchAll().
SECTION D
Update
When AJAX controller receive a UPDATE message, it can either update immediately or check existance before update. The former method seem intuitive and not safe, however, we suggest it. Because the exception handling in PHP CRUD class helps by dispatching error message to client site to notice that operation has not been done as the row is absent.
Update a Row by Searching ID
If trying to update product with id=A005, you can submit with modified name or price, and then make a inquery to check how the row has changed.
UPDATE Usage in PHP CRUD Class
function actionUpdate ($d) {
global $db, $tblname;
$error = "";
/* database */
$sql = "UPDATE {$tblname} SET name=?, price=?, photo=? WHERE id=? ";
$ary = array($d['name'], $d['price'], $d['photo'], $d['id']);
if(!$db->execute($sql, $ary)) $error = $db->error;
elseif($db->rowCount == 0) $error = "UPDATE Nothing";
/* response */
echo json_encode([
"sts" => $error=="" ? "succ" : "fail",
"err" => $error,
"msg" => "UPDATE success",
]);
}
Similarly, question mark placeholders link parameters to SQL statement. When $db->execute($sql, $ary) has executed, the status value $db->error and $db->rowCount in PHP CRUD class can determine what message the client site will see.
Update Error in PHP CRUD Class
If changing primary key to a non-existed one with id=A015, however, PDO won’t throw error because this case is just an invalid query, rather than an exception. At this time, you can check the number of affected rows $db->rowCount to determine how to prompt the application error to users.
SECTION E
Delete
When a DELETE attempt comes to AJAX controller, the DELETE operation in PHP CRUD class will search primary key for locating a row, and delete it from database.
Delete a Row by Searching ID
If trying to delete product with id=A006, you can submit directly, and then make a inquery to check whether the product has been removed.
DELETE Usage in PHP CRUD Class
function actionDelete ($d) {
global $db, $tblname;
$error = "";
/* database */
$sql = "DELETE FROM {$tblname} WHERE id=?";
$ary = array($d['id']);
if(!$db->execute($sql, $ary)) $error = $db->error;
elseif($db->rowCount == 0) $error = "DELETE Nothing";
/* response */
echo json_encode([
"sts" => $error=="" ? "succ" : "fail",
"err" => $error,
"msg" => "DELETE success",
]);
}
The DELETE usage is similar to that for UPDATE. As changing primary key to be, for example, id=A017 which is not existed in database, $db->rowCount will become zero to indicate that no record has been deleted.
FINAL
Conclusion
The PDOException does not refer to invalid query as an error, instead the execution return TRUE. Thus we have to check the number of affected rows to see if the result is what we want. The class we provided does help.
Thank you for reading, and we have suggested more helpful articles here. If you want to share anything, please feel free to comment below. Good luck and happy coding!
Suggested Reading
- 6 Stages of Registration Form in PHP with Email Check
- How Cordova Hybrid APP Use Its Default Database
TRY IT
Quick Experience
That’s all for this project, and here is the link that let you experience the program. Please kindly leave your comments for our enhancement.
Try It Yourself
Click here to execute the source code, thus before studying the downloaded codes, you can check whether it is worthy.