IT Gets Done Easily: jquery php crud

Screenshots

I've search thru the web about CRUD in PHP jquery Ajax using modal form and i can say all the snippets i find is not accurate and helpful so i decided to share my own snippets to you guys.

In this tutorial we will be using Codeigniter Datatables, Jquery, Bootstrap, Xampp for local webserver and a little bit of commonsense.

First of all download all the components to complete this project. I supposed you guys have the knowledge how to get this component and make it run. Follow the link on the above components and you will be alright.

I will not be discussing here how to install and set up codeigniter. You can follow this detailed User Guide from codeIgniter.

Lets Start Coding.

File Structure:

Now go to your Htdocs folder and make a folder called assets. Inside this folder we will make 2 new folder and name it js and css. Here we will paste the bootstrap jquery and datatables components.

assets/js folder

assets/css folder

Creating Database:

Open your localhost phpmyadmin and create a database named project.

Code Igniter Database Settings:

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => '',
 'database' => 'project',
 'dbdriver' => 'mysqli',
 'dbprefix' => '',
 'pconnect' => FALSE,
 'db_debug' => (ENVIRONMENT !== 'production'),
 'cache_on' => FALSE,
 'cachedir' => '',
 'char_set' => 'utf8',
 'dbcollat' => 'utf8_general_ci',
 'swap_pre' => '',
 'encrypt' => FALSE,
 'compress' => FALSE,
 'stricton' => FALSE,
 'failover' => array(),
 'save_queries' => TRUE
);

I'll make it a little simpler lest skip the migration on CodeIgniter. Let's create a table from your database name project.

Table name: products

Column
prod_id = int, auto increment, primary key
prod_name{type: varchar, constraint:100}
prod_cost {type: decimal, constraint:65,2}
prod_stocks {type: INT}
prod_type {type:varchar, constraint:150}
prod_date_add {type: datetime , default: currenttimestamp}

in codeigniter create a model.Here we will create function for all the database interaction. I created a model name Project_model below is the code.

Model Project_model.php

<?php


class Project_model extends CI_Model {
    //Adding updating and delete Data function
    function crud($selector,$table,$data,$col_name=null,$id=null){
          if($selector==1){
          $this->db->insert($table,$data);}
         elseif ($selector==2){
            $this->db->update($table, $data, array($col_name => $id));
        }
        elseif($selector==3){
         $this->db->delete($table, array($col_name => $id));
        }
    }
    
    //pull data from database you can use and query join statement as long as you echo data as json
    function getTable($table){
      
       $this->db->select('*');
       $this->db->from($table);
       $query=$this->db->get();
       $data=$query->result();  
        return $data;
      
    }
    
    
}

Controller MycrudProject.php

<?php

/* 
 * Codeigniter Crud by alex barbacena
 */

class MyCrudProject extends CI_Controller{
    function __construct()
    {           parent::__construct() ; 
           $this->load->helper('url');
           $this->load->model('Project_model');
             }//end construct
        public function index(){
             $this->load->view('project');
        if (isset($_POST['btnAdd'])){
             $id=$this->input->post('ID');
             $switch=$this->input->post('switch');
          $data=[
            'prod_name'=>$this->input->post('Name'),
             'prod_cost'=>$this->input->post('Price'),
              'prod_type'=>$this->input->post('P_type'),
              'prod_stocks'=>$this->input->post('Stocks')];  
          if($switch==1){
                 $this->Project_model->crud(1,'products',$data);}
          elseif($switch==2){
                $this->Project_model->crud(2,'products',$data,'prod_id',$id);
                        }
          elseif ($switch==3){
             $id=$this->input->post('del_id');   
            $this->Project_model->crud(3,'products',null,'prod_id',$id);
          }
            
        }//end btn add
        
    }//end function
    //ajax page  here is where you pull the data from database to display on the table and select options
    // you can use join statment as many as you like  as long as you echo it as jsondata
    function jsondata($id){
             if($id==1){
       $data=$this->Project_model->getTable('products');
       echo json_encode($data);
        }      
         if($id==2){
       $data=$this->Project_model->getTable('type');
       echo json_encode($data);
        }      
        
    }
}//end Class

HTML Views Folder

<html>
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>OnTimeExpress | Dashboard</title>
  <!-- Tell the browser to be responsive to screen width -->
  <meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
  <!-- Bootstrap 3.3.6 -->
  <!-- bootstrap -->
  <link rel="stylesheet" href="<?php echo base_url().'/assets/css/bootstrap.min.css';?>">
 
  <!-- Font Awesome -->
     <link rel="stylesheet" href="<?php echo base_url().'assets/css/jquery.dataTables.css';?>"> 
  <link rel="stylesheet" href="<?php echo base_url().'assets/css/font-awesome.min.css';?>">
   <!-- Theme style -->

  
 
    <script src="<?php echo base_url().'assets/js/jQuery-2.2.0.min.js';?>"></script>
    <script src="<?php echo base_url().'assets/js/bootstrap.min.js';?>"></script>
  <script  src="<?php echo base_url().'assets/js/jquery.dataTables.js';?>"> </script>
  <script type="text/javascript" src="<?php echo base_url().'assets/js/jquery.dataTables.min.js';?>"></script>

   
  <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
  <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
  <!--[if lt IE 9]>
  <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script>
  <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
  <![endif]-->
</head>
<div class="container">
<h1>
 Simple PHP CodeIgniter JQuery Databtables CRUD </h1>
<p class="help-block">
by: Alex Barbacena </p>
<div class="row">
                <div class="col-md-10 col-md-offset-1">
            <div class="pull-right" style="margin-bottom:10px;">
                   <input type="button" class="btn btn-primary btn-sm" value="New Record"  id="add"/>
            </div>
<table class="table" id="project">
        <thead>
<tr><th>ID</th>
            <th>Name</th>
            <th>Price</th>
            <th>P_Type</th>
            <th>Stocks</th>
            <th>Date Inventory</th>
                <th></th> <th></th>
                        </tr>
</thead>
                <tbody>

                </tbody>
            </table>
</div>
</div>
</div>
<!-- end container -->
 <div class="modal fade" id="myform" role="dialog">
    <div class="modal-dialog">
    
      <!-- Modal content-->
        <form  method="post">
      <div class="modal-content">
        <div class="modal-header">
          <button type="button" class="close" data-dismiss="modal">×</button>
          <h4 class="modal-title">
</h4>
</div>
<div class="modal-body">
         <input type="hidden" name="switch" id="switch" />
     <div class="fields">
     </div>
<div id="delete">
</div>
</div>
<div class="modal-footer">
          <input type="submit" class="btn btn-info" id="btn_form" value="Save" name="btnAdd"></form>
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        </div>
</div>
</div>
</div>
<!-- end modal -->
  
  
 
 <script type="text/javascript" src="<?php echo base_url().'assets/js/project.js';?>"></script>

Jquery assets/js/project.js

    $(document).ready(function(){
   var table= $('#project').DataTable({
    "bProcessing": false,
    "bServerSide": false,
    "sAjaxSource": "myCrudproject/jsondata/1",
     "fnServerData": function ( sSource, aoData, fnCallback, oSettings ) {
     oSettings.jqXHR = $.ajax( {
        "type": "POST",
        "url": sSource,
         "dataType": "json",
        "data":aoData,
         "success":function(data){
         table.rows.add(data).draw();
         
        
         }
        });
    },           columns: [
                 { "data": "prod_id" },
                { "data": "prod_name" },
                { "data": "prod_cost",
                "render":function(data){
                    return '$'+' '+data; 
                }},
                { "data": "prod_type" },
                { "data": "prod_stocks", 
                "render": function(data){
                    return data+' '+'Pcs';
                }
                },
                { "data": "prod_date_add",
                "render": function (data) {
                    var date = new Date(data);
                    var datetime = date.getMonth()+'/'+date.getDate()+'/'+ date.getFullYear().toString().substring(2)+'|'+ date.getHours()+':'+ date.getMinutes()+' '+  date.getSeconds();
                      return datetime;
                   
                   }
                },
                { "data":null,
                "render": function (data) {
               return '<a class="edit" href="#"><i class="fa fa-pencil-square fa-2x "></i></a>'}},
        
                {"data":null,
                  "render":function(data){
                 return '<a class="delete" href="#"><i class="fa fa-trash fa-2x" aria-hidden="true"></i></a>';
                }
            }
                                 ]//end column
     
     });//end booked table
      
         var form=$('#myform');
         var title=$('.modal-title');
         var fields=$('.fields');
         var toggle=$('#switch');
         var del=$('#delete');
        var data= [];
            $('#project thead').find('tr').each(function (rowIndex) {
            var cols = [];
             $(this).find('th').each(function (colIndex, c) {
            cols.push(c.textContent);
                 });
                    data.push(cols);
                      });
                         var formfield=data[0];
                         var input='';
                         for(var i=0; i<formfield.length;i++){
                         input +='<div class="form-group" id="'+i+'">
<label for="'+formfield[i]+'">'+formfield[i]+'</label><input type="text" class="form-control" id="'+formfield[i]+'" name="'+formfield[i]+'" placeholder="'+formfield[i]+'" /></div>
';
                        }//end for
                            title.empty();
                            title.append('Add New Product');
                            fields.empty();
                            fields.append(input);
                            fields.find('div:first-child').children('input').attr('type','hidden');
                            fields.find('div:first-child').children('label').hide();
                            fields.find('div:nth-last-child(3)').hide();
                            fields.find('div:nth-last-child(2)').hide();
                            fields.find('div:last-child').hide();
           //select option                 
      $.ajax({
          type:'Post',
          "url": 'myCrudproject/jsondata/2',
         "dataType": "json",
       success:function(data){
        var text=fields.find('div:nth-last-child(5) input');
       var preselect=$('<select id="type" name="P_type" class="form-control"></select>'), options='';
       for(var x=0;x<data.length;x++){
           options +='<option value="'+data[x].type_name+'">'+data[x].type_name+'</option>';
           
       }
       var select= preselect.append(options);
       select.prepend('<option value="" selected>Select</option>');
        text.replaceWith(select);
 
       }
       });                    
         //button add
       $('#add').on('click',function(){
       fields.show();
       toggle.val(1);
       del.hide();
            $('#btn_form').val('New Record');
            var table= $('#project');
             form.modal('toggle');

                    $('#ID').val('');
                  $('#Name').val('');
                  $('#Price').val('');
                  $('#type  option:selected').html('Select Value');
                  $('#type  option:selected').val('Select');

                  $('#Stocks').val('');
                });//end button add
                //edit button
              $('#project tbody').on('click', '.edit',  function(){
                  fields.show();
                  del.hide();
              toggle.val(2);   
           form.modal('toggle');
           title.empty();
           title.append('Edit Products');

           $('#btn_form').val('Update Record');
           var data= [];
                       $(this).closest('tr').each(function (rowIndex) {
                       var cols = [];
                        $(this).find('td').each(function (colIndex, c) {
                       cols.push(c.textContent);
                            });
                               data.push(cols);
                                 });
           $.each(data, function(k,v){
           var pr =v[2].split('$');
           $('#ID').val(v[0]);
           $('#Name').val(v[1]);
           $('#Price').val(pr[1]);
           $('#type  option:selected').html(v[3]);
           $('#type  option:selected').val(v[3]);
           var st =v[4].split('Pcs');
           $('#Stocks').val(st[0]);

           });

           });

            $('#project tbody').on('click', '.delete',  function(){
                     $('#myform').modal('toggle');
                     title.empty();
                     title.append('Delete Record');
                     fields.hide();
                     toggle.val(3);
                     del.show();
                  del.empty();
                var id=$(this).closest('tr').find('td:first').text();
                $('#btn_form').val('Delete');
                del.append('<h3>
Are you sure you want to Delete?</h3>
');
                del.append($('<input type="hidden" name="del_id" value="'+id+'"/>'));

            });



                });// doc ready

For more details download the Project file on this link Comment down below your questions and i'll be happy to answer. Happy coding.

Pages

Monday, September 12, 2016

Simple CRUD using Bootstrap, Datatables,CodeIgniter, Jquery Ajax