Create Simple CRUD with Datatables, Jquery and AdminLTE

By Sigit Prasetya Nugroho · · , 91 Comments

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.
My Twitter profileMy Facebook profileMy Google+ profileMy YouTube channel

Sigit Prasetya Nugroho

This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

91 Comments

 Add your comment
  1. nolbyte

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

    Reply
  2. Uros

    Thank you so much, You are great!

    Reply
  3. andrew

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

    Reply
  4. ahmed

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

    Reply
  5. toto

    awesome…thanks

    Reply
  6. Angga

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

    Reply
  7. Angga

    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]

    Reply
    • Sigit Prasetya Nugroho

      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.. 🙂

      Reply
      • Angga

        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

        Reply
        • Angga

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

          Reply
          • Sigit Prasetya Nugroho

            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. Angga

    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 🙂

    Reply
    • Sigit Prasetya Nugroho

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

      Reply
      • Angga

        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’]));

        Reply
  9. Cris

    how to download the source program
    thanks

    Reply
  10. Delshire

    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!

    Reply
  11. Delshire

    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!

    Reply
  12. Eric

    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,

    Reply
  13. fcafra

    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 !

    Reply
  14. Saurabha

    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.

    Reply
  15. ONY

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

    Reply
  16. bohn

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

    Thank you

    Reply
  17. Alexis Gaitan

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

    Reply
    • Sigit Prasetya Nugroho

      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 🙂

      Reply
  18. altaf

    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

    Reply
  19. aififffff

    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”:[]}

    Reply
  20. Bala

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

    Reply
  21. Yosra

    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.

    Reply
  22. Bayu Kurniawan

    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! 🙂

    Reply
    • Sigit Prasetya Nugroho

      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

      Reply
  23. avocalipso

    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 ?

    Reply
    • Sigit Prasetya Nugroho

      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]

      Reply
  24. Niko

    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

    Reply
    • Sigit Prasetya Nugroho

      Silahkan buka file data.php dan ubah dengan kode berikut :

      [php]
      <?php
      include "../config.php";
      $query=mysql_query("SELECT @rownum := @rownum + 1 AS urutan,t.*
      FROM customer t,
      (SELECT @rownum := 0) r");
      if(mysql_error())
      {
      die(print_r($mysql_error()));

      }
      $data = array();
      while($r = mysql_fetch_assoc($query)) {
      $data[] = $r;
      }
      $i=0;
      foreach ($data as $key) {
      // add new button
      $data[$i][‘button’] = ‘<button type="submit" id_cust="’.$data[$i][‘id_cust’].’" class="btn btn-primary btnedit" ><i class="fa fa-edit"></i></button>
      <button type="submit" id_cust="’.$data[$i][‘id_cust’].’" name_cust="’.$data[$i][‘name’].’" class="btn btn-primary btnhapus" ><i class="fa fa-remove"></i></button>’;
      $i++;
      }
      $datax = array(‘data’ => $data);
      echo json_encode($datax);
      ?>
      [/php]

      Coba tekan F12 pada browser dan buka tab network , kemudian tambahkan data baru pastikan mendapat response json array seperti gambar berikut

      Jika masih error dapat anda kirimkan project anda ke saya agar dapat saya lihat . Semoga membantu:)

      Reply
      • Niko

        Sudah saya kirim email mas 🙂

        Reply
      • Chiefphreak

        Output from de debugger. I see that there is an error in the buttons but I don’t know how to solve this.

        Reply
        • Sigit Prasetya Nugroho

          Check your code in data.php below:

          Reply
    • avocalipso

      solvednya diapain bang? ko mendadak sama ya nongol Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 hihi
      Mohon bantuannya

      Reply
  25. Bayu Kurniawan

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

    Reply
    • Sigit Prasetya Nugroho

      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

      Reply
  26. Bayu Kurniawan

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

    Reply
    • Sigit Prasetya Nugroho

      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 ) 🙂

      Reply
  27. Taufik

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

    Reply
  28. ian

    gan, ane coba pake script agan..sukses utk post data, tapi gk show results di page nya..script agan gk ad yg ane rubah..kra2 dmn salahnya y gan ? tlg dbantu gan…thanks

    Reply
  29. ARjun P Yadav

    Hi Friend
    I have a problem and i think you can help me .
    Can you please tell me how to show the image column of a table in pdf when use export button in Datatable

    Reply
  30. Joko Supriyanto

    Link code full nya mna gan?

    Reply
  31. r15ch13

    This example code is vulnerable to SQL Injections! Please use PDO or at least MySQLi.

    Reply

Leave a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.