1. Tutorials
  2. PHP Tutorial
  3. Vanilla DataTables CRUD using PHP and MySQL Database Tutorial

Vanilla DataTables CRUD using PHP and MySQL Database Tutorial

Submitted by oretnom23 on Saturday, November 12, 2022 - 15:31.

Introduction

In this tutorial, you will learn to Create CRUD Functionalities using the Vanilla DataTables, PHP, and MySQL Database. This tutorial aims to provide a reference or a guide to beginners or new programmers to enhance their PHP and JS programming capabilities. Here, snippets and sample source code are provided and the source code file is free to download.

What is Vanilla DataTables?

The Vanilla DataTables is a lightweight, extendable, dependency-free javascript HTML table plugin. It is like a VanillaJS version of the jQuery DataTables Plugin which has a lot of options, methods, and events for managing or manipulating the HTML table on the client side. This plugin contains multiple functionalities such as sorting, searchable, pagination, and more. To know more, visit https://github.com/Mobius1/Vanilla-DataTables.

How to CRUD Functionalities in Vanilla DataTables using PHP and MySQL Database?

There are a lot of ways to create CRUD (Create, Read, Update, and Delete) functionalities for Vanilla DataTables Data using PHP and MySQL Database. Here, I will show you how it is being done effectively by using the following technologies.

  • HTML
  • Plain JavaScript
  • Vanilla DataTables Plugin
  • PHP
  • MySQL Database
  • Bootstrap Framework

Getting started

First, download XAMPP or any equivalent software to run PHP Script and MySQL Database on your local machine. For those who are using XAMPP, open the XAMPP's Control Panel and start Apache and MySQL.

Download Vanilla DataTables

Next, download the Vanilla DataTables plugin at https://github.com/Mobius1/Vanilla-DataTables or you can also use the provided CDNs of the plugin library.

Creating the Database

Next, create the sample database that we need for this tutorial. Create a new database named dummy_db. Then use the following MySQL Schema to create the sample table.

  1.     CREATE TABLE `members` (
  2.         `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3.         `name` varchar(200) NOT NULL,
  4.         `contact` varchar(50) NOT NULL,
  5.         `address` text NOT NULL,
  6.         `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  7.         `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
  8.     ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Let's do the Coding Part

Database Connection

The below snippets is a PHP Script that handles the database connection between the web application and the MySQL Database.

db-connect.php

  1. <?php
  2. $host = "localhost";
  3. $username = "root";
  4. $pw = "";
  5. $db_name = "dummy_db";
  6.  
  7. $conn = new mysqli($host, $username, $pw, $db_name);
  8.     if(!$conn){
  9.     die('Database connection failed');
  10.     }
  11. ?>

Creating the Backend/API

The following snippet is a PHP Script that contains the CRUD functionalities on the backend. The script handles all Insert, Update, Retrieve, and Delete Data on the Database.

api.php

  1. <?php
  2. require_once('db-connect.php');
  3. $action = isset($_GET['action']) ? $_GET['action'] : '';
  4.  
  5. /**
  6.     * Fetch All Members
  7.     */
  8.  
  9. function get_data(){
  10.     global $conn;
  11.     $sql = "SELECT * FROM `members` order by `id` asc";
  12.  
  13.     $qry = $conn->query($sql);
  14.     $data = [];
  15.     if($qry->num_rows > 0){
  16.         while($row = $qry->fetch_assoc()){
  17.             $data[] = [
  18.                 'id' => $row['id'],
  19.                 'name' => $row['name'],
  20.                 'contact' => $row['contact'],
  21.                 'address' => $row['address'],
  22.                 'action' => $row['id']
  23.             ];
  24.         }
  25.     }
  26.     $conn->close();
  27.     return json_encode($data);
  28. }
  29.  
  30. /**
  31.     * Insert/Update Member
  32.     */
  33.  
  34. function save_member(){
  35.     global $conn;
  36.     if($_SERVER['REQUEST_METHOD'] == 'POST'){
  37.         $id = $_POST['id'];
  38.         $name = addslashes($conn->real_escape_string($_POST['name']));
  39.         $address = addslashes($conn->real_escape_string($_POST['address']));
  40.         $contact = addslashes($conn->real_escape_string($_POST['contact']));
  41.         if(empty($id) || !is_numeric($id)){
  42.             $sql = "INSERT INTO `members` (`name`, `contact`, `address`)
  43.                VALUES ('{$name}', '{$contact}', '{$address}')";
  44.         }else{
  45.             $sql = "UPDATE `members` set `name` = '{$name}', `contact` = '{$contact}', `address` = '{$address}' where `id` = '{$id}' ";
  46.         }
  47.         $save = $conn->query($sql);
  48.         if($save){
  49.             $resp['status'] ='success';
  50.         }else{
  51.             $resp['status'] ='failed';
  52.             $resp['error'] =$conn->error;
  53.         }
  54.     }else{
  55.         $resp['status'] = 'failed';
  56.         $resp['error'] = "Request must be using POST Method.";
  57.     }
  58.     $conn->close();
  59.     return json_encode($resp);
  60. }
  61.  
  62. /**
  63.     * Fetch Single Member Data
  64.     */
  65. function get_single(){
  66.     global $conn;
  67.     if($_SERVER['REQUEST_METHOD'] == 'POST'){
  68.         $id = $_POST['id'];
  69.         $sql = "SELECT * FROM `members` where id = '{$id}'";
  70.         $get = $conn->query($sql);
  71.         if($get->num_rows > 0){
  72.             $resp['status'] = 'success';
  73.             $resp['data'] = $get->fetch_assoc();
  74.         }else{
  75.             $resp['status'] = 'failed';
  76.             $resp['error'] = "Member ID does not exists.";
  77.         }
  78.     }else{
  79.         $resp['status'] = 'failed';
  80.         $resp['error'] = "Request must be using POST Method.";
  81.     }
  82.     $conn->close();
  83.     return json_encode($resp);
  84. }
  85.  
  86. /**
  87.     * Delete Member Data
  88.     */
  89. function delete_member(){
  90.     global $conn;
  91.     if($_SERVER['REQUEST_METHOD'] == 'POST'){
  92.         $id = $_POST['id'];
  93.         $sql = "DELETE FROM `members` where id = '{$id}'";
  94.         $delete = $conn->query($sql);
  95.         if($delete){
  96.             $resp['status'] = 'success';
  97.         }else{
  98.             $resp['status'] = 'failed';
  99.             $resp['error'] = $conn->error;
  100.         }
  101.     }else{
  102.         $resp['status'] = 'failed';
  103.         $resp['error'] = "Request must be using POST Method.";
  104.     }
  105.     $conn->close();
  106.     return json_encode($resp);
  107. }
  108.  
  109. if(!empty($action)){
  110.     if(function_exists($action)){
  111.         $exec = $action();
  112.         echo $exec;
  113.     }else{
  114.         $resp['status'] = 'failed';
  115.         $resp['error'] = 'Invalid Given Action';
  116.         return $resp;
  117.     }
  118. }else{
  119.     $resp['status'] = 'failed';
  120.     $resp['error'] = 'Action must not be empty';
  121.     return $resp;
  122. }

Creating the JavaScript

The snippet below is a JavaScript file that contains all the functionalities of the web application on the client side. The script below is not dependent on any other JavaScript library or framework except for the Vanilla DataTables Plugin and Bootstrap v5 Modal.

app.js

  1. var formModal,
  2. vanillaDT,
  3. modal,
  4. memberForm;
  5. document.addEventListener('DOMContentLoaded', () => {
  6.     /**
  7.         * Initialize Vanilla JS DataTabale
  8.         */
  9.     var dt_option = {
  10.         columns: [{
  11.             select : 3 ,
  12.             render: function(data){
  13.                 return data.replaceAll(/\n\r/gi,'<br>');
  14.             }
  15.         },{
  16.             select : 4 ,
  17.             render: function(data){
  18.                 return `<div class="text-center">
  19.                             <button class="btn btn-outline-primary btn-sm rounded-0" type="button" onclick="edit_member(${data})">
  20.                                 <i class="fa-solid fa-edit"></i>
  21.                             </button>
  22.                             <button class="btn btn-outline-danger btn-sm rounded-0" type="button" onclick="delete_member(${data})">
  23.                                 <i class="fa-solid fa-trash"></i>
  24.                             </button>
  25.                         <div class="text-center">
  26.                         `;
  27.             }
  28.         }],
  29.         ajax:'api.php?action=get_data'
  30.     }
  31.     vanillaDT = new DataTable('#memberTable',dt_option)
  32.  
  33.     /**
  34.         * Form Modal
  35.         */
  36.     modal = document.getElementById('memberFormModal')
  37.     formModal = new bootstrap.Modal('#memberFormModal', {
  38.         backdrop :'static'
  39.     })
  40.     memberForm = document.getElementById('member-form')
  41.  
  42.     document.getElementById('add_new').addEventListener('click', function(){
  43.         modal.querySelector('.modal-title').innerText = `Add New Member`
  44.         formModal.show()
  45.     })
  46.     modal.addEventListener('hide.bs.modal', function(){
  47.         memberForm.reset()
  48.     })
  49.  
  50.  
  51.     /**
  52.         * Form Submission
  53.         */
  54.  
  55.         memberForm.addEventListener('submit', (e) => {
  56.         e.preventDefault()
  57.         var formData = new FormData(memberForm)
  58.         modal.querySelectorAll('.btn, button').forEach( el => { el.setAttribute('disabled',true) } )
  59.         fetch('api.php?action=save_member', {
  60.             method: 'POST',
  61.             body: formData
  62.         }).then(resp=>{
  63.             return resp.json();
  64.         }).then(data => {
  65.             if(!!data.status){
  66.                 if(data.status == 'success'){
  67.                     alert(`Member's Data has been saved successfully.`);
  68.                        vanillaDT.destroy()
  69.                        vanillaDT.init()
  70.                }else if(!!data.error){
  71.                    alert("Saving Member's Data Failed due to some error.");
  72.                    console.log(data.error)
  73.                }else{
  74.                    alert("Saving Member's Data Failed due to some error.");
  75.                }
  76.  
  77.            }else{
  78.                alert("Saving Member's Data Failed due to some error.");
  79.            }
  80.            modal.querySelectorAll('.btn, button').forEach( el => { el.removeAttribute('disabled') } )
  81.            formModal.hide();
  82.        }).catch((error) => {
  83.            console.error(error)
  84.            modal.querySelectorAll('.btn, button').forEach( el => { el.removeAttribute('disabled') } )
  85.        });
  86.        })
  87.    
  88. } )
  89.  
  90. /**
  91.    * Edit Member Function
  92.    * @param {*} $id
  93.    */
  94.  
  95. function edit_member($id=0){
  96.    var formData = new FormData();
  97.    formData.append('id', $id)
  98.    fetch('api.php?action=get_single',{
  99.        method:'POST',
  100.        body:formData
  101.    })
  102.    .then(resp => {
  103.        return resp.json();
  104.    })
  105.    .then(data => {
  106.        if(!!data.status){
  107.            if(data.status == 'success'){
  108.                memberForm.querySelector('input[name="id"]').value = data.data.id
  109.                memberForm.querySelector('input[name="name"]').value = data.data.name
  110.                memberForm.querySelector('input[name="contact"]').value = data.data.contact
  111.                memberForm.querySelector('textarea[name="address"]').value = data.data.address
  112.                modal.querySelector('.modal-title').innerText = "Edit Member's Data";
  113.                formModal.show()
  114.            }else if(!!data.error){
  115.                alert("Fetching Data Failed.")
  116.                console.error(data.error)
  117.            }else{
  118.                alert("Fetching Data Failed.")
  119.                console.error(data)
  120.            }
  121.        }else{
  122.            alert("Fetching Data Failed.")
  123.                console.error(data)
  124.        }
  125.        
  126.    })
  127.    .catch(error=>{
  128.        console.log(error)
  129.        alert("Fetching Data Failed.")
  130.    })
  131. }
  132.  
  133. /**
  134.    * Delete Member
  135.    * @param {*} $id
  136.    * @returns
  137.    */
  138.  
  139. function delete_member($id=0){
  140.    if(confirm(`Are you sure to delete this member?`) === false)
  141.    return false;
  142.    var formData = new FormData();
  143.    formData.append('id', $id)
  144.    fetch('api.php?action=delete_member',{
  145.        method:'POST',
  146.        body:formData
  147.    })
  148.    .then(resp => {
  149.        return resp.json();
  150.    })
  151.    .then(data => {
  152.        if(!!data.status){
  153.            if(data.status == 'success'){
  154.                alert("Member has been deleted successfully.");
  155.                vanillaDT.destroy();
  156.                vanillaDT.init();
  157.            }else if(!!data.error){
  158.                alert("Fetching Data Failed.")
  159.                console.error(data.error)
  160.            }else{
  161.                alert("Fetching Data Failed.")
  162.                console.error(data)
  163.            }
  164.        }else{
  165.            alert("Fetching Data Failed.")
  166.                console.error(data)
  167.        }
  168.        
  169.    })
  170.    .catch(error=>{
  171.        console.log(error)
  172.        alert("Fetching Data Failed.")
  173.    })
  174. }
  175.    

Snapshots

Using the provided snippets will result in a simple application that allows the end-user to manage the member list.

Main Page

Vanilla DataTables CRUD -Main Page

Form Modal

Vanilla DataTables CRUD - Form Modal

That's it! You can now test the simple web application using the above snippets on your end. You can also download the compiled source code zip file on this website for free.

That's the end of this tutorial. I hope this Vanilla DataTables CRUD using PHP and MySQL Tutorial will help you with what you are looking for and that you'll find this useful for your future projects.

Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)

Tags
php
MySQL Database
Vanilla JS
Vanilla JS DataTables
php tutorial
datatables
DataTables CRUD
CRUD
php crud
JS CRUD
JS tutorial

Comments

Submitted by Pradeep Shukla (not verified) on Fri, 12/30/2022 - 18:22

Add a new table but delete or edit, is working or the alias name is not coming in the table while the database is going right in the table

Add new comment

About text formats