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.
Before getting started to create CRUD application with PHP using jQuery, Ajax, and MySQLi, take a look at the files structure.
php_crud_jquery_ajax_mysql/ ├── DB.class.php ├── index.php ├── userAction.php ├── js/ │ └── jquery.min.js └── bootstrap/ ├── bootstrap.min.css └── bootstrap.min.js
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=utf8 COLLATE=utf8_unicode_ci;
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.
<?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; } }
This is the main view file which is visible to the user. In this single page, the user can do all the CRUD operations without page refresh.
jQuery Library:
The jQuery and Ajax are used to handle CRUD operations without page refresh, so, include the jQuery library.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
Bootstrap Library:
The Bootstrap is used to integrate modal popup and styling the table, list, form fields, and links. Include the CSS & JS files of the Bootstrap 4 library.
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" crossorigin="anonymous"> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" crossorigin="anonymous"></script>
JavaScript Code:
The following JavaScript code handles the CRUD request using jQuery and Ajax.
userAction.php
) using jQuery and Ajax. On success, the response HTML is rendered in the user data list table.userAction.php
) using jQuery and Ajax. Based on the response, the message is shown to the user.userAction.php
) and set the respective value in the form fields.add
param is set in the onclick
attribute of the submit button (#userSubmit
).edit
param is set in the onclick
attribute of the submit button (#userSubmit
). Also, the editUser()
function is called to pre-filled the user’s data in the HTML form based on the rowId.onclick
attribute is removed from the submit button (#userSubmit
). Also, the pre-filled form data and the status message is removed from the HTML elements.<script> // Update the users data list function getUsers(){ $.ajax({ type: 'POST', url: 'userAction.php', data: 'action_type=view', success:function(html){ $('#userData').html(html); } }); } // Send CRUD requests to the server-side script function userAction(type, id){ id = (typeof id == "undefined")?'':id; var userData = '', frmElement = ''; if(type == 'add'){ frmElement = $("#modalUserAddEdit"); userData = frmElement.find('form').serialize()+'&action_type='+type+'&id='+id; }else if (type == 'edit'){ frmElement = $("#modalUserAddEdit"); userData = frmElement.find('form').serialize()+'&action_type='+type; }else{ frmElement = $(".row"); userData = 'action_type='+type+'&id='+id; } frmElement.find('.statusMsg').html(''); $.ajax({ type: 'POST', url: 'userAction.php', dataType: 'JSON', data: userData, beforeSend: function(){ frmElement.find('form').css("opacity", "0.5"); }, success:function(resp){ frmElement.find('.statusMsg').html(resp.msg); if(resp.status == 1){ if(type == 'add'){ frmElement.find('form')[0].reset(); } getUsers(); } frmElement.find('form').css("opacity", ""); } }); } // Fill the user's data in the edit form function editUser(id){ $.ajax({ type: 'POST', url: 'userAction.php', dataType: 'JSON', data: 'action_type=data&id='+id, success:function(data){ $('#id').val(data.id); $('#name').val(data.name); $('#email').val(data.email); $('#phone').val(data.phone); } }); } // Actions on modal show and hidden events $(function(){ $('#modalUserAddEdit').on('show.bs.modal', function(e){ var type = $(e.relatedTarget).attr('data-type'); var userFunc = "userAction('add');"; if(type == 'edit'){ userFunc = "userAction('edit');"; var rowId = $(e.relatedTarget).attr('rowID'); editUser(rowId); } $('#userSubmit').attr("onclick", userFunc); }); $('#modalUserAddEdit').on('hidden.bs.modal', function(){ $('#userSubmit').attr("onclick", ""); $(this).find('form')[0].reset(); $(this).find('.statusMsg').html(''); }); }); </script>
PHP & HTML Code:
The following HTML is used to build the data list table and form dialog.
delete
and row ID params to remove the record from the database.<?php // Include and initialize DB class require_once 'DB.class.php'; $db = new DB(); // Fetch the users data $users = $db->getRows('users'); ?> <div class="container"> <div class="row"> <div class="col-md-12 head"> <h5>Users</h5> <!-- Add link --> <div class="float-right"> <a href="javascript:void(0);" class="btn btn-success" data-type="add" data-toggle="modal" data-target="#modalUserAddEdit"><i class="plus"></i> New User</a> </div> </div> <div class="statusMsg"></div> <!-- List the users --> <table class="table table-striped table-bordered"> <thead class="thead-dark"> <tr> <th>ID</th> <th>Name</th> <th>Email</th> <th>Phone</th> <th>Action</th> </tr> </thead> <tbody id="userData"> <?php if(!empty($users)){ foreach($users as $row){ ?> <tr> <td><?php echo '#'.$row['id']; ?></td> <td><?php echo $row['name']; ?></td> <td><?php echo $row['email']; ?></td> <td><?php echo $row['phone']; ?></td> <td> <a href="javascript:void(0);" class="btn btn-warning" rowID="<?php echo $row['id']; ?>" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a> <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm('Are you sure to delete data?')?userAction('delete', '<?php echo $row['id']; ?>'):false;">delete</a> </td> </tr> <?php } }else{ ?> <tr><td colspan="5">No user(s) found...</td></tr> <?php } ?> </tbody> </table> </div> </div> <!-- Modal Add and Edit Form --> <div class="modal fade" id="modalUserAddEdit" role="dialog"> <div class="modal-dialog"> <div class="modal-content"> <!-- Modal Header --> <div class="modal-header"> <h4 class="modal-title">Add New User</h4> <button type="button" class="close" data-dismiss="modal">×</button> </div> <!-- Modal Body --> <div class="modal-body"> <div class="statusMsg"></div> <form role="form"> <div class="form-group"> <label for="name">Name</label> <input type="text" class="form-control" name="name" id="name" placeholder="Enter your name"> </div> <div class="form-group"> <label for="email">Email</label> <input type="email" class="form-control" name="email" id="email" placeholder="Enter your email"> </div> <div class="form-group"> <label for="phone">Phone</label> <input type="text" class="form-control" name="phone" id="phone" placeholder="Enter phone no"> </div> <input type="hidden" class="form-control" name="id" id="id"/> </form> </div> <!-- Modal Footer --> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Close</button> <button type="button" class="btn btn-success" id="userSubmit">SUBMIT</button> </div> </div> </div> </div>
This file handles the CRUD requests coming from the Ajax request of the view file (index.php
) and returns the respective requested data. Here the code is executed based on the action_type
parameter. The action_type can be five types, data, view, add, edit, and delete. The following operations will happen based on the action_type request.
data
– Fetch a single record based on the id from the database using getRows() function of DB class. Returns the user data as JSON format.view
– Fetch all records from the database and returns the users data as HTML format.add
– Retrieve form fields data using $_POST in PHP, validate the input values, and insert the data in the database. Returns the response as JSON format.edit
– Retrieve form fields data using $_POST in PHP, validate the input values, and update the data in the database. Returns the response as JSON format.delete
– Deletes the record from the database based on the ID and returns the status as JSON format.<?php // Include and initialize DB class require_once 'DB.class.php'; $db = new DB(); // Database table name $tblName = 'users'; // If the form is submitted if(!empty($_POST['action_type'])){ if($_POST['action_type'] == 'data'){ // Fetch data based on row ID $conditions['where'] = array('id' => $_POST['id']); $conditions['return_type'] = 'single'; $user = $db->getRows($tblName, $conditions); // Return data as JSON format echo json_encode($user); }elseif($_POST['action_type'] == 'view'){ // Fetch all records $users = $db->getRows($tblName); // Render data as HTML format if(!empty($users)){ foreach($users as $row){ echo '<tr>'; echo '<td>#'.$row['id'].'</td>'; echo '<td>'.$row['name'].'</td>'; echo '<td>'.$row['email'].'</td>'; echo '<td>'.$row['phone'].'</td>'; echo '<td><a href="javascript:void(0);" class="btn btn-warning" rowID="'.$row['id'].'" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a> <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm(\'Are you sure to delete data?\')?userAction(\'delete\', \''.$row['id'].'\'):false;">delete</a></td>'; echo '</tr>'; } }else{ echo '<tr><td colspan="5">No user(s) found...</td></tr>'; } }elseif($_POST['action_type'] == 'add'){ $msg = ''; $status = $verr = 0; // Get user's input $name = $_POST['name']; $email = $_POST['email']; $phone = $_POST['phone']; // Validate form fields if(empty($name)){ $verr = 1; $msg .= 'Please enter your name.<br/>'; } if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){ $verr = 1; $msg .= 'Please enter a valid email.<br/>'; } if(empty($phone)){ $verr = 1; $msg .= 'Please enter your phone no.<br/>'; } if($verr == 0){ // Insert data in the database $userData = array( 'name' => $name, 'email' => $email, 'phone' => $phone ); $insert = $db->insert($tblName, $userData); if($insert){ $status = 1; $msg .= 'User data has been inserted successfully.'; }else{ $msg .= 'Some problem occurred, please try again.'; } } // Return response as JSON format $alertType = ($status == 1)?'alert-success':'alert-danger'; $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>'; $response = array( 'status' => $status, 'msg' => $statusMsg ); echo json_encode($response); }elseif($_POST['action_type'] == 'edit'){ $msg = ''; $status = $verr = 0; if(!empty($_POST['id'])){ // Get user's input $name = $_POST['name']; $email = $_POST['email']; $phone = $_POST['phone']; // Validate form fields if(empty($name)){ $verr = 1; $msg .= 'Please enter your name.<br/>'; } if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){ $verr = 1; $msg .= 'Please enter a valid email.<br/>'; } if(empty($phone)){ $verr = 1; $msg .= 'Please enter your phone no.<br/>'; } if($verr == 0){ // Update data in the database $userData = array( 'name' => $name, 'email' => $email, 'phone' => $phone ); $condition = array('id' => $_POST['id']); $update = $db->update($tblName, $userData, $condition); if($update){ $status = 1; $msg .= 'User data has been updated successfully.'; }else{ $msg .= 'Some problem occurred, please try again.'; } } }else{ $msg .= 'Some problem occurred, please try again.'; } // Return response as JSON format $alertType = ($status == 1)?'alert-success':'alert-danger'; $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>'; $response = array( 'status' => $status, 'msg' => $statusMsg ); echo json_encode($response); }elseif($_POST['action_type'] == 'delete'){ $msg = ''; $status = 0; if(!empty($_POST['id'])){ // Delate data from the database $condition = array('id' => $_POST['id']); $delete = $db->delete($tblName, $condition); if($delete){ $status = 1; $msg .= 'User data has been deleted successfully.'; }else{ $msg .= 'Some problem occurred, please try again.'; } }else{ $msg .= 'Some problem occurred, please try again.'; } // Return response as JSON format $alertType = ($status == 1)?'alert-success':'alert-danger'; $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>'; $response = array( 'status' => $status, 'msg' => $statusMsg ); echo json_encode($response); } } exit; ?>
PHP CRUD Operations with Search and Pagination
PHP CRUD with Ajax is very useful to make the data management user-friendly. The user doesn’t need to navigate the pages to add, edit, and update the records in the database. The data can be manipulated in the database without page reload using jQuery, Ajax, PHP, and MySQL. In the example script, we have tried to make PHP CRUD operations more simple and user-friendly. You can easily extend our PHP CRUD script functionality as per your needs.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
hi how can I add a search field?
I liked the coding standards and implementation. It would have been a great help if you had a video explaining building the code from scratch.
Hi, Im trying to deploy the script with .htaaccess enabled for hiding .php extension and it doest seem to work
See this tutorial and follow the steps – https://www.codexworld.com/how-to/remove-index-php-from-url-codeigniter-htaccess/
hello
thanks for your script
how to set up auto increase input in edit form so everytime i did an edit, i can count how much that row has been edited?
thanks
very good
Hello. Do your script have search and pagination or all records show on one page? An if it more than 1000?
This tutorial demonstrates the CRUD operations. For pagination and search, see this tutorial – https://www.codexworld.com/ajax-pagination-with-search-filter-php/
That’s fantastic – thank you so much! Easy to understand, and worked out of the box. One question though: how would I go about adding a radio button field (in my case for “gender”)? Any advice would be highly appreciated! Warm regards, Helmar
How to design taxi management panel in php
Hello thanks you for your script
How to use limit and ORDER BY on the same query?
Use like the below.
I just play around with your php_crud_jquery_ajax_mysql script. Its great 🙂 I wanna integrate it with my html template which uses bootstrap 4. this does not work. do you have plans to have also a bootstrap 4 version for your template?
many thanks in advance
— markus
nice code its useful..
$conditions[‘where’] = array(
’email’ => email_1,
’email’ => email_2
You have written the wrong code. In your case, the code will be like the following.
Please can you help me with multiple conditions? I want to use where $condition and $condition
You can specify multiple conditions in $conditions[‘where’] as an array.
This is a great script. Is there a was to use instead of . Can figure out how to populate it.
Can you please help in adding the WHERE clause (WHERE status = 0) to this statement:
$users = $db->getRows(‘users’,array(‘order_by’=>’id DESC’));
Thanks
It’s very easy to add WHERE clause to this statement, use the following example code.
Thank you so Much !!
nice thanks alot
can you add checkbox status column and update the code please
For example
$conditions[‘where’] = array(‘logtime’=> $_POST[‘fromdate’],
‘logtime’=> $_POST[‘todate’]);
How can i search from date and todate
Please help.
You have to specify an additional
where_between
condition ingetRows()
function which will add a BETWEEN SQL in the query.Dear sir
Thanks a lot for your good job
I need to query on different database on the same serveur
Can you help me to find a simple way to select what database to query in DP.php
Thanks
Jean-Marc
I want use BETWEEN Operator in where condition but i am not getting result.Please help
this is an excellent tutorial. i do have one question I have added a status field to the database to mark the user active or inactive but i cant figure out how to check the checkbox from the database when the user is active and how to change the active field in the database from the checkbox on update or insert. thank you for the help
Excellent blogger. I have tried from another blogger’s code which does not work properly. This code is working fine into my project. thanks.
Hi,
I’ve downloaded the php crud code and i have followed every step from the tutorial but unfortunatly i have a problem with it.
When I try to add a user i get this message “Some problem occurred, please try again.” but when i reload the page the user is actually added so that’s kinda strange. How can is solve this problem?
@Fren Please send your source code at contact@codexworld.com. We’ll check and let you know the issue.
Hi, I have the same question with Jerzy. How can I add more conditions in $condition like WHERE somehing=this AND something=that ??? .. I really don’t know MySQLi 🙁
Many thanks for the Tutorial 😀
You can easily add multiple WHERE conditions, pass the columns and values as an array in
$conditions['where']
.Good Tutorial! Learning a Lot! Keep it up…
Hi, thank you for your very impressive and helpful tutorial.. I have another request, can you please add searchform and pagination to this project? Kindly send me the source code for the additional function of this project to my email.. You’re help will be much more appreciated. 🙂 Thank you!
One Of The Best Tutorial
Great Work
Hello, great examples, but how add more conditions in $condition like WHERE somehing=this ???
best regards
Can you add filter by categories and also add PAGINATION to this tutorial? Thanks!
hi, thanks for the tut, but download link is not working on dropbox, please fix it …
@Gautam We’ve checked the download link again and it is working properly, please try again. If you still facing this issue, contact our support team at support@codexworld.com.