Create Simple CRUD with Datatables, Jquery and AdminLTE

Seegatesite.com – Make simple CRUD web application using Datatables, Jquery and adminLTE is fast and efficient measures. I will share how utilizing datatables plugin to display data from the server to the browser using AJAX in order to look more interactive table. In addition Datatables plugin, jquery and adminlte use to make the system more attractive and responsive. Let’s start the following tutorial.

Tutorial Create Simple CRUD with Datatables, Jquery and AdminLTE

Requirement :

  • AdminLTE Theme. Download here
  • Plugin Datatables (Already contained in adminlte).
  • Jquery (Already contained in adminlte).
  • Plugin SweetAlert (To beautify the javascript alert, please download here).
  • Plugin Bootstrap-Notify (To beautify the notification, please download here).
  1. Create MYSQL database with names “crud”Create New Database As Crud Create Simple Crud With Datatables Jquery And Adminlte
  2. Add the customer table in the database crudCreate New Table Customer Create Simple Crud With Datatables Jquery And Adminlte Revisi
  3. Add the following query as the initial data customer table.

  4. Extracts adminlte.zip that you downloaded earlier on your public folder (if using xampp, at the htdocs folder inside / ubuntu in the folder /var/www/html/) and rename the folder adminlte be crud.
  5. Copy plugin sweetalert (sweetalert.css and sweetalert.min.js) into the folder crud/plugins/sweetalert/
    Copy Sweetalert Plugin To Plugins Folder Create Simple Crud With Datatables Jquery And Adminlte
  6. Copy plugin bootstrap-notify (bootstrap-notify.min.js) into the folder /crud/plugin/bootstrap-notify/Copy Bootstrap Notify Plugin To Plugins Folder Create Simple Crud With Datatables Jquery And Adminlte
  7. Create a new folder with the name “application” in the crud folder, and then create a folder inside “application” folder with the name “customer“.
  8. Get blank.html file (/crud/pages/examples/blank.html), copy to folder /crud/application/customer/ and rename it to index.php
  9. Edit index.php, then add the css and js files below

  10. Edit section class="content" in index.php and paste the following script

  11. Create 4 php file in the customer folderdata.php : This file is used to display customer data using ajax on datatables.copy the following code on data.php

    get_cust.php : This file is used to retrieve customer data with a specific id when btnedit button on click. copy the following code in the file get_cust.php

    save.php : This file is used to store and update customer table in the database. copy the following code in the file save.php

    delete.php : This file is used to delete the customer data in the database.copy the following code on delete.php

  12. Create a javascript file with the name customer.js and put in the same folder as index.php and copy the following script on customer.js

    Don’t forget to call the customer.js script by putting script at the end of the line on index.php

A short explanation :

$('#table_cust').DataTable({ ................}); - Used to display customer data in the data tables using ajax when document is already.

$(document).on("click","#btnadd",function(){ .. }) - When a add customer button is pressed, the jquery will run the script

$(document).on( "click",".btnhapus", function() { .. }) - When the button with “.btnhapus” class clicked, jquery will run the script above to delete customer data

$(document).on("click","#btnsave",function(){ .. }) - When the button with id “btnsave” clicked , jquery will run a script to store / update customer data

– To reload or refresh datatables’s data, we need to reload the datatables with script below

13. Don’t forget to create config.php on the application folder

Done, simple CRUD with datatables, jquery and adminlte been completed. Please test your project in the browser with the address http://localhost/crud/application/customer. If successful, the display on your browser will be as follows

Create Simple CRUD With Datatables Jquery Adminlte For Beginners

If you need a complete file please download the link below

My other article : Showing Mysql Data To Html Tables With Many Options with Jquery And AdminLTE

Thus article about How to create simple CRUD with Datatables ,Jquery and AdminLTE, hope useful

The following two tabs change content below.
This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

81 Comments

 Add your comment
  1. Hi! Your link has been disable by google url shortener. Please fix. Tolong perbaiki link nya 🙂
    Cheers

  2. Thank you so much, You are great!

  3. can i download the source of this project? the tutorial is so awesome. thanks for the share.

  4. thanks for the tutorial. the download has password… i am referring to the crud.zip

  5. awesome…thanks

  6. Terima Kasih atas tutorialnya, tapi kok ada error muncul ya “DataTables warning: table id=table_cust – Invalid JASON response”. ini error kenapa ya?

  7. Mau nanya om, untuk nampilin format tanggal di datatable bagaimana ya caranya? dalam DB saya format tanggalnya yyyy-mm-dd, tapi yang muncul dalam datatables [object][object]

    • Hello angga,
      Anda menampilkan pake ajax ? pada data php yang dipanggil untuk menampilkan tabel ditambahkan date(‘d/m/Y’,strtotime(table[‘field_tanggal’])) seperti contoh berikut
      [php]
      $i=0;
      foreach($table as $key)
      {
      $table[i][‘field_tanggal’] = date(‘d/m/Y’,strtotime($key[‘field_tanggal’]));
      $i++;
      }
      [/php]

      Semoga membantu, atau kalo boleh saya lihat script anda.. 🙂

      • dear Om Sigit..terima kasih untuk bantuannya sepertinya masih ada error kalo pake script om di atas. muncul “Warning: strtotime() expects parameter 1 to be string, object given in ..\data.php on line 11”
        Berikut script saya.
        [php]
        include "../config.php";
        $query="SELECT CONVERT(VARCHAR(10),dateFrom,103) as dateFromCon,* FROM(SELECT ROW_NUMBER() OVER (ORDER BY Id) AS urutan, * FROM HULL_WORKSHEET) AS EMP";
        $hasil = sqlsrv_query($conn,$query);
        $data = array();
        while($r = sqlsrv_fetch_array($hasil)) {
        $data[] = $r;
        }
        $i=0;
        foreach ($data as $key) {
        $data[$i][‘dateFrom’] = date(‘d/m/Y’,strtotime($key[‘dateFrom’]));
        // add new button
        $data[$i][‘button’] = ‘<i></i><i></i>’;
        $i++;
        }
        $datax = array(‘data’ =&gt; $data);
        echo json_encode($datax);
        [/php]
        Akhirnya saya akalin convert date nya di query sql nya pakai field dateFromCon

        • Tetapi saya masih ragu dengan menggunakan convcert di query untuk kedepannya, apakah sudah benar convert di query apa ada cara lain? Terima kasih sebelumnya

          • Helo mas angga,
            Maaf saya masih bingung, untuk apa menggunakan convert ? apakah anda menggunakan sqlserver ? Jika Mysql, format date akan seperti ini “YYYY-mm-dd”, sehingga waktu di parsing ke php akan menjadi string, contohnya ketika saya memiliki field tgl_transaksi, ketika saya cek menggunakan var_dump() hasilnya seperti ini ‘ string(10) “2016-08-12” ‘. dari hasil tersebut agar menjadi format “d/m/Y” tinggal diubah menggunakan
            [php]
            foreach ($data as $key) {
            $data[$i][‘dateFromCon’] = date(‘d/m/Y’,strtotime($key[‘dateFromCon’]));
            // add new button
            $data[$i][‘button’] = ‘<i></i><i></i>’;
            $i++;
            }
            $datax = array(‘data’ => $data);
            echo json_encode($datax);
            [/php]

            Di query anda menggunakan nama dateFromCon bukan dateFrom

            CMIIW, semoga membantu

  8. Dear Om Sigit, melanjutkan pertanyaan sebelumnya, iya om saya pakai MSSQL Server. kalau saya pakai

    $data[$i][‘dateFromCon’] = date(‘d/m/Y’,strtotime($key[‘dateFromCon’]));

    muncul error “Warning: strtotime() expects parameter 1 to be string, object given in ..\data.php on line 11”

    field dateFrom ada di dalam table HULL_WORKSHEET, karena ada error makanya saya convert saja di querynya, mucul lah field baru dateFromCon.

    Maaf ni saya banyak tanya 🙂

    • hello mas angga,
      bisa coba di cek dulu var_dump($key[‘dateFromCon’]) ? hasilnya apa 🙂

      • var_dump($key[‘dateFromCon’]) => string(10) “08/08/2016” (field dateFromCon sudah saya convert dahulu di query nya)
        [php]
        var_dump($key[‘dateFrom’]) =&gt;
        object(DateTime)#63 (3) {
        ["date"]=&gt;
        string(26) "2016-08-08 00:00:00.000000"
        ["timezone_type"]=&gt;
        int(3)
        ["timezone"]=&gt;
        string(13) "Europe/Berlin"
        }
        [/php]
        Yang saya inginkan tidak usah convert di query, yang artinya field dateFromCon tidak dipakai, hanya menggunakan field dateFrom saja.
        Sebenarnya dengan menggunakan field dateFromCon di datatables tanggal sudah muncul dengan benar tanpa menggunakan $data[$i][‘dateFromCon’] = date(‘d/m/Y’,strtotime($key[‘dateFromCon’]));

  9. how to download the source program
    thanks

  10. Friend i have this error, I NEED HELP PLEASE!!!

    Fatal error: Uncaught Error: Call to undefined function mysql_connect() in C:\xampp\htdocs\crud\application\config.php:7 Stack trace: #0 C:\xampp\htdocs\crud\application\customer\data.php(2): include() #1 {main} thrown in C:\xampp\htdocs\crud\application\config.php on line 7

    HELP PLEASE, and! I FOLLOW YOU!

  11. Ohh yes! use php 7 and now I am installing php5, a solution for use in php7 should I do? …
    Thanks in advance testing in php5!

  12. I need to select individual records by filtering the results in the data.php with a regular WHERE clause like WHERE name=”John”. How do I achieve that with your code in MySQL statement in data.php? I need your help, please,

  13. Hi, really great article ! Thank’s for sharing. I need a help please… when the column of mysql is very lange and contains strange character like ‘ or \n or \r ….. the datatables respond with this error message “Datatables warning Invalid JSON response”. … please, there is a method or hint to solve ? Thank’s very much !

  14. I downloaded your code and tried to execute. I am able to save the data. But the data.php doesn’t seem to load any data. I see no errors as well. Any suggestions would be helpful.

  15. Tq Mas Sigit… Barokah ilmunya njenengan… Sehat Selalu buat mas Brow dan pasti lancar rejekinya.. Aamiin.
    Salam Kenal, Ony.

  16. Hello la connection mysql n’est plus à jour.
    Pouvez vous refaire une connection avec mysqli?

    Thank you

  17. Hi why when i create a new customer or edit one customer after click Save button the form does not closes automatically

    • Every programmer has his own style in making applications. If you need to closes form automatically, add this code below in the save button after success save the data
      [php]

      $("#modalcust").modal("hide");

      [/php]

      I hope answer your questions 🙂

  18. hi i am trying to download the zip document, and successfully downloaded the material but i am not able to extract that its asking some password to extract so please let me know the procedure to extract.

    thank you

  19. hi..saya ada dapat error ini..
    Warning: mysqli_query() expects parameter 1 to be mysqli, object given in C:\xampp\htdocs\sriidu2\application\student\data.php on line 5

    Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\sriidu2\application\student\data.php on line 7
    {“data”:[]}

  20. The 2 buttons, edit and delete are appearing one below other. What could be the issue? Is it that I missed a css?

  21. Hello, thank you for the great tutorial, I want to know if it is possible to integrate your source code into my Java Enterprise Edition Application ( with JSF), my initial problem was how to have dynamic data into AdminLTE table.

  22. How if the notification similar to facebook notification? Can you make tutorial about it? If us insert or delete data from table, the notification will be showed in the ‘bell’ icon (AdminLTE). Thanks! 🙂

    • halo mas Bayu,
      untuk membuat notifikasi seperti itu intinya sama dengan artikel ini.
      1. Ketika anda tambah atau hapus data, anda perlu menambahkan 1 field flag agar dapat dicek sistem apakah notifikasi sudah di broadcast atau belum.
      2. Anda dapat melakukan pengecekan flag delete atau tambah data dengan ajax jquery yang diset interval tiap 1 menit atau 2 menit. atau ketika halaman diload.

      Maaf saya belum sempat membuatkan tutorialnya karena ada project diluar kota. akan saya bikinkan tutorialnya secepat mungkin jika telah kembali ke kota asal saya 🙂

      Terimakasih telah berkunjung

  23. DataTables warning: table id=table_cust – Requested unknown parameter ‘country’ for row 0. For more information about this error, please see http : //datatables.net/tn/4

    ini kenapa ya mas ?

    • Maaf mas ada kekeliruan di pembuatan struktur table customer, bukan field borndate tetapi country. Artikel sudah saya perbaiki pada bagian pembuatan tabel dan insert tabel. Terimakasih 🙂

      [php]
      [php]
      INSERT INTO customer(NAME,gender,country,phone) VALUES(‘Sigit Prasetya N’,’Male’,’Indonesia’,’000-857489′);
      INSERT INTO customer(NAME,gender,country,phone) VALUES(‘Taylor Swift’,’Female’,’British’,’0858774858′);
      INSERT INTO customer(NAME,gender,country,phone) VALUES(‘Kristen Stewart’,’Female’,’United State’,’888-985859′);
      [/php]
      [/php]

  24. 1. DataTables warning: table id=tablea – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    2. Ada error seperti diatas, data masuk ke database, tapi tidak tampil ke dalam tabel.

    Mohon pencerahannya mas

  25. Mas, bagaimana caranya saat menambah item baru, waktu menekan tombol save, modalnya hilang.

    • di javascript nya di di btnsavenya di ubah seperti berikut
      [php]
      $(document).on("click","#btnsave",function(){
      var id_cust = $("#txtid").val();
      var name = $("#txtname").val();
      var gender = $("#cbogender").val();
      var country = $("#txtcountry").val();
      var phone = $("#txtphone").val();
      var crud=$("#crudmethod").val();
      if(name == ” || name == null ){
      swal("Warning","Please fill customer name","warning");
      $("#txtname").focus();
      return;
      }
      var value = {
      id_cust: id_cust,
      name: name,
      gender:gender,
      country:country,
      phone:phone,
      crud:crud
      };
      $.ajax(
      {
      url : "save.php",
      type: "POST",
      data : value,
      success: function(data, textStatus, jqXHR)
      {
      var data = jQuery.parseJSON(data);
      if(data.crud == ‘N’){
      if(data.result == 1){
      $.notify(‘Successfull save data’);
      var table = $(‘#table_cust’).DataTable();
      table.ajax.reload( null, false );
      $("#txtname").focus();
      $("#txtname").val("");
      $("#txtcountry").val("");
      $("#txtphone").val("");
      $("#crudmethod").val("N");
      $("#txtid").val("0");
      $("#txtnama").focus();
      }else{
      swal("Error","Can’t save customer data, error : "+data.error,"error");
      }
      }else if(data.crud == ‘E’){
      if(data.result == 1){
      $.notify(‘Successfull update data’);
      var table = $(‘#table_cust’).DataTable();
      table.ajax.reload( null, false );
      $("#txtname").focus();
      }else{
      swal("Error","Can’t update customer data, error : "+data.error,"error");
      }
      }else{
      swal("Error","invalid order","error");
      }
      $("#modalcust").modal("hide");
      },
      error: function(jqXHR, textStatus, errorThrown)
      {
      swal("Error!", textStatus, "error");
      }
      });
      });

      [/php]

      ada penambahan $("#modalcust").modal("hide");

      Semoga membantu

  26. Mas untuk fungsi edit, muncul notifikasi sukses, tapi data di db tidak terupdate. Mohon pencerahannya mas. 🙂

    • coba di bagian save.php di cek dl mungkin ada yang salah, atau buka f12 pada browser tab bagian network, kemudian coba tekan tombol update. ntar bagian save.php akan muncul, lihat response nya. (Tutorial saya ini hanya projek sederhana, dasar membuat CRUD saja, jadi kemungkinan tidak lengkap, silahkan dikembangkan sendiri ) 🙂

  27. pake php 7 error di baris mysqli_error(), untuk solusinya bagaimana?

Leave a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.