This article shows how to create a CRUD (Create, Read, Update, Delete) application system with PHP & MySQL using Object Oriented Programming (OOP) technique. I had written an article before about creating Simple CRUD application with PHP & MySQL but that has been programmed with procedural way. In this article, we create the same kind of CRUD application using OOP.
First of all, we will create a new MySQL database. Let us name the database as ‘test‘.
create database `test`;
Then, we will create a new table in database ‘test’. Let us name the table as ‘users‘.
use `test`;
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
Now, we will create a database connection class named DbConfig. We put our classes inside a folder named classes. This classes folder will be inside our website root folder. So, the path of DbConfig class will be classes/DbConfig.php. This class contains database connection code. Here, we define our database host, database name, database username and database password.
classes/DbConfig.php
<?php
class DbConfig
{
private $_host = 'localhost';
private $_username = 'root';
private $_password = 'root';
private $_database = 'test';
protected $connection;
public function __construct()
{
if (!isset($this->connection)) {
$this->connection = new mysqli($this->_host, $this->_username, $this->_password, $this->_database);
if (!$this->connection) {
echo 'Cannot connect to database server';
exit;
}
}
return $this->connection;
}
}
?>
There are other two classes for this example application. One is the Crud class and the other is Validation class. Crud class is responsible for executing database queries. Validation class is responsible for checking correctness of form POST data, like checking for valid email, valid age, etc.
classes/Crud.php
<?php
include_once 'DbConfig.php';
class Crud extends DbConfig
{
public function __construct()
{
parent::__construct();
}
public function getData($query)
{
$result = $this->connection->query($query);
if ($result == false) {
return false;
}
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
public function execute($query)
{
$result = $this->connection->query($query);
if ($result == false) {
echo 'Error: cannot execute the command';
return false;
} else {
return true;
}
}
public function delete($id, $table)
{
$query = "DELETE FROM $table WHERE id = $id";
$result = $this->connection->query($query);
if ($result == false) {
echo 'Error: cannot delete id ' . $id . ' from table ' . $table;
return false;
} else {
return true;
}
}
public function escape_string($value)
{
return $this->connection->real_escape_string($value);
}
}
?>
classes/Validation.php
<?php
class Validation
{
public function check_empty($data, $fields)
{
$msg = null;
foreach ($fields as $value) {
if (empty($data[$value])) {
$msg .= "$value field empty <br />";
}
}
return $msg;
}
public function is_age_valid($age)
{
//if (is_numeric($age)) {
if (preg_match("/^[0-9]+$/", $age)) {
return true;
}
return false;
}
public function is_email_valid($email)
{
//if (preg_match("/^[_a-z0-9-+]+(\.[_a-z0-9-+]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$/", $email)) {
if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
return true;
}
return false;
}
}
?>
To add data into database, we need an html form.
add.html
<html>
<head>
<title>Add Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form action="add.php" method="post" name="form1">
<table width="25%" border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="age"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Add"></td>
</tr>
</table>
</form>
</body>
</html>
Form action on add.html is add.php. It means that the submitted form data will go to add.php. In add.php, we do a simple validation of checking if the entered name, email & age are empty or not. If they are all filled then the data will be inserted into database table.
The validation part is handled by a new class named Validation. Functions like check_empty, is_age_valid, is_email_valid are present in the Validation class with specific purposes.
As you can see in the below code, we first instantiate the Crud class for database connection and then we instantiate the Validation class. We then check the validation of POST data. If everything is fine then we run the execute() function of Crud class.
add.php
<html>
<head>
<title>Add Data</title>
</head>
<body>
<?php
//including the database connection file
include_once("classes/Crud.php");
include_once("classes/Validation.php");
$crud = new Crud();
$validation = new Validation();
if(isset($_POST['Submit'])) {
$name = $crud->escape_string($_POST['name']);
$age = $crud->escape_string($_POST['age']);
$email = $crud->escape_string($_POST['email']);
$msg = $validation->check_empty($_POST, array('name', 'age', 'email'));
$check_age = $validation->is_age_valid($_POST['age']);
$check_email = $validation->is_email_valid($_POST['email']);
// checking empty fields
if($msg != null) {
echo $msg;
//link to the previous page
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} elseif (!$check_age) {
echo 'Please provide proper age.';
} elseif (!$check_email) {
echo 'Please provide proper email.';
}
else {
// if all the fields are filled (not empty)
//insert data to database
$result = $crud->execute("INSERT INTO users(name,age,email) VALUES('$name','$age','$email')");
//display success message
echo "<font color='green'>Data added successfully.";
echo "<br/><a href='index.php'>View Result</a>";
}
}
?>
</body>
</html>
Data from database is fetched and displayed in index.php file. This is our homepage. This file also contains a link to add data. On every row of displayed data, there is also a link to edit and delete data. Below is a sample image of our homepage:
index.php
<?php
//including the database connection file
include_once("classes/Crud.php");
$crud = new Crud();
//fetching data in descending order (lastest entry first)
$query = "SELECT * FROM users ORDER BY id DESC";
$result = $crud->getData($query);
//echo '<pre>'; print_r($result); exit;
?>
<html>
<head>
<title>Homepage</title>
</head>
<body>
<a href="add.html">Add New Data</a><br/><br/>
<table width='80%' border=0>
<tr bgcolor='#CCCCCC'>
<td>Name</td>
<td>Age</td>
<td>Email</td>
<td>Update</td>
</tr>
<?php
foreach ($result as $key => $res) {
//while($res = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>".$res['name']."</td>";
echo "<td>".$res['age']."</td>";
echo "<td>".$res['email']."</td>";
echo "<td><a href=\"edit.php?id=$res[id]\">Edit</a> | <a href=\"delete.php?id=$res[id]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";
}
?>
</table>
</body>
</html>
Each row of data can be edited separately. Row ID is passed in the URL of edit.php. ID uniquely identifies the data entry.
While adding data, we had two files: add.html and add.php. While editing data, I have kept the entire thing in a single edit.php file. Edit form in HTML and database update code in PHP are present in the same file.
In the code below, at first a single row entry of data is fetched based on the id. The fetched data is displayed in the edit form.
edit.php
<?php
// including the database connection file
include_once("classes/Crud.php");
$crud = new Crud();
//getting id from url
$id = $crud->escape_string($_GET['id']);
//selecting data associated with this particular id
$result = $crud->getData("SELECT * FROM users WHERE id=$id");
foreach ($result as $res) {
$name = $res['name'];
$age = $res['age'];
$email = $res['email'];
}
?>
<html>
<head>
<title>Edit Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form name="form1" method="post" action="editaction.php">
<table border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" value="<?php echo $name;?>"></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="age" value="<?php echo $age;?>"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email" value="<?php echo $email;?>"></td>
</tr>
<tr>
<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>
The edit form action is editaction.php. When the edit form is submitted, it goes to editaction.php. Here, some validation is checked for the POST data and then the particular database table row is updated.
editaction.php
<?php
// including the database connection file
include_once("classes/Crud.php");
include_once("classes/Validation.php");
$crud = new Crud();
$validation = new Validation();
if(isset($_POST['update']))
{
$id = $crud->escape_string($_POST['id']);
$name = $crud->escape_string($_POST['name']);
$age = $crud->escape_string($_POST['age']);
$email = $crud->escape_string($_POST['email']);
$msg = $validation->check_empty($_POST, array('name', 'age', 'email'));
$check_age = $validation->is_age_valid($_POST['age']);
$check_email = $validation->is_email_valid($_POST['email']);
// checking empty fields
if($msg) {
echo $msg;
//link to the previous page
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} elseif (!$check_age) {
echo 'Please provide proper age.';
} elseif (!$check_email) {
echo 'Please provide proper email.';
} else {
//updating the table
$result = $crud->execute("UPDATE users SET name='$name',age='$age',email='$email' WHERE id=$id");
//redirectig to the display page. In our case, it is index.php
header("Location: index.php");
}
}
?>
Each row of data can be deleted separately. Row ID is passed in the URL of delete.php. ID uniquely identifies the data entry. After deletion, the user is redirected to homepage (index.php).
delete.php
<?php
//including the database connection file
include_once("classes/Crud.php");
$crud = new Crud();
//getting id of the data from url
$id = $crud->escape_string($_GET['id']);
//deleting the row from table
//$result = $crud->execute("DELETE FROM users WHERE id=$id");
$result = $crud->delete($id, 'users');
if ($result) {
//redirecting to the display page (index.php in our case)
header("Location:index.php");
}
?>
Download Full Source Code: Simple Create, Read, Update, Delete in PHP & MySQL using Object Oriented Programming (OOP)
Hope this helps. Thanks.