Displaying data from MySQL without a page reload using Ajax

Displaying data from MySQL without reloading the page can be done with an on-click event which does an Ajax request that then fills elements with the returned data.

This is done with no page refreshing/resetting.

You will need jQuery and Bootstrap (this example styled with Bootstrap):

<link rel="stylesheet" href="https://write.corbpie.com/wp-content/litespeed/localres/aHR0cHM6Ly9jZG5qcy5jbG91ZGZsYXJlLmNvbS8=ajax/libs/twitter-bootstrap/4.5.3/css/bootstrap.min.css"/>
<script src="https://write.corbpie.com/wp-content/litespeed/localres/aHR0cHM6Ly9jZG5qcy5jbG91ZGZsYXJlLmNvbS8=ajax/libs/jquery/3.4.1/jquery.min.js"></script>

The MySQL table and data for this example:

user_id|result|when               |decimal|score
    260|     1|2019-06-18 10:11:03| 1.9800|94
    261|     0|2019-06-17 12:31:03| 1.1856|55
    262|     1|2019-06-14 06:28:03| 1.2673|61
    263|     1|2019-06-10 11:44:03| 1.6904|70
    264|     1|2019-06-09 17:21:08| 1.8655|88

Example 1; Basic one-row fetch

Each time the button is pressed the fetch.php file will be called and its data returned and assigned to elements.

The inline ajax script:

<script>
    jQuery(document).ready(function ($) {
        $("#btn_fetch").click(function (e) {
            e.preventDefault();
            $.ajax({
                type: "POST",
                url: "fetch.php",
                dataType: "json",
                success: function (result) {
                    $('#result').html(result);
                    document.getElementById("updated").innerHTML = result[0];
                    document.getElementById("id").innerHTML = result[1];
                    document.getElementById("result").innerHTML = result[2];
                    document.getElementById("score").innerHTML = result[3];
                },
                error: function (result) {
                    alert('error');
                }
            });
        });
    });
</script>

As the returned data is an array each HTML element is assigned its value with the array index.

fetch.php:

<?php
$db = new PDO('mysql:host=127.0.0.1;dbname=test_table;charset=utf8mb4', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$select = $db->prepare("SELECT `user_id`, `result`, `when`, `score` FROM `login_data` LIMIT 1;");
$select->execute();
$array = [];
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    $uid = $row['user_id'];
    $result = $row['result'];
    $when = $row['when'];
    $score = $row['score'];
    $array = array(date('g:i:s a D jS M'), $uid, $result, $when, $score);
}
echo json_encode($array);

This will return the id = 264 row as there is no where conditions in the query and has a limit of 1.

HTML file

<div class="container">
    <div class="row">
        <div class="col-12">
            <div class="card py-3">
                <div class="row text-center">
                    <div class="col-12 px-5">
                        <button type="button" class="btn btn-secondary px-5 mt-2" id="btn_fetch">Fetch</button>
                    </div>
                </div>
                <div class="row">
                    <div class="col-12 px-5">
                        <p><b>Id:</b> <span class="id" id="id"></span></p>
                        <p><b>Result:</b> <span class="result" id="result"></span></p>
                        <p><b>Score:</b> <span class="score" id="score"></span></p>
                        <p><b>Values fetched:</b> <span class="updated" id="updated"></span></p>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

 

ajax mysql output no page refresh

Example 2; Fetching with an input id

The first example returned data from a hardcoded value, now for a dynamic example that will return data from a query based on a number input element.

The input will be the user_id column. The differences now are a variable assigned from the input element and this is passed with the POST request as uid:

<script>
    jQuery(document).ready(function ($) {
        $("#btn_fetch").click(function (e) {
            var uid = $('input[name="user_id"]').val();
            e.preventDefault();
            $.ajax({
                type: "POST",
                data: {uid: uid},
                url: "fetch.php",
                dataType: "json",
                success: function (result) {
                    $('#result').html(result);
                    document.getElementById("updated").innerHTML = result[0];
                    document.getElementById("id").innerHTML = result[1];
                    document.getElementById("result").innerHTML = result[2];
                    document.getElementById("score").innerHTML = result[4];
                },
                error: function (result) {
                    alert('error');
                }
            });
        });
    });
</script>

The HTML with an input:

<div class="container">
    <div class="row">
        <div class="col-12">
            <div class="card py-3">
                <div class="row text-center">
                    <div class="col-12 px-5">
                        <input type="number" id="user_id" name="user_id" class="form-control" min="0" max="99999">
                        <button type="button" class="btn btn-secondary px-5 mt-2" id="btn_fetch">Fetch</button>
                    </div>
                </div>
                <div class="row">
                    <div class="col-12 px-5">
                        <p><b>Id:</b> <span class="id" id="id"></span></p>
                        <p><b>Result:</b> <span class="result" id="result"></span></p>
                        <p><b>Score:</b> <span class="score" id="score"></span></p>
                        <p><b>Values fetched:</b> <span class="updated" id="updated"></span></p>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

The fetch.php file now has a check for $_POST['uid'] this is passed into the MySQL PDO query:

<?php
$db = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8mb4', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (isset($_POST['uid'])){
    $uid = $_POST['uid'];
} else {
    $uid = 264;//Fallback id. Not proper handle of situation...
}
$select = $db->prepare("SELECT `user_id`, `result`, `when`, `score` FROM `login_data` WHERE `user_id` = ? LIMIT 1;");
$select->execute([$uid]);
$array = [];
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    $uid = $row['user_id'];
    $result = $row['result'];
    $when = $row['when'];
    $score = $row['score'];
    $array = array(date('g:i:s a D jS M'), $uid, $result, $when, $score);
}
echo json_encode($array);

Now the data will be for the user id in the input element:

ajax mysql output from input no page refresh ajax mysql output from input no page refresh 2