Uncategorized

CRUD dengan Ajax, PHP, dan MySQL

0 Comments

Sebelum memulai membuat program saya akan sedikit membahas tentang ajax. Asynchronous javascript and xml (Ajax) secara singkatnya merupakan teknik pemrograman web, yang dimana data request dari client ke server akan dikerjakan dibelakang layar (tanpa memuat ulang halaman). Jadi prosesnya akan lebih cepat, ringan, serta interaktif. Jadi kalau sebelumnya saya sudah pernah share ke kalian tentang aplikasi buku tamu / guest book. Ketika menambahkan atau memperbaharui data, maka browser akan memuat ulang dokumen atau halaman tersebut agar data baru tersebut dapat dilihat. Berbeda dengan ajax, kalian tidak akan memuat ulang dokumen / halaman tersebut (alias realtime).

Requirement :

  • PHP v5+
  • MySQL v5.5+
  • JQuery v2+
  • Bootstrap v4+
  • Datatables

Struktur File dan Folder :

  • css
    • style.css
  • inc
    • db_config.php
    • user.php
  • js
    • jquery.dataTables.min.js
    • jquery-2.1.3.min.js
  • index.php
  • userAction.php

Membuat Database

Pertama – tama seperti biasa, buatlah database baru dengan nama macode kemudian buat table dengan nama users serta 4 fields sebagai berikut :

CREATE TABLE 'users' (
    'id' int(11) NOT NULL AUTO_INCREMENT,
    'first_name' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    'last_name' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    'email' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    'password' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    'phone' varchar(15) COLLATE utf8_unicode_ci NOT NULL,
    'created' datetime NOT NULL,
    'modified' datetime NOT NULL,
    'status' enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
    PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Konfigurasi Database (db_config.php)

Setelah membuat database, selanjutnya adalah membuat konfigurasi awal. Untuk informasi database yang telah dibuat sebelumnya agar lebih mudah diintegrasikan ke beberapa class.

<?php

class DB_Config {
    
    public $db_host = "localhost";
    public $db_username = "root";
    public $db_password = "";
    public $db_name = "crud";

}
?>

Membuat Tampilan Halaman – Data table

Kalian bisa membuat sendiri tampilan data tablenya atau menggunakan template. Silahkan download full source code pada link dibawah untuk tampilan data table seperti di atas.

File : index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    
    <title>Realtime Datatable</title>

    <link rel="stylesheet" type="text/css" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css">
    <link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>

 <div class="container">
    <div class="row">
        <div class="col-md-12 mt-3">
            <h3>Table Users</h3>
            <hr class="ma-hr">
            <!-- Add link -->
            <div class="float-right">
                <a href="javascript:void(0);" class="btn btn-success" data-type="add" data-toggle="modal" data-target="#modalUserAddEdit"><i class="plus"></i> New User</a>
            </div>
        </div>
        <div class="statusMsg"></div>
        <!-- List the users -->
        <table class="cn-data-tables">
            <thead class="">
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody id="userData">
                <?php if(!empty($users)) { foreach($users as $row) { ?>
                <tr>
                    <td><?php echo $row['id']; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['email']; ?></td>
                    <td><?php echo $row['phone']; ?></td>
                    <td>
                        <a href="javascript:void(0);" class="btn btn-warning" rowID="<?php echo $row['id']; ?>" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a>
                        <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm('Are you sure to delete data?')?userAction('delete', '<?php echo $row['id']; ?>'):false;">delete</a>
                    </td>
                </tr>
                <?php } }else{ ?>
                <tr><td colspan="5">No user(s) found...</td></tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</div>



<!-- Modal Add and Edit Form -->
<div class="modal fade" id="modalUserAddEdit" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <!-- Modal Header -->
            <div class="modal-header">
              <h4 class="modal-title">Add New User</h4>
              <button type="button" class="close" data-dismiss="modal">×</button>
            </div>
            
            <!-- Modal Body -->
            <div class="modal-body">
                <div class="statusMsg"></div>
                <form role="form">
                    <div class="form-group">
                        <label for="name">Name</label>
                        <input type="text" class="form-control" name="name" id="name" placeholder="Enter your name">
                    </div>
                    <div class="form-group">
                        <label for="email">Email</label>
                        <input type="email" class="form-control" name="email" id="email" placeholder="Enter your email">
                    </div>
                    <div class="form-group">
                        <label for="phone">Phone</label>
                        <input type="text" class="form-control" name="phone" id="phone" placeholder="Enter phone no">
                    </div>
                    <input type="hidden" class="form-control" name="id" id="id"/>
                </form>
            </div>
            
            <!-- Modal Footer -->
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-success" id="userSubmit">SUBMIT</button>
            </div>
        </div>
    </div>
</div>

<script src="js/jquery-2.1.3.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.bundle.min.js"></script>
<script src="js/jquery.dataTables.js"></script>
<script src="js/ajaxController.js"></script>

</body>
</html>

Model class user (user.php)

Tujuan dari model kelas user adalah sebagai kerangka code fungsi yang nantinya akan digunakan / diintegrasikan ke kontroler. Pada model user ini terdapat beberapa method seperti insert() untuk menambahkan data, update() untuk memperbaharui data, delete() untuk menghapus data dan, getRows() untuk pembacaan data pada database.

<?php

require_once 'db_config.php';

class User extends DB_Config {
    
    public $db;
    
    public function __construct() {
        // Koneksi ke database
        $this->db = new mysqli($this->db_host, $this->db_username, $this->db_password, $this->db_name);
        if ($this->db->connect_errno) {
            printf("Connect failed: %s\n", $this->db->connect_error);
            exit();
        }
    }
    
    /*
     * Melakukan permintaan data yang ada pada database sesuai parameternya
     * @param table adalah nama tabel
     * @param condition, merupakan sebuah aturan permintaan data misalnya limit, order_by atau where.
     */
    public function getRows($table, $conditions = array()) {
        $sql = 'SELECT ';
        $sql .= array_key_exists( "select",$conditions ) ? $conditions['select'] : '*';
        $sql .= ' FROM ' . $table;

        if ( array_key_exists("where",$conditions) ){
            $sql .= ' WHERE ';
            $i = 0;
            foreach ( $conditions['where'] as $key => $value ) {
                $pre = ($i > 0)?' AND ':'';
                $sql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        
        if (array_key_exists("order_by", $conditions)) {
            $sql .= ' ORDER BY ' . $conditions['order_by']; 
        } else {
            $sql .= ' ORDER BY id DESC ';
        }
        
        if (array_key_exists("start", $conditions) && array_key_exists("limit", $conditions)) {
            $sql .= ' LIMIT ' . $conditions['start'] . ',' . $conditions['limit']; 
        } elseif (!array_key_exists("start", $conditions) && array_key_exists("limit", $conditions)) {
            $sql .= ' LIMIT ' . $conditions['limit']; 
        }
        
        $result = $this->db->query($sql);
        
        if ( array_key_exists("return_type", $conditions) && $conditions['return_type'] != 'all' ) {
            switch ( $conditions['return_type'] ) {
                case 'count':
                    $data = $result->num_rows;
                    break;
                case 'single':
                    $data = $result->fetch_assoc();
                    break;
                default:
                    $data = '';
            }
        } else {
            if ( $result->num_rows > 0) {
                while ( $row = $result->fetch_assoc() ) {
                    $data[] = $row;
                }
            }
        }
        return !empty($data) ? $data : false;
    }
    
    /*
     * Memasukkan data ke dalam database
     * @param table, adalah nama tabel
     * @param data, adalah data yang akan dimasukkan ke database
     */
    public function insert( $table, $data ) {
        if ( !empty($data) && is_array($data) ) {
            $columns = '';
            $values  = '';
            $i = 0;
            if ( !array_key_exists('created', $data) ){
                $data['created'] = date("Y-m-d H:i:s");
            }
            if ( !array_key_exists('modified', $data) ){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            foreach ( $data as $key => $val ) {
                $pre = ($i > 0) ? ', ' : '';
                $columns .= $pre . $key;
                $values  .= $pre . "'" . $this->db->real_escape_string($val) . "'";
                $i++;
            }
            $query = "INSERT INTO " . $table . " (" . $columns . ") VALUES (" . $values . ")";
            $insert = $this->db->query($query);
            return $insert ? $this->db->insert_id : false;
        } else {
            return false;
        }
    }
    
    /*
     * Update / memperbaharui data di database
     * @param table, adalah nama tabel
     * @param data, adalah data yang ingin diperbaharui
     * @param condition, merupakan sebuah aturan permintaan data misalnya limit, order_by atau where.
     */
    public function update( $table, $data, $conditions ) {
        if ( !empty($data) && is_array($data) ) {
            $colvalSet = '';
            $whereSql = '';
            $i = 0;
            if ( !array_key_exists('modified', $data) ) {
                $data['modified'] = date("Y-m-d H:i:s");
            }
            foreach ( $data as $key => $val ){
                $pre = ($i > 0) ? ', ' : '';
                $colvalSet .= $pre . $key . "='" . $this->db->real_escape_string($val) . "'";
                $i++;
            }
            if ( !empty($conditions) && is_array($conditions) ){
                $whereSql .= ' WHERE ';
                $i = 0;
                foreach ( $conditions as $key => $value ) {
                    $pre = ($i > 0) ? ' AND ' : '';
                    $whereSql .= $pre . $key . " = '" . $value . "'";
                    $i++;
                }
            }
            $query = "UPDATE " . $table . " SET " . $colvalSet . $whereSql;
            $update = $this->db->query($query);
            return $update ? $this->db->affected_rows : false;
        } else {
            return false;
        }
    }
    
    /*
     * Menghapus data yang ada pada database
     * @param table, adalah nama tabel
     * @param condition, merupakan sebuah aturan permintaan data misalnya limit, order_by atau where.
     */
    public function delete( $table, $conditions ){
        $whereSql = '';
        if( !empty($conditions) && is_array($conditions) ){
            $whereSql .= ' WHERE ';
            $i = 0;
            foreach ( $conditions as $key => $value ) {
                $pre = ($i > 0) ? ' AND ' : '';
                $whereSql .= $pre . $key . " = '" . $value . "'";
                $i++;
            }
        }
        $query = "DELETE FROM " . $table . $whereSql;
        $delete = $this->db->query($query);
        return $delete ? true : false;
    }
}
?>

Kontroler

Setelah kita membuat modelnya, selanjutnya adalah membuat kontrolernya. Karna disini kita menggunakan ajax, maka yang akan memanggil perintah yang ada di kontroler ini adalah ajax (javascript). Jadi akan kita bagi 2, controler file (php) dan controller file (javascript).

File : userAction.php

<?php
// Include dan initialisasi class DB
require_once 'inc/user.php';
$db = new User();

// Nama tabel dalam database
$tblName = 'users';

// Jika form di submit / menekan tombol submit
if (!empty($_POST['action_type'])) {
    if ($_POST['action_type'] == 'data') {
        
        $conditions['where'] = array('id' => $_POST['id']);
        $conditions['return_type'] = 'single';
        $user = $db->getRows($tblName, $conditions);
        
        // Mengembalikan data ke format JSON
        echo json_encode($user);
    } elseif($_POST['action_type'] == 'view') {
        
        $users = $db->getRows($tblName);
        
        // Output - Format HTML
        if (!empty($users)) {
            foreach($users as $row) {
                echo '<tr>';
                echo '<td>#'.$row['id'] . '</td>';
                echo '<td>'.$row['name'] . '</td>';
                echo '<td>'.$row['email'] . '</td>';
                echo '<td>'.$row['phone'] . '</td>';
                echo '<td><a href="javascript:void(0);" class="btn btn-warning" rowID="' . $row['id'] . '" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a>
                <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm(\'Are you sure to delete data?\')?userAction(\'delete\', \'' . $row['id'] . '\'):false;">delete</a></td>';
                echo '</tr>';
            }
        } else {
            echo '<tr><td colspan="5">No user(s) found...</td></tr>';
        }
    } elseif ($_POST['action_type'] == 'add') {
        $msg = '';
        $status = $verr = 0;
        
        // Inputan pengguna
        $name = $_POST['name'];
        $email = $_POST['email'];
        $phone = $_POST['phone'];
        
        // Validasi input
        if (empty($name)) {
            $verr = 1;
            $msg .= 'Please enter your name.<br/>';
        }
        if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $verr = 1;
            $msg .= 'Please enter a valid email.<br/>';
        }
        if (empty($phone)) {
            $verr = 1;
            $msg .= 'Please enter your phone no.<br/>';
        }
        
        if ($verr == 0) {
            // Memasukkan data ke database
            $userData = array(
                'name'  => $name,
                'email' => $email,
                'phone' => $phone
            );
            $insert = $db->insert($tblName, $userData);
            
            if ($insert) {
                $status = 1;
                $msg .= 'User data has been inserted successfully.';
            } else {
                $msg .= 'Some problem occurred, please try again.';
            }
        }
        
        // Respon berupa JSON
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    } elseif ($_POST['action_type'] == 'edit') {
        $msg = '';
        $status = $verr = 0;
        
        if (!empty($_POST['id'])) {
            // Inputan pengguna
            $name = $_POST['name'];
            $email = $_POST['email'];
            $phone = $_POST['phone'];
            
            // Validasi input
            if (empty($name)) {
                $verr = 1;
                $msg .= 'Please enter your name.<br/>';
            }
            if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $verr = 1;
                $msg .= 'Please enter a valid email.<br/>';
            }
            if (empty($phone)) {
                $verr = 1;
                $msg .= 'Please enter your phone no.<br/>';
            }
            
            if ($verr == 0) {
                // Update data pada database
                $userData = array(
                    'name'  => $name,
                    'email' => $email,
                    'phone' => $phone
                );
                $condition = array('id' => $_POST['id']);
                $update = $db->update($tblName, $userData, $condition);
                
                if ($update) {
                    $status = 1;
                    $msg .= 'User data has been updated successfully.';
                } else {
                    $msg .= 'Some problem occurred, please try again.';
                }
            }
        } else {
            $msg .= 'Some problem occurred, please try again.';
        }
        
        // Respon berupa JSON
        $alertType = ($status == 1) ? 'alert-success' : 'alert-danger';
        $statusMsg = '<p class="alert ' . $alertType . '">' . $msg . '</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);

    } elseif ($_POST['action_type'] == 'delete') {
        $msg = '';
        $status = 0;
        
        if (!empty($_POST['id'])) {
            // Menghapus data yang ada pada database
            $condition = array('id' => $_POST['id']);
            $delete = $db->delete($tblName, $condition);
            
            if ($delete) {
                $status = 1;
                $msg .= 'User data has been deleted successfully.';
            } else {
                $msg .= 'Some problem occurred, please try again.';
            }
        } else {
            $msg .= 'Some problem occurred, please try again.';
        }  

        $alertType = ($status == 1) ? 'alert-success' : 'alert-danger';
        $statusMsg = '<p class="alert ' . $alertType . '">' . $msg . '</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }
}

exit;
?>

File : ajaxController.js

// Data Tables
var cnTable = $(".cn-data-tables").dataTable();
$('.dataTables_paginate > a').wrapInner('');
$('.dataTables_paginate > a span').addClass('btn-paginate');

function getUsers() {
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        data: 'action_type=view',
        success:function(html){
            $('#userData').html(html);
        }
    });
}

// Mengirim permintaan CRUD ke server
function userAction( type, id ) {
    id = ( typeof id == "undefined" ) ? '' : id;
    var userData = '', frmElement = '';

    if( type == 'add' ) {
        frmElement = $("#modalUserAddEdit");
        userData = frmElement.find('form').serialize() + '&action_type=' + type + '&id=' + id;
    } else if ( type == 'edit' ) {
        frmElement = $("#modalUserAddEdit");
        userData = frmElement.find('form').serialize() + '&action_type=' + type;
    } else {
        frmElement = $(".row");
        userData = 'action_type=' + type + '&id=' + id;
    }
    frmElement.find('.statusMsg').html('');
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        dataType: 'JSON',
        data: userData,
        beforeSend: function() {
            frmElement.find('form').css("opacity", "0.5");
        },
        success:function( resp ) {
            frmElement.find('.statusMsg').html(resp.msg);
            if(resp.status == 1){
                if(type == 'add'){
                    frmElement.find('form')[0].reset();
                }
                getUsers();
            }
            frmElement.find('form').css("opacity", "");
        }
    });
}

function editUser( id ) {
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        dataType: 'JSON',
        data: 'action_type=data&id=' + id,
        success:function( data ) {
            $('#id').val(data.id);
            $('#name').val(data.name);
            $('#email').val(data.email);
            $('#phone').val(data.phone);
        }
    });
}

$(function() {
    $('#modalUserAddEdit').on('show.bs.modal', function( e ) {
        var type = $(e.relatedTarget).attr('data-type');
        var userFunc = "userAction('add');";
        if(type == 'edit') {
            userFunc = "userAction('edit');";
            var rowId = $(e.relatedTarget).attr('rowID');
            editUser(rowId);
        }
        $('#userSubmit').attr("onclick", userFunc);
    });
    
    $('#modalUserAddEdit').on('hidden.bs.modal', function() {
        $('#userSubmit').attr("onclick", "");
        $(this).find('form')[0].reset();
        $(this).find('.statusMsg').html('');
        setTimeout(function() {
            location.reload();
       }, 0001);
    });
});

Terakhir adalah menambahkan syntax berikut ke file index.php.

<?php
// Include and initialize DB class
require_once 'inc/user.php';
$query = new User();

// Fetch the users data
$users = $query->getRows('users');
?>

Oke mungkin cukup sekian tutorial kali ini, jangan lupa share juga keteman kalian. Kalian juga dapat mendownload full source code pada tombol dibawah ini :

Source Code Database

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *