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.

Also, read:

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");
}
?>

7 thoughts on “Object Oriented CRUD Operation with PHP and MySQL”

James Morris says:

10 May, 2019 at 6:18 am

demo and download show only blank pages

1. PHPZAG Team says:
  
  11 May, 2019 at 8:28 am
  
  Fixed, now it’s working. Thanks!
  
Fito Dido says:

7 December, 2020 at 2:32 pm

demo and download don’t work

1. PHPZAG Team says:
  
  12 December, 2020 at 5:36 pm
  
  It’s fixed now, thanks!
  
Henry says:

14 January, 2021 at 12:03 pm

Very neat. Just a template I was looking for. Comprehensive and succinct.

JK says:

16 January, 2021 at 12:54 am

I get Fatal error: Uncaught Error: Call to undefined function mysql_connect() , in the class Employee.php. Any clues why? mysqli seems to work but then the next one gets the error:
Call to undefined function mysql_set_charset()

1. PHPZAG Team says:
  
  16 January, 2021 at 7:50 pm
  
  Try checking to see if the PHP MySQL extension module is being loaded. If not then enable to load this in php.ini. Thanks!

Object Oriented CRUD Operation with PHP and MySQL

7 thoughts on “Object Oriented CRUD Operation with PHP and MySQL”

Leave a Reply Cancel reply

Latest Tutorials

7 thoughts on “Object Oriented CRUD Operation with PHP and MySQL”

Leave a Reply Cancel reply

Latest Tutorials

Popular Tags