Long polling with database data?

Question

After a week of googling and search.I am hard to find even a single tutorial about long polling from a database table instead of from a flat text file named data.text. Currently, I write manually anything in data.text and it instantly appears in the browser.

This is the question: Long polling using database? is not answered properly even in StackOverflow. (I found a lot here but in vain.).Example of this is also here filemtime alternative for MySQL

How can I modify getdata.php to make it enable for fetching data from database?

 $sql=mysqli_query($database,"SELECT * FROM messages  where time>=$curr_date ORDER by      time DESC");
  while($row=mysqli_fetch_array($sql)){
    $messages=$row['messages'];
    $id=$row['id'];
    echo $messages;
  }

Messages table is as follows

    id     fro   to  mesg      time  status  last_modified  

---

I am here listing an example. In this example, three files are being used.

1. index.html
2. getdat.php
3. data.text

Is there any need to make a fourth file to get data from database(mysql)? if not, then what type of changes are necessary in the getdata.php or data.text to use dynamic data from database?

Here is my Javascript

<script type="text/javascript" charset="utf-8">

        var timestamp = null;

        function waitformsg() {
            $.ajax({
                type:"Post",
                url:"getdata.php?timestamp="+timestamp,
                async:true,
                cache:false,
                success:function(data) {
                    var json = eval('(' + data + ')');
                    if(json['msg'] != "") {
                        $("#messages").append(json['msg']);

                    }
                    timestamp = json["timestamp"];

                    setTimeout("waitformsg()", 1000);
                },
                error:function(XMLhttprequest, textstatus, errorthrown) {
                    alert("error:" + textstatus + "(" + errorthrown + ")");
                    setTimeout("waitformsg()", 15000);
                }




                });

        }
        $(document).ready(function() {

            waitformsg();
        });
    </script>

Here is the getdata.php file

<?php
include("../model/includes/classes.php");

$filename='data.php';

$lastmodif=isset($_GET['timestamp'])?$_GET['timestamp']:0;
$currentmodif=filemtime($filename);

while($currentmodif<=$lastmodif){
    usleep(10000);
    clearstatcache();
    $currentmodif=filemtime($filename);
}

$response=array();
$response['msg']=file_get_contents($filename);
$response['timestamp']=$currentmodif;
echo json_encode($response);
?>

Answer 1

I have done something very similar recently. I did use jQuery .ajax call instead of the generic XMLhttprequest but the idea is the same:

recentFunction(container, lastDate){
    var lastDate = "";

    return $.ajax({
        type: "POST",
        url: "getData.php",
        cache: false,
        data: { 'request': 'recent',
            'param': lastDate },
        dataType: "json",
        success: function(data){
            if(data != null){
                $.each(data, function(key, value){
                    if(key == 0){
                        lastDate = value['date_added'];
                    }
                    var html = "some html here";
                    // append html to dom element here
                                // and delete any old items here if needed
                });
            }
        },
        complete: function(){
            setTimeout(function(){recentFunction(container, lastDate)}, 7000);
        }
    });
}

in the getData.php file I have query with a where clause that gets any items from db that are more recent than last element. Default value for $lastDate is set to 0, so it returns all items if no date is submitted.

<?php

$lastDate = 0;
$recent = array();
$recentQuery = "SELECT id, date FROM someTable WHERE date > '" . $lastDate . "'";
$recentResults = $db->query($recentQuery);

while($r = $recentResults->fetch_array(MYSQLI_ASSOC)){
      $recentMovies[] = $r;
}

echo json_encode($recentMovies);

?>