How To Create Live Data Monitoring Page in PHP using jQuery and Ajax Tutorial

Submitted by oretnom23 on Friday, October 22, 2021 - 14:02.

In this tutorial, we will tackle about How to Create a Live Monitoring Page in PHP and JavaScript (jQuery and Ajax). A page which automaticaly updates the data without reloading or refreshing the page. This web application feature can be meet by several methods or techniques but in this tutorial I will be using JavaScript's setInterval() function to execute a certain function for a certain time interval. This function will excute an Ajax Request which gets the updated data from the database and display it without reloading the page using jQuery.

I will be providing a simple PHP Application source code below which is Counting Posts Views. The application will automatically update the number of views in each post shown every .5 seconds. The source code uses only dummy data and randomly updates the post's views count.

Getting Started

In this tutorial, I used Bootstrap v5 for the design of application. And, kindly download also the jQuery Library. After that, compile you CSS and Script assets into a directory. Then, open your XAMPP/WAMP's Control Panel and start Apache and MySQL.

Creating the Database

Open a browser and browse the PHPMyAdmin i.e. http://localhost/phpmyadmin. Next, create a new database naming dummy_data. Then, copy the SQL script below and paste in the SQL Tab's textfield and click the Go button.

        CREATE TABLE `authors` (
          `id` int(11) NOT NULL,
          `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
          `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
          `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
          `birthdate` date NOT NULL,
          `added` timestamp NOT NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
        INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES
        (1, 'Emily', 'Hegmann', '[email protected]', '1979-03-16', '1976-04-05 11:59:54'),
        (2, 'Miracle', 'Anderson', '[email protected]', '1975-02-02', '2006-04-13 11:34:40'),
        (3, 'Kendra', 'Raynor', '[email protected]', '1979-03-19', '1987-11-17 14:51:03'),
        (4, 'Simone', 'Huel', '[email protected]', '1971-09-30', '2006-05-13 21:56:04'),
        (5, 'Ena', 'Harris', '[email protected]', '2010-10-27', '2004-12-06 06:04:25'),
        (6, 'Emiliano', 'Kshlerin', '[email protected]', '1979-04-20', '2009-12-10 05:42:56'),
        (7, 'Jedediah', 'Hermiston', '[email protected]', '1976-08-01', '1997-09-06 03:35:39');
        CREATE TABLE `posts` (
          `id` int(11) NOT NULL,
          `author_id` int(11) NOT NULL,
          `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
          `description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
          `content` text COLLATE utf8_unicode_ci NOT NULL,
          `date` date NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
        INSERT INTO `posts` (`id`, `author_id`, `title`, `description`, `content`, `date`) VALUES
        (1, 1, 'Adipisci incidunt qui enim libero adipisci.', 'Commodi quo aspernatur neque quasi error. Sit doloribus porro tenetur vitae. Neque doloribus reprehenderit et et ullam. Ea dolores molestiae dignissimos sed expedita dolor.', 'Eos quaerat ullam eligendi earum. Laborum occaecati sunt qui amet et sequi. Culpa quia sequi non voluptates aut omnis quia. Culpa ex porro officia voluptatibus doloribus.', '1970-07-10'),
        (2, 2, 'Libero repellendus qui sit quis doloremque.', 'Modi accusamus ad totam facilis id rerum sit ipsa. Expedita labore sint velit laborum cupiditate in repudiandae.', 'Omnis et expedita sed. Qui est aut vitae quia ab suscipit. Maiores tempore et labore et inventore et. Exercitationem qui tempora rerum delectus ut facere eaque.', '1982-10-31'),
        (3, 3, 'Delectus voluptatem dolorem officiis quaerat assumenda.', 'Voluptatum architecto id aliquid est. Similique dolores commodi harum minima doloribus et.', 'Nemo accusamus qui autem eos. Tempora et dolore nemo facere.', '1999-01-29'),
        (4, 4, 'Excepturi voluptas saepe fugit aut saepe.', 'Laboriosam rerum aut quasi nulla. Vel atque quae fugit dolor aliquid. Voluptas ratione quod quod debitis quasi dicta. Ut ea iure est accusamus reiciendis.', 'Debitis numquam id molestiae expedita officiis. Amet natus repellendus voluptas alias. Magni ratione qui incidunt quod deleniti aut.', '1979-01-15'),
        (5, 5, 'Ab dolorem nobis ut sit voluptatem aperiam minus eum.', 'Magni aliquam dolor enim rerum numquam. Impedit in modi vitae facere vitae mollitia dolor consequatur. Quas itaque adipisci labore omnis iure aut quisquam possimus.', 'Et rem tempora expedita accusamus. Inventore placeat est quisquam illo porro. Corporis dicta maiores ipsa quibusdam optio.', '1981-10-06'),
        (6, 6, 'Deleniti ut provident voluptatum culpa magnam.', 'Ea magnam nihil iste minus ipsa occaecati tenetur distinctio. Aliquam mollitia nihil nihil itaque et. Laboriosam quas eveniet delectus dolore alias ipsa nesciunt. Eum repellendus optio aut illo qui quod laudantium similique. Molestiae deserunt minima harum accusamus ratione voluptates asperiores.', 'Voluptatem est aut sit et debitis ut iusto. Expedita quia facilis accusantium rerum. Accusamus quas vitae nam exercitationem aut. Quis perspiciatis commodi architecto ducimus voluptatem.', '1996-10-24'),
        (7, 7, 'Omnis voluptatibus fugiat cum velit quod nostrum est ex.', 'Natus temporibus doloribus rem consequatur sit dolores veniam. Vel vel adipisci aspernatur nesciunt omnis.', 'Ut perspiciatis ut aspernatur voluptatum. Numquam velit explicabo ipsam similique quod beatae saepe nulla. Mollitia accusantium aspernatur quaerat deserunt aperiam.', '2010-08-12');
 
 
        CREATE TABLE `views` (
          `id` int(30) NOT NULL,
          `post_id` int(11) NOT NULL,
          `view_count` int(30) NOT NULL DEFAULT 0
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
        INSERT INTO `views` (`id`, `post_id`, `view_count`) VALUES
        (1, 1, 6712),
        (2, 2, 6816),
        (3, 5, 6877),
        (4, 3, 6620),
        (5, 7, 7449),
        (6, 6, 6480),
        (7, 4, 6676);
        ALTER TABLE `authors`
          ADD PRIMARY KEY (`id`),
          ADD UNIQUE KEY `email` (`email`);
 
        ALTER TABLE `posts`
          ADD PRIMARY KEY (`id`);
        ALTER TABLE `views`
          ADD PRIMARY KEY (`id`);
        ALTER TABLE `authors`
          MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=101;
        ALTER TABLE `posts`
          MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=101;
        ALTER TABLE `views`
          MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

Creating the Database Connection

Next, we will be creating our database connection. In your source code folder, create a new PHP File naming connect.php. Copy/Paste the source code below and save the file. Make sure to configure your database connection string according to your database setup.

        <?php
        $host='localhost';
        $username="root";
        $pw= "";
        $db_name = "dummy_data";
 
        $conn = new mysqli($host, $username, $pw, $db_name);
 
        if(!$conn){
            die('Database Connection failed. Error: '.$conn->error);
        }

Creating the Interface

The script below the is the code for our Page Interface. The script queries all the posts listed in the database along with the author name and date published. The posts will be displayed with 0 counts of views by default. Save the script below as index.php

        <!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.0">
    <title>Live Monitoring in PHP and jQuery/Ajax</title>
    <link rel="stylesheet" href="./css/bootstrap.min.css">
    <!-- Custom CSS -->
    <script src="./js/jquery-3.6.0.min.js"></script>
    <script src="./js/bootstrap.min.js"></script>
    <script src="./js/script.js"></script>
    <style>
         :root {
            --bs-success-rgb: 71, 222, 152 !important;
        }
        
        html,
        body {
            height: 100%;
            width: 100%;
            font-family: Apple Chancery, cursive;
        }
        .fs-7 {
            font-size: .8rem!important;
        }
    </style>
</head>
 
<body class="bg-light">
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient" id="topNavBar">
        <div class="container">
            <a class="navbar-brand" href="https://sourcecodester.com">
            Sourcecodester
            </a>
        </div>
    </nav>
    <div class="container py-3" id="page-container">
        <small>How to create a Live Monitoring Page in PHP with jQuery/Ajax using <span class="text-primary">setInterval()</span></small>
        <hr>
        <h3><b>Contents Number of Views</b></h3>
        <div class="col-12">
            <div class="row row-cols-1 row-cols-sm-1 row-cols-md-2 row-cols-xl-3 gx-4 gy-4" id="posts">
                <?php 
                include('connect.php');
                $posts = $conn->query("SELECT p.*,concat(a.first_name,' ',a.last_name) as author FROM `posts` p inner join `authors` a on p.author_id = a.id order by unix_timestamp(p.date) desc ");
                while($row = $posts->fetch_array()):
                ?>
                <div class="col post-item">
                    <div class="card rounded-0 h-100">
                        <div class="card-body rounded-0">
                            <h5 class="card-title text-truncate" title="<?php echo $row['title'] ?>"><?php echo $row['title'] ?></h5>
                            <div class="w-100 d-flex justify-content-between">
                                <span class="text-muted fs-7"><?php echo ucwords($row['author']) ?></span>
                                <span class="text-muted fs-7"><?php echo date("M d, Y",strtotime($row['date'])) ?></span>
                            </div>
                            <div class="w-100 text-end mt-3">
                                <div class="fs-4 fw-bold views-count" data-id="<?php echo $row['id'] ?>">0</div>
                            </div>
                        </div>
                    </div>
                </div>
                <?php endwhile; ?>
            </div>
        </div>
    </div>
 
</body>
 
</html>

Creating the JavaScript Functions

Next, the following script is the JavaScript functions that updates the number of view of each views. It contains the Ajax Request and setInterval script. Save the file as script.js.

        var ids = {};
        var countInterval;
        $(function() {
            $('#posts .views-count').each(function() {
                ids[$(this).attr('data-id')] = $(this).text()
 
            })
            countInterval = setInterval(() => {
                update_count()
            }, 500)
            update_count()
        })
 
        function update_count() {
            ids = {}
            $('#posts .views-count').each(function() {
                ids[$(this).attr('data-id')] = $(this).text()
 
            })
            if (ids.length <= 0) {
                // Stop Interval if no post/content
                clearInterval(countInterval)
                return false;
            }
            $.ajax({
                url: 'get_count.php',
                method: 'POST',
                data: { ids: ids },
                dataType: 'json',
                error: err => {
                    console.log(err)
                    alert("An error occured while updating the content views")
                    clearInterval(countInterval)
                },
                success: function(resp) {
                    if (resp.length > 0) {
                        Object.keys(resp).map(k => {
                            $('#posts .views-count[data-id="' + resp[k].id + '"]').text(resp[k].count)
                        })
                        sort_element()
                    }
                }
            })
        }
 
        function sort_element() {
            var sorted = $($('#posts .post-item').toArray().sort(function(a, b) {
                var Aelement = a.getElementsByClassName('views-count')[0].innerText,
                    Belement = b.getElementsByClassName('views-count')[0].innerText;
                Aelement = parseFloat(Aelement.replace(/\,/gi, ''));
                Belement = parseFloat(Belement.replace(/\,/gi, ''));
                return Aelement - Belement;
            }))
 
            Object.keys(sorted).map(k => {
                if (typeof sorted[k] == 'object') {
                    var el = $(sorted[k]).clone()
                    $(sorted[k]).remove()
                    $('#posts').prepend(el)
                    el.show('slow')
                }
            })
        }

Creating the PHP API

Lastly, create a new PHP File naming get_count.php. This file will updates the view count of a randow post in the database then returns the updated view count as a JSON Data.

        <?php 
        require_once('connect.php');
        // Update View Count
        function update_view(){
            global $conn;
            $id = rand(1,7);
            $check = $conn->query("SELECT id from `views` where post_id ='{$id}'")->num_rows;
            $add_view = rand(1,99);
            if($check > 0){
                $conn->query("UPDATE `views` set `view_count` =  `view_count` + {$add_view}  where post_id ='{$id}'");
            }else{
                $conn->query("INSERT `views` set `view_count` =  {$add_view} , post_id ='{$id}'");
            }
        }
 
        $data = array();
        update_view();
        foreach($_POST['ids'] as $post_id => $value){
            $value=str_replace(',','',$value);
            // Sample Query
            $views = $conn->query("SELECT view_count from `views` where post_id ='{$post_id}'");
            if($views->num_rows > 0){
                $view_count = $views->fetch_array()['view_count'];
                if($value == $view_count)
                    $count = 0;
                else
                    $count =  $view_count;
            }else{
                $count = 0;
            }
 
            // Generate Randow Number for content view count
            // $count = mt_rand(0,99999999);
            if($count > 0)
            $data[] = array('id'=>$post_id, 'count'=>number_format($count));
        }
 
        echo json_encode($data);
 
        $conn->close();
 
        ?>

There you go! You can now test the simple Live Monitoring Web Application on your end. Browse the application in your browser and see if it is working according to plan. If you have encountered an error, kindly review your source code or download the working source code I created for this tutorial. The download button is located below this article.

DEMO VIDEO

That's the end of this tutorial. I hope this tutorial will help you and your future web application projects.

How To Create Live Data Monitoring Page in PHP using jQuery and Ajax Tutorial

Getting Started

Creating the Database

Creating the Database Connection

Creating the Interface

Creating the JavaScript Functions

Creating the PHP API

DEMO VIDEO

Happy Coding :)

Comments

Ajax

Add new comment