CRUD example in PHP jQuery and Ajax

We received a lot of tutorial requests from users and most often they (mostly new developer) ask for simple CRUD operation (create, read, update and delete) in PHP, jQuery and Ajax. And I think it’s a good suggestion as it will certainly clear some basic concepts as well. So in this post we will share an example of simple CRUD operation that you can easily understand and integrate into your projects. We have also added some cool animation effects to it. What we’ll build is kind of address book where user can enter his basic details like name, email etc. However the whole purpose of this post is to learn CRUD in PHP jQuery and Ajax. So in this demo, user can add his contact details (Create) then he may update/change that detail later on (Update) or he can simply delete the details (Delete). Of course details of all users can be seen/viewed (Read). So it completes all the operations that we we’ll be see in the demo.

crud-infotuts

                                            Live Demo

                     

So lets see how you can set up this easy demo on your server, First create a table named crudtable in your database. Below is the SQL query for crudtable:


CREATE TABLE IF NOT EXISTS <code>crudtable</code> (
 <code>id</code> int(11) NOT NULL AUTO_INCREMENT,
 <code>firstname</code> varchar(30) NOT NULL,
 <code>lastname</code> varchar(30) NOT NULL,
 <code>email</code> varchar(30) NOT NULL,
 <code>favjob</code> varchar(30) NOT NULL,
 PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Now edit db.php file with your DB name and other credentials. Also you can set the value of $limit variable in db.php to set how many rows must be displayed in one page and after that pagination will work.

Now we have simple index.php with basic table/form structure. That you will see on main page. Now we have to core files which are responsible for all the operations, little bit of validation and animation effect.

DBManipute.php file is responsible for  CRUD operation and pagination logic, you will notice functions to save data, update data, delete data and show data in dbManipute.php file, below is our code including all those functions (skipping the code for pagination that you will get in demo download).

Code for dbManipute.php:


<?php
include('db.php');

if(isset($_REQUEST['actionfunction']) && $_REQUEST['actionfunction']!=''){
$actionfunction = $_REQUEST['actionfunction'];

 call_user_func($actionfunction,$_REQUEST,$con,$limit,$adjacent);
}
function saveData($data,$con){

 $fname = $data['firstname'];
 $lname = $data['lastname'];
 $email = $data['email'];
 $favjob = $data['favjob'];
 $sql = "insert into crudtable(firstname,lastname,email,favjob) values('$fname','$lname','$email','$favjob')";
 if($con->query($sql)){
 echo "added";
 }
 else{
 echo "error";
 }

}
function editUser($data,$con){
 $userid = $data['user'];
 $userid = base64_decode($userid);
 $sql = "select * from crudtable where id=$userid";
 $user = $con->query($sql);
 if($user->num_rows>0){
 $user = $user->fetch_array(MYSQLI_ASSOC);
 ?>
 <form name='signup' id='signup'>
 <div class='row'>
 <p><label for='username'>First name</label>
 <input type='text' name='firstname' id='firstname' value='<?php echo $user['firstname']?>' placeholder='Enter First name' /></p>
 </div>
 <div class='row'>
 <p><label for='lastname'>Last name</label>
 <input type='text' name='lastname' id='lastname' value='<?php echo $user['lastname']?>' placeholder='Enter Last name' /></p>
 </div>
 <div class='row'>
 <p><label for='email'>Email</label>
 <input type='text' name='email' id='email' value='<?php echo $user['email']?>' placeholder='Enter Email' /></p>
 </div>
 <div class='row'>
 <p><label for='favjob'>Favourite Job</label>
 <input type='text' name='favjob' id='favjob' value='<?php echo $user['favjob']?>' placeholder='Enter Favorite Job' /></p>

 </div>
 <input type="hidden" name="actionfunction" value="updateData" />
 <input type="hidden" name="user" value="<?php echo base64_encode($user['id']) ?>" />
 <div class='row'>
 <input type='button' id='updatesubmit' class='submit' value='Update' />

 </div>
 </form>
 <?php }
}
function showData($data,$con,$limit,$adjacent){
 $page = $data['page'];
 if($page==1){
 $start = 0;
 }
 else{
 $start = ($page-1)*$limit;
 }

 $sql = "select * from crudtable order by id asc";
 $rows = $con->query($sql);
 echo $rows = $rows->num_rows;

 $sql = "select * from crudtable order by id asc limit $start,$limit";

 $data = $con->query($sql);
 $str='<tr class="head"><td>Firstname</td><td>Lastname</td><td>Email</td><td>Favourite Job</td><td></td></tr>';
 if($data->num_rows>0){
 while( $row = $data->fetch_array(MYSQLI_ASSOC)){
 $str.="<tr id='".$row['id']."'><td>".$row['firstname']."</td><td>".$row['lastname']."</td><td>".$row['email']."</td><td>".$row['favjob']."</td><td><input type='button' class='ajaxedit' value='Edit' user='".base64_encode($row['id'])."' /> <input type='button' class='ajaxdelete' value='Delete' user='".base64_encode($row['id'])."' ></td></tr>";
 }
 }else{
 $str .= "<td colspan='5'>No Data Available</td>";
 }
$str = $str."<tr><td colspan='5'>".pagination($limit,$adjacent,$rows,$page)."</tr></tr>";
echo $str;
}
function updateData($data,$con){
 $fname = $data['firstname'];
 $lname = $data['lastname'];
 $favjob = $data['favjob'];
 $email = $data['email'];
 $user = $data['user'];
 $user = base64_decode($user);
 $sql = "update crudtable set firstname='$fname',lastname='$lname',email='$email',favjob='$favjob' where id=$user";
 if($con->query($sql)){
 echo "updated";
 }
 else{
 echo "error";
 }
 }
 function deleteUser($data,$con,$limit,$adjacent){
 $user = $data['user'];
 $user = base64_decode($user);
 $sql = "delete from crudtable where id=$user";
 if($con->query($sql)){
 showData($data,$con,$limit,$adjacent);
 }
 else{
 echo "error";
 }
 }

?>

Now another main file is js/javascript file named script.js. Script file does all the validation and animation stuffs (utilizing the power of jQuery easing plugin for cool effects). Its a long script so I am not pasting it here but you can always grab the demo files and test it on your own. I hope you guys will love this script and our efforts to create this for our developer friends. Let us know if you want any improvement in this script or you have any queries for us regarding this post.

Also please share the tutorial with your friends. We would love to be friends with you on Gplus, FB and Twitter :)

About sanjeev

sanjeev
Dreamer, Blogger and Thinker. I love to help people in solving their problems. You can also join me HERE

Related posts:

14 Responses so far.

  1. Hi,
    I was trying to display “All users” tab, on document-ready, not “Add user”.
    I with adding –
    jQuery(“#show_user_sec”).show();
    getusers();

    and removing –
    jQuery(‘#add_user_sec’).show();

    from the top of document ready script.
    it does show the All users page, but now when i click “Add user”, it does not do anything unless i first click “all users” (which hides ‘all users list’) then click on “add user”, then only it displays the “add user” tab/div.

    please help me, how to make it work?
    thanks.
    regards,

  2. Hi Atul,

    Do these things..

    1. add ‘jQuery(“#show_user_sec”).show();’ instead of ‘jQuery(‘#add_user_sec’).show();’. you have done this.

    2. put “add_user” click function above of “show_user” click function

    3. add var ‘section = jQuery(“.user_section”);’ after ‘animation=true’ under add_user click function.

    This wil work. if this will not work let me know and send your script to my email id.

    1. Hi Jayant,

      Thank you very much for your reply. It really worked. Just to note that –

      – I had to add getusers(); after jQuery(“#show_user_sec”).show(); in you point 1, as I had mentioned it my previous comment too.

      – And for better/correct visual appearance, remove the active class from Add User’s li and add it to All Users’.

      Lastly I have a doubt –
      – As you mentioned in your 2nd point, “put “add_user” click function above of “show_user” click function”. My question is – Why was it placed INSIDE click function of “Show_user” click function, in the first place?

      e.g. I kept it outside, alongwith changes you mentioned. It works. Then I simply added “Query(‘#add_user_sec’).show();” on the top, instead of “#show_user_sec”. And it still works like it should. The new case displays Add user on page load, navigates to all users on respective single click. Then shouldn’t “Add user” click function be outside.

      I hope I am clear to you.
      Thanks.
      Regards,

  3. Could you please update your script? If you click Add User when the form for adding a user is showing, it hides & disables the section for adding a new user. The same thing happens when you have all users listed and click All Users.

    Thanks

  4. how to add multiple images in database..like each image store individually in database not the last image only.
    plzz help …

  5. Thank you for your contribution .
    As I can put a confirmation when deleting a user?
    can you help me

  6. hello sir kindly post php price range slider with mysql database with dynamicaly change value by slider.

    thanx

  7. Hello good morning

    I changed the lines you comment on the day November 7, 2014 at 4:58 am
    But it does not work for me, can you please see what I have wrong?

    jQuery(document).ready(function(){
    jQuery(‘.user_section’).hide();
    jQuery(‘#show_user_sec’).show();

    var left, opacity, scale;
    var animating;
    jQuery(“#add_user”).click(function(event){
    var section = jQuery(“.user_section”);
    jQuery(document).prop(‘title’, ‘Show Users’);
    jQuery(“#msg”).html(“”);

    if(animating) return false;
    animating = true;
    var section = jQuery(“.user_section”);
    var activesec;
    for($i=0;$i<section.length;$i++){
    if(jQuery(section[$i]).css('display')=="block"){
    activesec = section[$i];
    break;
    }
    }
    if(jQuery(this).hasClass("active")){
    return false;
    }
    jQuery(".navi_sec").removeClass("active");
    jQuery(this).parent().addClass("active");

    //show the next fieldset

Comments are closed.