CRUD Ajax and PHP Without Refresh Page

CRUD Ajax and PHP Without Refresh Page

 

CRUD is a familiar operating function for a web programmer, CRUD almost every web application available on the internet. Without CRUD it will be difficult to manage a data on the database. CRUD application is very important for a programmer because it is the framework of a web application.

CRUD stands for functions of Add / Create / New, Read / View, Edit / Update and Delete / Remove data from the database. The function is always there to connect the application form input to the database. Maybe, you can create CRUD with PHP and MySQL, but this tutorial we will implement how to make CRUD with jQuery Ajax and PHP MySQL without refresh page.

Creating CRUD with Ajax function is very nice because it makes our web application will be light and easy in inputting and displaying data from database, with Ajax function will also make page without refresh.

Create Database

You can create your own database with own table name and own item name, but if you want to like this tutorial is also not a problem, you can simply copy and paste the code below into your SQL execution like Phpmyadmin or Workbench.

CREATE DATABASE IF NOT EXISTS `mydata` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `mydata`;

CREATE TABLE `crud` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `address` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Layout Design (index.html)

You can design layouts for forms and tables by using Bootstrap, Metro UI CSS, UIKit, Semantic UI, MaterializeCSS and other CSS Framewok. In addition, you can also design with basic HTML5 / CSS3. Below is a layout design using Bootstrap for one page without refresh, and you can copy and paste to new file with name index.html.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>One Page CRUD Ajax</title>

    <link href="assets/css/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/font-awesome.min.css" rel="stylesheet">
    <link href="assets/css/dataTables.bootstrap.min.css" rel="stylesheet">

    <!-- HTML5 shim and Respond.js for 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>
<body>
    
    <div class="container">
        <h3>One Page CRUD Ajax PHP/MySQL</h3>
        <div class="row">
            <div class="col-md-9">
                <table class="table table-bordered table-striped table-hover">
                    <thead>
                        <tr>
                            <th width="40">ID</th>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Phone</th>
                            <th>Address</th>
                            <th width="100">Action</th>
                        </tr>
                    </thead>
                    <tbody></tbody>
                </table>
            </div>
            <div class="col-md-3">
                <form id="sidebar">
                    <div class="form-group">
                        <label for="id">ID</label>
                        <input type="number" id="id" name="id" class="form-control" placeholder="Identity"/>
                    </div>
                    <div class="form-group">
                        <label for="nm">Name</label>
                        <input type="text" id="nm" name="nm" class="form-control" placeholder="Full Name"/>
                    </div>
                    <div class="form-group">
                        <label for="em">Email</label>
                        <input type="email" id="em" name="em" class="form-control" placeholder="Your Email"/>
                    </div>
                    <div class="form-group">
                        <label for="hp">Phone Number</label>
                        <input type="tel" id="hp" name="hp" class="form-control" placeholder="Mobile Phone Number"/>
                    </div>
                    <div class="form-group">
                        <label for="ad">Address</label>
                        <textarea id="ad" name="ad" class="form-control" placeholder="Street Address"></textarea>
                    </div>
                    <button type="button" id="save" class="btn btn-primary" onclick="saveData()">Save</button>
                    <button type="button" id="update" class="btn btn-warning" onclick="updateData()">Update</button>
                </form>
            </div>
        </div>
    </div>

    <script src="assets/js/jquery.min.js"></script>
    <script src="assets/js/bootstrap.min.js"></script>
    <script src="assets/js/jquery.dataTables.min.js"></script>
    <script src="assets/js/dataTables.bootstrap.min.js"></script>
    <script src="script.js"></script>
</body>
</html>

Connect to Database

The first time in making a web application is a connection between a web application with a database that you can create a special file with the name of config.php or connection.php or you can combine the connection with the other files such as this tutorial we combine into one file all functions both connections , add data, view data, edit update and delete data.

$dbh = new PDO('mysql:host=localhost;dbname=mydata', 'root', '');

Add/Create Data

To add data or create data, in this tutorial we will discuss how to implement or how to work added data into database by using ajax jquery and php, the data will be executed through input form which have id by clicking the button that there is javascript function that is onclick attribute, The data will be redirected to the saveData () function on the onclick attribute, for example the form below which has been given id on input and javascript function on the button.

<form id="sidebar">
      <div class="form-group">
            <label for="id">ID</label>
            <input type="number" id="id" name="id" class="form-control" placeholder="Identity"/>
      </div>
      <div class="form-group">
            <label for="nm">Name</label>
            <input type="text" id="nm" name="nm" class="form-control" placeholder="Full Name"/>
      </div>
      <div class="form-group">
            <label for="em">Email</label>
            <input type="email" id="em" name="em" class="form-control" placeholder="Your Email"/>
      </div>
      <div class="form-group">
            <label for="hp">Phone Number</label>
            <input type="tel" id="hp" name="hp" class="form-control" placeholder="Mobile Phone Number"/>
      </div>
      <div class="form-group">
            <label for="ad">Address</label>
            <textarea id="ad" name="ad" class="form-control" placeholder="Street Address"></textarea>
      </div>
      <button type="button" id="save" class="btn btn-primary" onclick="saveData()">Save</button>
</form>

The data will then be sent to the saveData() function (script.js) using javascript or jquery and in the saveData() function is Ajax for useful post data to send data to file server such as PHP, JSP, ASP.Net, Perl, Python and others.

function saveData(){
    var id = $('#id').val()
    var name = $('#nm').val()
    var email = $('#em').val()
    var phone = $('#hp').val()
    var address = $('#ad').val()
    $.post('server.php?p=add', {id:id, nm:name, em:email, hp:phone, ad:address}, function(data){
        viewData()
        $('#id').val(' ')
        $('#nm').val(' ')
        $('#em').val(' ')
        $('#hp').val(' ')
        $('#ad').val(' ')
    })
}

On a file server like php (server.php) is the place for execution of data into the database, data sent from saveData () will be executed here and will be stored into the database.

try{
        $id = $_POST['id'];
        $nm = $_POST['nm'];
        $em = $_POST['em'];
        $hp = $_POST['hp'];
        $ad = $_POST['ad'];
        $stmt = $dbh->prepare("INSERT INTO crud VALUES(?,?,?,?,?)");
        $stmt->bindParam(1,$id);
        $stmt->bindParam(2,$nm);
        $stmt->bindParam(3,$em);
        $stmt->bindParam(4,$hp);
        $stmt->bindParam(5,$ad);
        if($stmt->execute()){
            print "<div class='alert alert-success' role='alert'>Data has been added</div>";
        } else{
            print "<div class='alert alert-danger' role='alert'>Failed to add data</div>";
        }
} catch(PDOException $e){
        print "Error!: " . $e->getMessage() . "<br/>";
}

View/Read Data

Read data or view data is a function or operation to display data from the database into the table through php and ajax with no refresh when done data addition and data editing. You can create a table by providing with tbody tags like below.

<table class="table table-bordered table-striped table-hover">
    <thead>
        <tr>
            <th width="40">ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Phone</th>
            <th>Address</th>
            <th width="100">Action</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

Next make it to the script.js file or it can be in the script tag a viewData () function to call data from php.

$('table').dataTable();
viewData();
$('#update').prop("disabled",true);

function viewData(){
    $.get('server.php', function(data){
        $('tbody').html(data)
    })
}

Then on the server.php file make the execution to call data from the database record.

try{
        $stmt = $dbh->prepare("SELECT * FROM crud ORDER BY id DESC");
        $stmt->execute();
        while($row = $stmt->fetch()){
            print "<tr>";
            print "<td>".$row['id']."</td>";
            print "<td>".$row['name']."</td>";
            print "<td>".$row['email']."</td>";
            print "<td>".$row['phone']."</td>";
            print "<td>".$row['address']."</td>";
            print "<td class='text-center'><div class='btn-group' role='group' aria-label='group-".$row['id']."'>";
            ?> 
            <button onclick="editData('<?php echo $row['id'] ?>','<?php echo $row['name'] ?>','<?php echo $row['email'] ?>','<?php echo $row['phone'] ?>','<?php echo $row['address'] ?>')" class='btn btn-warning'>Edit</button>
            <button onclick="removeConfirm('<?php echo $row['id'] ?>')" class='btn btn-danger'>Trash</button>
            <?php 
            print "</div></td>";
            print "</tr>";
        }
    } catch(PDOException $e){
        print "Error!: " . $e->getMessage() . "<br/>";
 }

Edit/Update Data

Edit data is a function of adding data by id to the input form, the data added comes from the table or from the database, whereas, update the data is to change the data from the input form and then change the data in the database record, to make the input form similar to the form Add / Create data just add below save button and to edit button already exist in Read / View Data.

<button type="button" id="update" class="btn btn-warning" onclick="updateData()">Update</button>

Then create the editData() and updateData() function, the editData() function only displays the data into the input form, updateData() function is the data execution button that will be sent to php.

function editData(id, nm, em, hp, ad) {
    $('#id').val(id)
    $('#nm').val(nm)
    $('#em').val(em)
    $('#hp').val(hp)
    $('#ad').val(ad)
    $('#id').prop("readonly",true);
    $('#save').prop("disabled",true);
    $('#update').prop("disabled",false);
}

function updateData(){
    var id = $('#id').val()
    var name = $('#nm').val()
    var email = $('#em').val()
    var phone = $('#hp').val()
    var address = $('#ad').val()
    $.post('server.php?p=update', {id:id, nm:name, em:email, hp:phone, ad:address}, function(data){
        viewData()
        $('#id').val(' ')
        $('#nm').val(' ')
        $('#em').val(' ')
        $('#hp').val(' ')
        $('#ad').val(' ')
        $('#id').prop("readonly",false);
        $('#save').prop("disabled",false);
        $('#update').prop("disabled",true);
    })
}

Then change the data in the database with the following command code.

try{
        $id = $_POST['id'];
        $nm = $_POST['nm'];
        $em = $_POST['em'];
        $hp = $_POST['hp'];
        $ad = $_POST['ad'];
        $stmt = $dbh->prepare("UPDATE crud SET name=?, email=?, phone=?, address=? WHERE id=?");
        $stmt->bindParam(1,$nm);
        $stmt->bindParam(2,$em);
        $stmt->bindParam(3,$hp);
        $stmt->bindParam(4,$ad);
        $stmt->bindParam(5,$id);
        if($stmt->execute()){
            print "<div class='alert alert-success' role='alert'>Data has been updated</div>";
        } else{
            print "<div class='alert alert-danger' role='alert'>Failed to update data</div>";
        }
    } catch(PDOException $e){
        print "Error!: " . $e->getMessage() . "<br/>";
}

Delete/Remove Data

In the delete data or remove data, we must create a confirmation button which means that every time the user will delete the data it will appear notification in the form of alerts or warnings to continue or cancel. For the function as below.

function deleteData(id){
    $.post('server.php?p=delete', {id:id}, function(data){
        viewData()
    })
}

function removeConfirm(id){
    var con = confirm('Are you sure, want to delete this data!');
    if(con=='1'){
        deleteData(id);
    }
}

Then when the delete button is clicked then immediately the data will be deleted from database without refresh page.

try{
        $id = $_POST['id'];
        $stmt = $dbh->prepare("DELETE FROM crud WHERE id=?");
        $stmt->bindParam(1,$id);
        if($stmt->execute()){
            print "<div class='alert alert-success' role='alert'>Data has been deleted</div>";
        } else{
            print "<div class='alert alert-danger' role='alert'>Failed to delete data</div>";
        }
} catch(PDOException $e){
        print "Error!: " . $e->getMessage() . "<br/>";
}

Thank you very much for visiting this website. Please download the source code below.

CRUD Ajax and PHP Without Refresh Page