PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL
"unknow" - 27/Dec/2018
"unknow" - 27/Dec/2018
[SHOWTOGROUPS=4,20]
CRUD operation helps to Create, Read, Update and Delete database records. Add, Edit, Update and Delete functionality is commonly used in the data management section of every web application. You can easily implement the CRUD operations with MySQL in PHP. Probably, you’ve integrated the PHP CRUD operation many times on the website, but today we’ll show you the user-friendly way to implement CRUD functionality in PHP.
Generally, in PHP CRUD operations the web page is refreshed or redirected each time an action is requested. To make this CRUD process user-friendly, it can be implemented without page refresh using jQuery and Ajax. In this tutorial, we’ll implement PHP CRUD operations without page refresh using jQuery, Ajax, and MySQL. The example PHP CRUD script will help to read, add, update, and delete the records from MySQL database.
The following functionality will be implemented to build PHP CRUD Operations with Bootstrap 4 using jQuery, Ajax, and MySQL.
Create Database Table
To store and manage the data a table needs to be created in the database. The following SQL creates a users table with some basic fields in the MySQL database.
Database Class (DB.class.php)
The DB class handles all the database related operations (connect, insert, update, and delete). Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.
[/SHOWTOGROUPS]
CRUD operation helps to Create, Read, Update and Delete database records. Add, Edit, Update and Delete functionality is commonly used in the data management section of every web application. You can easily implement the CRUD operations with MySQL in PHP. Probably, you’ve integrated the PHP CRUD operation many times on the website, but today we’ll show you the user-friendly way to implement CRUD functionality in PHP.
Generally, in PHP CRUD operations the web page is refreshed or redirected each time an action is requested. To make this CRUD process user-friendly, it can be implemented without page refresh using jQuery and Ajax. In this tutorial, we’ll implement PHP CRUD operations without page refresh using jQuery, Ajax, and MySQL. The example PHP CRUD script will help to read, add, update, and delete the records from MySQL database.
The following functionality will be implemented to build PHP CRUD Operations with Bootstrap 4 using jQuery, Ajax, and MySQL.
- The user’s data will be fetched from the database and listed with the add, edit, and delete link.
- The add link allows the user to add new data to the database.
- The edit link allows the user to update previously inserted data.
- The delete link allows the user to delete the data from the database.
- All CRUD operations will happen on a single page without page refresh or redirect.
Код:
php_crud_jquery_ajax_mysql/
├── DB.class.php
├── index.php
├── userAction.php
├── js/
│ └── jquery.min.js
└── bootstrap/
├── bootstrap.min.css
└── bootstrap.min.jsCreate Database Table
To store and manage the data a table needs to be created in the database. The following SQL creates a users table with some basic fields in the MySQL database.
Код:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;Database Class (DB.class.php)
The DB class handles all the database related operations (connect, insert, update, and delete). Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.
- __construct() – Connect to the database with PHP and MySQLi Extension.
- getRows() – Fetch records from the database based on the specified conditions.
- insert() – Insert data into the database.
- update() – Update data into the database.
- delete() – Delete data from the database.
Код:
<?php
/*
* DB Class
* This class is used for database related (connect, insert, update, and delete) operations
* @author CodexWorld.com
* @url http://www.codexworld.com
* @license http://www.codexworld.com/license
*/
class DB{
private $dbHost = "localhost";
private $dbUsername = "root";
private $dbPassword = "root";
private $dbName = "codexworld";
public function __construct(){
if(!isset($this->db)){
// Connect to the database
$conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
if($conn->connect_error){
die("Failed to connect with MySQL: " . $conn->connect_error);
}else{
$this->db = $conn;
}
}
}
/*
* Returns rows from the database based on the conditions
* @param string name of the table
* @param array select, where, order_by, limit and return_type conditions
*/
public function getRows($table, $conditions = array()){
$sql = 'SELECT ';
$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
$sql .= ' FROM '.$table;
if(array_key_exists("where",$conditions)){
$sql .= ' WHERE ';
$i = 0;
foreach($conditions['where'] as $key => $value){
$pre = ($i > 0)?' AND ':'';
$sql .= $pre.$key." = '".$value."'";
$i++;
}
}
if(array_key_exists("order_by",$conditions)){
$sql .= ' ORDER BY '.$conditions['order_by'];
}else{
$sql .= ' ORDER BY id DESC ';
}
if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
$sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit'];
}elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
$sql .= ' LIMIT '.$conditions['limit'];
}
$result = $this->db->query($sql);
if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
switch($conditions['return_type']){
case 'count':
$data = $result->num_rows;
break;
case 'single':
$data = $result->fetch_assoc();
break;
default:
$data = '';
}
}else{
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
$data[] = $row;
}
}
}
return !empty($data)?$data:false;
}
/*
* Insert data into the database
* @param string name of the table
* @param array the data for inserting into the table
*/
public function insert($table, $data){
if(!empty($data) && is_array($data)){
$columns = '';
$values = '';
$i = 0;
if(!array_key_exists('created',$data)){
$data['created'] = date("Y-m-d H:i:s");
}
if(!array_key_exists('modified',$data)){
$data['modified'] = date("Y-m-d H:i:s");
}
foreach($data as $key=>$val){
$pre = ($i > 0)?', ':'';
$columns .= $pre.$key;
$values .= $pre."'".$this->db->real_escape_string($val)."'";
$i++;
}
$query = "INSERT INTO ".$table." (".$columns.") VALUES (".$values.")";
$insert = $this->db->query($query);
return $insert?$this->db->insert_id:false;
}else{
return false;
}
}
/*
* Update data into the database
* @param string name of the table
* @param array the data for updating into the table
* @param array where condition on updating data
*/
public function update($table, $data, $conditions){
if(!empty($data) && is_array($data)){
$colvalSet = '';
$whereSql = '';
$i = 0;
if(!array_key_exists('modified',$data)){
$data['modified'] = date("Y-m-d H:i:s");
}
foreach($data as $key=>$val){
$pre = ($i > 0)?', ':'';
$colvalSet .= $pre.$key."='".$this->db->real_escape_string($val)."'";
$i++;
}
if(!empty($conditions)&& is_array($conditions)){
$whereSql .= ' WHERE ';
$i = 0;
foreach($conditions as $key => $value){
$pre = ($i > 0)?' AND ':'';
$whereSql .= $pre.$key." = '".$value."'";
$i++;
}
}
$query = "UPDATE ".$table." SET ".$colvalSet.$whereSql;
$update = $this->db->query($query);
return $update?$this->db->affected_rows:false;
}else{
return false;
}
}
/*
* Delete data from the database
* @param string name of the table
* @param array where condition on deleting data
*/
public function delete($table, $conditions){
$whereSql = '';
if(!empty($conditions)&& is_array($conditions)){
$whereSql .= ' WHERE ';
$i = 0;
foreach($conditions as $key => $value){
$pre = ($i > 0)?' AND ':'';
$whereSql .= $pre.$key." = '".$value."'";
$i++;
}
}
$query = "DELETE FROM ".$table.$whereSql;
$delete = $this->db->query($query);
return $delete?true:false;
}
}