Php notification alert when new record is inserted
Tags: php, mysql
Published: 2022-03-17 10:32:28

Problem:
My small project requires me to monitor my sales database using PHP.
The page must monitor any new entries of the database so that I will be able to respond to the sales request.
If it is possible, it is a minute-per-minute update. My code is working fine except that I need to refresh every minute.
It is so cumbersome because I'm still attending to the current customer and I still need to refresh my page.
Is there any way that I can have fresh sales information through my page was not being refreshed?

Here's my code.

<?php

    $sales = '';
    // Set the query String
    $sql = "SELECT * FROM $tblname where `status`= 1";
    
    // Reading the Specific Record
    $query = $conn->query($sql);

    // Check if record exist
    if ($query->num_rows > 0) {
        // Loop through the retrieve records
        while($record = $query->fetch_assoc()) {                
    
            $sales .= '
            <div>Customer: '.$record["customer"]. 'date: '.$record["date"].' Transaction #: '.$record["transaction "].'  </div>';                            
        }
    }
?>

<!DOCTYPE html>
<html>
<head>
    <title>Sales Monitoring</title>
</head>
<body>

    <div id="main">
        <div id="activity"></div>
        <div id="notification"><?php echo $sales; ?></div>
    </div>
</body>
</html>
Thanks

Solution:
You can employ javascript using ajax call. By using the ajax call and setting the interval to 1 minute, then you will have fresh sales information every minute. First, your PHP code above can be transferred to another PHP file that our ajax will call. We call it "getnewTransaction.php".

<?php

    $sales = '';
    // Set the query String
    $sql = "SELECT * FROM $tblname where `status`= 1";
    
    // Reading the Specific Record
    $query = $conn->query($sql);

    // Check if record exist
    if ($query->num_rows > 0) {
        // Loop through the retrieve records
        while($record = $query->fetch_assoc()) {                
    
            $sales .= '
            <div>Customer: '.$record["customer"]. 'date: '.$record["date"].' Transaction #: '.$record["transaction "].'  </div>';                            
        }
    }

    // Return this value to the main page
    echo $sales;
?>
Only the echo $sales are added to your code since it is working fine. Once this is done, we are ready to make the ajax call. So here's the javascript:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

<script>    

    // seconds * milliSeconds
      var minuteMS = 60 * 1000;

    // Make sure that the page is already loaded
    $(document).ready(function() {
        // Set the interval to 1 minute
        window.setInterval(function() {
            getInfo();
        }, minuteMS);
    });

    function getInfo() {
        //make the ajax call
        $.ajax({
            url: 'getnewTransaction.php',
            type: 'POST',
            data: {},
            success: function(data) {
                document.getElementById("notification").innerHTML = data;    
            },
            error: function (request, status, error) {
                alert(request.responseText);
            }
        });
    }
    
</script>
There you go, take note of the minuteMS variable. You can change your refresh interval by changing the number of seconds that you want. This variable helps you if you decide on a much higher or lower refresh interval.

Happy Coding!!