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);
?>

Relations

Welcome to Stack Overflow. It will help you get an answer if you clarify your question. It seems that you're creating a web page (also known as a client application in Javascript) that is supposed to use ajax to poll your server. It seems that you want the first ajax request to getdata.php to retrieve all the messages already stored in your table, and for subsequent requests to retrieve any new messages that have appeared since the most recent request. Is that correct? Also, please show the definition of your messages table. – Allayne

@OllieJones Thank u for your response.You seems to me a last hope for solving this query.Exactly.I am bulding chat application using long polling technique.Messaes table is just a standered chat table including id,to,from,message,time columns. – Relations

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);

?>

Trouvaille

thank you.But the problem is that where and how shell I place sql statements in getdada.php? – Relations

It really doesn't matter where you place it. Just execute the SQL query as you would normally, build an array from the data(possibly from multiple queries) and just output the resulting array. Last line would just be "echo json_encode($output);" I guess you could also output XML or just text if you wanted. – Trouvaille