Simple PHP CRUD Operations without Page Refresh jQuery Ajax MySQL

Spread the love

Project Name: – Simple PHP CRUD Operations without Page Refresh jQuery Ajax MySQL (Ajax TODO PHP & MYSQL Using jQuery)

Solution

  • Database Name – ajaxtodo
  • Table Name – details
  • 4 Field – id(int, auto_increment, primary), user(varchar, 255), address(varchar, 255), phone(varchar, 255)

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">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" />
    <title>AJAX Tutorial by HindiAlerts.com</title>
</head>

<body>
    <div class="container">
        <div class="row">
            <div class="col-md-12 d-flex justify-content-center">
                <form method="post" id="myform">
                    <input type="hidden" value="" name="id" id="dataid">
                    <div class="form-group">
                        <label for="name">Name</label>
                        <input id="name" class="form-control" type="text" name="name">
                    </div>
                    <div class="form-group">
                        <label for="address">Address</label>
                        <input id="address" class="form-control" type="text" name="address">
                    </div>
                    <div class="form-group">
                        <label for="phone">Phone</label>
                        <input id="phone" class="form-control" type="text" name="phone">
                    </div>
                    <button id="formbtn" class="btn btn-success btn-block" type="submit">Save Data</button>
                </form>
            </div>
            <div class="col-md-12">
                <h2 class="text-center">My Data</h2>
                <div id="table"></div>
            </div>
        </div>
    </div>
    </body>
    
    <!--add jquery-->
    <!--add bootstrap js-->
    <!--add ajax.js file-->

</html>

db.php

<?php

$conn = new mysqli('localhost', 'root', '', 'ajaxtodo');

if ($conn->connect_error) {
    die("Connection Failed");
}

insert.php

<?php
//including db.php file for database connection
include 'db.php';

//query to insert data
$query = "INSERT INTO details(name, address, phone) VALUES(?,?,?)";

//prepare statement
$stmt = $conn->prepare($query);

//binding parameter with string
$stmt->bind_param('sss', $_POST['name'], $_POST['address'], $_POST['phone']);

//execute 
if ($stmt->execute()) {
    echo "Data Added";
} else {
    echo "Error Occured";
}

show.php

<?php
//including db.php file for database connection
include 'db.php';

//query for selecting data from our database
$query = "SELECT * FROM details";

//execute query
$result = $conn->query($query);

//checking result that should be more than 0
if ($result->num_rows > 0) {
    //html code for output
    $opt = '<table class="table table-bordered">
    <tbody>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Address</th>
            <th>Phone</th>
            <th>Edit</th>
            <th>Delete</th>
        </tr>
    ';

    //looping our data till the last on our database
    while ($row = $result->fetch_assoc()) {
        $opt .= "<tr>
                    <td>{$row['id']}</td>
                    <td>{$row['name']}</td>
                    <td>{$row['address']}</td>
                    <td>{$row['phone']}</td>
                    <td><button type='button' class='btn btn-warning edit' id='" . $row['id'] . "'>Edit</button></td>
                    <td><button type='button' class='btn btn-danger delete' id='" . $row['id'] . "'>Delete</button></td>
                </tr>
                ";
    }
    $opt .= '</tbody>
        </table>';
    // echo our output that we can use in our index.php using ajax response
    echo $opt;
}

edit.php

<?php
//including db.php file for database connection
include "db.php";

//getting a data that is releted to id which we send by ajax while we click on button using on index.php
$q = "SELECT * FROM details WHERE id=" . $_POST['id'];

//execute query
$rst = $conn->query($q);

//checking result is more than 0 or not
if ($rst->num_rows > 0) {
    //looping data
    while ($row = $rst->fetch_assoc()) {
        //echo data into json data that we can parse into object in js
        echo json_encode($row);
    }
}

update.php

<?php

//including db.php file for database connection
include 'db.php';

// extract array into variable by using extract built in function
// how it work
// it convert index into a variable like $_POST['name'] will be $name or $_POST['address'] will be $address
extract($_POST);

//query to update data into our database
$q = "UPDATE details SET name='$name', address='$address', phone='$phone' WHERE id=" . $id;

//execute query
if ($conn->query($q)) {
    echo "Data Updated";
} else {
    echo "Unable to Update Data";
}

delete.php

<?php
//including db.php file for database connection
include 'db.php';

//query to delete data from database by id that we send from index.php via ajax.js
$query = "DELETE FROM details WHERE id=" . $_POST['id'] . " LIMIT 1";

//execute query
if ($conn->query($query)) {
    echo "data Deleted";
} else {
    echo "Unable to Delete Data" . $conn->error;
}

Now is the magic part – Ajax.js file

//inserting data
$(document).on('click', "#formbtn", function (e) {
    e.preventDefault();
    let data = {
        name: $('#name').val(),
        address: $('#address').val(),
        phone: $('#phone').val()
    }
    $.ajax({
        url: 'insert.php',
        method: "POST",
        data: data,
        success: function (res) {
            $('#name').val('');
            $('#address').val('');
            $('#phone').val('');
            load_data();
        }
    })
});
//Read Data
function load_data() {
    $.ajax({
        url: 'show.php',
        method: "GET",
        success: function (res, status) {
            if (status == "success") {
                $("#table").html(res);
            }
        }
    });


}
//calling load_data() for the load data from show.php as soon as document load....
load_data();

//edit get data
$(document).on('click', '.edit', function () {
    let id_value = $(this).attr('id');
    $.ajax({
        url: 'edit.php',
        method: "POST",
        data: { id: id_value },
        success: function (res) {
            //parsing data from json to object
            let data = $.parseJSON(res);
            $('#name').val(data.name);
            $('#address').val(data.address);
            $('#phone').val(data.phone);
            $('#dataid').val(data.id);
            $('#formbtn').text("Update Data").attr('id', "updatebtn").attr('type', '');
        }
    })
});


//update Data
$(document).on('click', "#updatebtn", function (e) {
    e.preventDefault();
    let data = {
        id: $('#dataid').val(),
        name: $('#name').val(),
        address: $('#address').val(),
        phone: $('#phone').val()
    }
    $.ajax({
        url: 'update.php',
        method: "POST",
        data: data,
        success: function (res) {
            $('#name').val('');
            $('#address').val('');
            $('#phone').val('');
            $('#updatebtn').text("Save Data").attr('id', "formbtn").attr('type', 'submit');
            load_data();
        }
    })
});

//delete data
$(document).on('click', '.delete', function () {
    let id_value = $(this).attr('id');
    $.ajax({
        url: 'delete.php',
        method: "POST",
        data: { id: id_value },
        success: function (res, status) {
            if (status == 'success') {
                load_data();
            }
        }
    })
});

This is only for learning purpose that you can understand easily. but you can make only one or two php file for making your project as small as possible. If you get any error or want to something new to learn share details below.

Other Important Topics

Be the first to comment

Leave a Reply

Your email address will not be published.


*