Refresh page if there is change in database

Question

I am trying to refresh my a page if there is a change in orderStatus from database using Ajax and PHP. I set the current orderStatus as predefined data and then use Ajax to get the current orderStatus from database and finally compare if they are not the same. I want to refresh the page if they are not the same.

PHP (autorefresh.php)

<?php

$orderId = $_POST["orderId"];

$query = "SELECT * FROM orderinhomeonlinecall WHERE orderId='$orderId'";
$result = mysqli_query($db, $query);

  while($row = mysqli_fetch_array($result))
  {
      $orderStatus = $row['orderStatus'];

      $data = array(
        'orderStatus'   => $orderStatus
       );
       echo json_encode($data);
}
?>

Javascript

<script type="text/javascript" >
var predefined_val = '<?php echo $orderStatus; ?>';// your predefined value.
$.document(ready(function(){
    setInterval(function(){
        $.ajax({
            type:"POST",
            url:"autorefresh.php", //put relative url here, script which will return php
            data:{orderId: <?php echo $orderId; ?>}, // if any you would like to post any data
            success:function(response){
                var data = response; // response data from your php script
                if(predefined_val !== data){
                    window.location.href=window.location.href;
                }
            }
        });                     
    },5000);// function will run every 5 seconds
}));

A change to the database cannot trigger a change to the page. You would have to periodically query the database using AJAX to compare the current status with the new status to make a change. — Jay Blanchard, Jan 06 '20 at 18:38
You are open to SQL injections. You should parameterize your query. — user3783243, Jan 06 '20 at 18:39
I'd write some AJAX and have it run every 5 seconds or so to fetch for updates. — Mustafa, Jan 06 '20 at 18:40
Use websockets instead of polling. The latter is an anti-pattern as it does not scale at all. — Rory McCrossan, Jan 06 '20 at 18:52
Thanks everyone, user3783243 and Dharman I'll handle the SQL Injection aspect later. — Louis Web Solutions, Jan 06 '20 at 19:48
https://stackoverflow.com/questions/7077770/window-location-href-and-window-open-methods-in-javascript Try `window.location = window.location.href;` — Amanjot Kaur, Jan 07 '20 at 09:58

score 0 · Answer 1 · edited Jan 06 '20 at 19:27

The below code should work, Need to mention dataType:"json" else use JSON.stringify(data) to parse response

<script type="text/javascript">
var predefined_val = '<?php echo $orderStatus; ?>';// your predefined value.
$(document).ready(function () {
    setInterval(function () {
        $.ajax({
            type: "POST",
            url: "autorefresh.php", //put relative url here, script which will return php
            data: {orderId: <?php echo $orderId; ?>}, // if any you would like to post any data
            dataType: "json",
            success: function (response) {
                var data = response; // response data from your php script
                if (predefined_val !== data.orderStatus) {
                    window.location.href = window.location.href;
                }
            }
        });
    }, 5000);// function will run every 5 seconds
});
</script>

Thanks for the help Ramasamy, however I have tried using your code but it still isn't reloading the page. — Louis Web Solutions, Jan 06 '20 at 19:39

score -2 · Accepted Answer · answered Jan 07 '20 at 09:52

I have tested this by creating two files(autorefresh.php,index.php) and test db with table and it is working for me. I think the below code would be helpful, If not please share you code, i will check and fix it.

autorefresh.php

// Create connection
$db = new mysqli("localhost", "root", "","test");

$orderId = $_POST["orderId"];

$query = "SELECT * FROM orderinhomeonlinecall WHERE orderId='$orderId'";

$result = mysqli_query($db, $query);

  while($row = mysqli_fetch_array($result))
  {
      $orderStatus = $row['orderStatus'];

      $data = array(
        'orderStatus'   => $orderStatus
       );
       echo json_encode($data);
}
?>

index.php

<?php
$orderStatus ='pending';
$orderId =1;
?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script type="text/javascript">
var predefined_val = '<?php echo $orderStatus; ?>';// your predefined value.
$(document).ready(function () {
    setInterval(function () {
        $.ajax({
            type: "POST",
            url: "autorefresh.php", //put relative url here, script which will return php
            data: {orderId: <?php echo $orderId; ?>}, // if any you would like to post any data
            dataType: "json",
            success: function (response) {
                var data = response; // response data from your php script
                if (predefined_val !== data.orderStatus) {
                    window.location.href = window.location.href;
                }
            }
        });
    }, 5000);// function will run every 5 seconds
});
</script>

It still ins't working. I tried to inspect the page and noticed that the ajax request isn't working on the page. I tried it in a separate folder, it's the same result. I have another ajax request that's working and I can see it when I inspect the page. How do I post my code please? The comment box isn't allowing me. — Louis Web Solutions, Jan 07 '20 at 10:55
Hi Ramasamy, I was able to get it to work by fixing the error on the page. I had a session file on top of the page that wasn't calling the right directory. But there's still one problem, the page keeps refreshing even when there is no database update. I checked the autorefresh.php file and it's returning the current orderstatus value. Why is the page refreshing every 5 mins even when there isn't db update? — Louis Web Solutions, Jan 07 '20 at 12:27
Thanks everyone and thanks Ramasamy. I finally got it to stop reloading continually by changing if (predefined_val !== data.dbstatus) to if (predefined_val != data.dbstatus). It's working fine now. Your answer is the right answer with just this little change. Thank you! — Louis Web Solutions, Jan 07 '20 at 12:52

Refresh page if there is change in database

2 Answers2