Skip to main content

Object Oriented CRUD Operation with PHP and MySQL

CRUD (Create, Read, Update and Delete) with database is a common functionality of web applications. In this tutorial you learn how to develop CRUD operation with PHP and MySQL using Object Oriented Programming (OOP) technique. The tutorial explained in easy steps with live demo to handle create, read, update and delete functionality into MySQL database with Employee data using PHP OOP. You can also download complete source code of live demo.

As we have covered this tutorial with live demo to create CRUD operation with PHP and MySQL using Object Oriented Programming (OOP) technique, so the file structure for this example is following.

  • index.php
  • Employee.php
  • create.php
  • read.php
  • update.php
  • delete.php

Steps1: Create MySQL Database Table
As in this tutorial, we will perform CRUD operation on employee data, so first we will create employee MySQL database table to perform operations. So we will use below query to create table.

CREATE TABLE `employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
`employee_name` varchar(255) NOT NULL COMMENT 'employee name',
`employee_salary` double NOT NULL COMMENT 'employee salary',
`employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';



Steps2: Create Employee Class with CRUD Method

Now we will create class Employee to handle connection to MySQL database and CRUD operations like select, insert, update and delete with MySQL database. We will have method get() to select employee records, method insert() to insert employee record, method update() to update employee details and method delete() to delete employee records. Here is complete Employee class with all method, you just need to change MySQL database connection details when run on your server.

<?php
class Employee {
private $databaseHost = "localhost";
private $databaseUser = "root";
private $databasePass = "";
private $databaseName = "phpzag_demos";
private $connection = false;
private $result = array();
private $myQuery = "";
private $numResults = "";
public function __construct() {
self::connect();
}
private function connect(){
if(!$this->connection){
$connected = @mysql_connect($this->databaseHost,$this->databaseUser,$this->databasePass);
@mysql_set_charset('utf8', $connected);
if($connected){
$seldb = @mysql_select_db($this->databaseName,$connected);
if($seldb){
$this->connection = true;
return true;
}else{
array_push($this->result,mysql_error());
return false;
}
}else{
array_push($this->result,mysql_error());
return false;
}
}else{
return true;
}
}
public function get($table, $rows = '*', $join = null, $where = null, $order = null, $limit = null){
$selectQuery = 'SELECT '.$rows.' FROM '.$table;
if($join != null){
$selectQuery .= ' JOIN '.$join;
}
if($where != null){
$selectQuery .= ' WHERE '.$where;
}
if($order != null){
$selectQuery .= ' ORDER BY '.$order;
}
if($limit != null){
$selectQuery .= ' LIMIT '.$limit;
}
$this->myQuery = $selectQuery;
if($this->checkTable($table)){
$query = @mysql_query($selectQuery);
if($query){
$this->numResults = mysql_num_rows($query);
for($row = 0; $row < $this->numResults; $row++){
$result = mysql_fetch_array($query);
$keys = array_keys($result);
for($key = 0; $key < count($keys); $key++){ if(!is_int($keys[$key])){ if(mysql_num_rows($query) >= 1){
$this->result[$row][$keys[$key]] = $result[$keys[$key]];
}else{
$this->result = null;
}
}
}
}
return true;
}else{
array_push($this->result,mysql_error());
return false;
}
}else{
return false;
}
}
public function insert($table,$params=array()){
if($this->checkTable($table)){
$sqlQuery='INSERT INTO `'.$table.'` (`'.implode('`, `',array_keys($params)).'`) VALUES ("' . implode('", "', $params) . '")';
$this->myQuery = $sqlQuery;
if($ins = @mysql_query($sqlQuery)){
array_push($this->result,mysql_insert_id());
return true;
}else{
array_push($this->result,mysql_error());
return false;
}
} else {
return false;
}
}
public function update($table,$params=array(),$where){
if($this->checkTable($table)){
$args=array();
foreach($params as $field=>$value){
$args[]=$field.'="'.$value.'"';
}
$sqlQuery='UPDATE '.$table.' SET '.implode(',',$args).' WHERE '.$where;
$this->myQuery = $sqlQuery;
if($query = @mysql_query($sqlQuery)){
array_push($this->result,mysql_affected_rows());
return true;
}else{
array_push($this->result,mysql_error());
return false;
}
}else{
return false;
}
}
public function delete($table,$where = null){
if($this->checkTable($table)){
if($where == null){
$deleteQuery = 'DROP TABLE '.$table;
}else{
$deleteQuery = 'DELETE FROM '.$table.' WHERE '.$where;
}
if($del = @mysql_query($deleteQuery)){
array_push($this->result,mysql_affected_rows());
$this->myQuery = $deleteQuery;
return true;
}else{
array_push($this->result,mysql_error());
return false;
}
}else{
return false;
}
}
private function checkTable($table){
$tableExist = @mysql_query('SHOW TABLES FROM '.$this->databaseName.' LIKE "'.$table.'"');
if($tableExist){
if(mysql_num_rows($tableExist)==1){
return true;
}else{
array_push($this->result,$table." does not exist in this database");
return false;
}
}
}
public function getResult(){
$value = $this->result;
$this->result = array();
return $value;
}
public function escapeString($data){
return mysql_real_escape_string($data);
}
public function check_empty($data, $fields) {
$msg = null;
foreach ($fields as $value) {
if (empty($data[$value])) {
$msg .= "$value field empty";
}
}
return $msg;
}
}

Steps3: Handle Employee Insert Functionality
Now we will handle Employee insert functionality into MySQL Database table. For this, we will design a employee details HTML Form in create.php file.

<div class="container">
<h3>Add Employee Details</h3>
<form method="post" name="form1" >
<table class="table-condensed" width="25%" border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Age</td>
<td><input type="number" name="age"></td>
</tr>
<tr>
<td>Salary</td>
<td><input type="number" name="salary"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Save" class="btn btn-info"></td>
</tr>
</table>
</form>
</div>

Then we will handle functionality to insert Employee records into MySQL database table Employee. So we will include class Employee.php and then create Employee object. Then on form submit, we will create array of employee details and call Employee method $emp->insert(’employee’,$array); to insert employee details into Employee table.

<?php
include_once("classes/Employee.php");
$emp = new Employee();
$inserted = 0;
if(isset($_POST['Submit'])) {
$name = $emp->escapeString($_POST['name']);
$age = $emp->escapeString($_POST['age']);
$salary = $emp->escapeString($_POST['salary']);
$emptyInput = $emp->check_empty($_POST, array('name', 'age', 'salary'));
if(!$emptyInput) {
$array = array(
"employee_name" => $name,
"employee_age" => $age,
"employee_salary" => $salary
);
$emp->insert('employee',$array); // Table name, column names and respective values
$inserted = $emp->getResult();
}
}
?>

Steps4: Handle Employee Select Functionality
Now we will display employee records from MySQL database in read.php. We will include class Employee.php and create object and then call method $emp->get(); to get employee records.
<?php
include_once("classes/Employee.php");
$emp = new Employee();
$emp->get('employee', '*', NULL, "", 'id DESC LIMIT 50');
$result = $emp->getResult();
?>

Then we will display employee records in HTML table.
<table class="table table-responsive">
<thead>
<tr>
<th>Name</th>
<th>Age</th>
<th>Salary</th>
<th></th>
</tr>
</thead>
<tbody>
<?php
foreach ($result as $key => $res) {
echo "<tr>";
echo "<td>".$res['employee_name']."</td>";
echo "<td>".$res['employee_age']."</td>";
echo "<td>".$res['employee_salary']."</td>";
echo "<td><a href=\"update.php?id=$res[id]\" class=\"btn btn-info\" role=\"button\">Edit</a> <a href=\"delete.php?id=$res[id]\" onClick=\"return confirm('Are you sure you want to delete?')\" class=\"btn btn-info\" role=\"button\">Delete</a></td>";
}
?>
</tbody>
</table

Steps5: Handle Employee Update Functionality
Now we will handle functionality to update employee in file update.php. first we will create employee edit HTML Form.

<div class="container">
<h3>Edit Employee Details</h3>
<form name="form1" method="post">
<table class="table-condensed" width="25%" 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="salary" value="<?php echo $salary;?>"></td>
</tr>
<tr>
<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
<td><input type="submit" name="update" value="Update" class="btn btn-info"></td>
</tr>
</table>
</form>
</div>

Then we will display employee details in edit form to edit employee details.

<?php
include_once("classes/Employee.php");
$emp = new Employee();
$id = $emp->escapeString($_GET['id']);
$emp->get('employee', '*', NULL, "id='$id'");
$result = $emp->getResult();
foreach ($result as $res) {
$name = $res['employee_name'];
$age = $res['employee_age'];
$salary = $res['employee_salary'];
}
?>

Then on employee update form submit, we will handle functionality to update employee details using method $emp->update().

<?php
if(isset($_POST['update'])) {
$id = $emp->escapeString($_POST['id']);
$name = $emp->escapeString($_POST['name']);
$age = $emp->escapeString($_POST['age']);
$salary = $emp->escapeString($_POST['salary']);
$emptyInput = $emp->check_empty($_POST, array('name', 'age', 'salary'));
if(!$emptyInput) {
$array = array(
"employee_name" => $name,
"employee_age" => $age,
"employee_salary" => $salary
);
$emp->update('employee',$array,"id='$id'");
if($emp->getResult()){
header("Location: index.php");
}
}
}
?>

Steps3: Handle Employee Delete Functionality
Now finally we will handle employee delete functionality in delete.php by calling Employee method $emp->delete().

<?php
include_once("classes/Employee.php");
$emp = new Employee();
$id = $emp->escapeString($_GET['id']);
$emp->delete('employee',"id='$id'");
if($emp->getResult()) {
header("Location:index.php");
}
?>

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo

     

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!