long polling with PHP and AJAX... almost there

Question

I am working with a school project. The basic ide of the project is, that we have some arduino boxes the sends some sensor data to a mysql db and we have a website that display it. Sensor data is sending lets say every 6sec.

I don´t have a lot of experience with PHP. But i am tinkerin my way, learing step by step..cowboy style? =)

The html/ajax/css:

    <!DOCTYPE html>
<html>
<head>
    <title>Arduino event poller</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript" charset="utf-8"></script>

    <style type = "text/css" media="screen">
        body{ font:13px/1.5 "helvetica neue", helvetica, arial, san-serif; background:#FFF; }
        #main{ width:430px; height: 300px; display:block; padding:10px 0; float: left; overflow: auto;}
        .event { display:block; background: #ececec; width:380px; padding:10px; margin:10px; overflow:hidden; text-align: left; }  
        .event img { display:block; float:left; margin-right:10px; }  
        .event p { font-weight: bold; }
        .event img + p { display:inline; }
        .patient-name { display:inline; color: #999999; font-size: 9px; line-height:inherit; padding-left: 5px; }
        .event-text{ color: #999999; font-size: 12px; padding-left: 5px; }
        .event-timestamp{ color: #000; padding-left: 5px; font-size: 9px;}
    </style>

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

        var timeStamp = null;

        /* Simple helper to add some divs.*/
        function addevents(patientroom, patientname, eventtyp, timestamp)
        {
            $("#main").append(
                "<div class='event'>"
                "<p>" + patientroom + "</p>"
                "<p class='patient-name'>" + patientname + "</p>"
                "<p class='event-text'>" + eventtyp + "</p>"
                "<p class='event-timestamp'>" + timestamp + "</p>"
                "</div>"
                );
        }

        /*This requests the url "getevents.php" When it complete*/
        function waitForEvents()
        {
            $.ajax({
                type: "GET",
                url: "getevents.php?timeStamp=" + timeStamp,

                async: true, /* If set to non-async, browser shows page as "Loading.."*/
                cache: false,
                timeout:50000, /* Timeout in ms */

                success: function(data, textStatus, jqXHR) /* called when request to getevents.php completes */
                {
                    addevents(data.patientroom, data.patientname, data.eventtyp, data.timestamp);
                        setTimeout(
                          waitForEvents, /* Request next event */
                          1000 /* ..after 1 seconds */                   
                        );
                    },
                error: function (XMLHttpRequest, textStatus, errorThrown){
                    alert("Error:" + textStatus + " (" + errorThrown + ")");
                    setTimeout(
                        'waitForEvents()', /* Try again after.. */
                        "5000"); /* milliseconds (5seconds) */      
                },
            });
        };

        $(document).ready(function(){
                waitForEvents(); /* Start the inital request */
        });
    </script>
</head>
<body>
    <div id="main">
    </div>
</body>
</html>

My backend php:

<?php
function getEvents()
{
    $con = mysql_connect("localhost","***","***");
        if(!con)
        {
            die('Could not connect: ' . mysql_error());
        }
        mysql_select_db("arduino_db",$con);

    $result = mysql_query("SELECT * FROM events ORDER BY eventID DESC LIMIT 1");
    if($result)
    {
        $patientroom = $row['rumNr'];
        $patientname = $row['inneboendeNamn'];
        $eventtyp = $row['handelse'];
        $timestamp = $row['timestamp'];
    }

    if($row)
    {
        header('application/json');
                echo json_encode($row);
                exit;
    }

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

    while($currentmodif <= $lastmodif)
    {
        unsleepp(1000);
        clearstatcache();
        $currentmodif = filemtime($result);
    }
}

?>

My question:

How to I fetch each row from the db and return each row in JSON format to the method "waitForEvents" in the frontend.

The example doesn't have to be scaleable, secure or complete, it just needs to work =)

UPDATE: new code based on Johns tips. All I gets is a blank page, and no errors.

John Green · Accepted Answer · 2011-05-22T20:02:35.917

The first thing that popped out to me is that your MySQL call is sort of blown.

When you run this line:

$result = mysql_query("SELECT * FROM events ORDER BY eventID DESC LIMIT 1");

You're going to get a MySQL resource. You need to utilize that to get your row:

$result = mysql_query("SELECT * FROM events ORDER BY eventID DESC LIMIT 1");
if ($result)
{
   $row =  mysql_fetch_assoc($result);
   if ($row)
   {
        // Your result is here, as a big associative array.  Each column in your 
        // table is now keyed to this array.  Exact fields will depend on your DB.
        //
        // Just access it like something like this:

        $id = $row['id'];
        $time = $row['time_stamp'];

   }
}

to echo it back out as JSON:

... // snip
   if ($row)
   {
        header('application/json');
        echo json_encode($row);
        exit;
   }
}
// handle your errors!

added: Additional error found in OP question:

//  The following line isn't valid.  This isn't what you'll get back from $.ajax.
//    success: function(patientroom, patientname, eventtyp, timestamp) 

//  Corrected code:
success: function(data, textStatus, jqXHR) 
/* called when request to getevents.php completes */
{
    addevents(data.patientroom, data.patientname, data.eventtyp, data.timestamp);
    setTimeout(
      waitForEvents, /* Request next event */
      1000 /* ..after 1 seconds */                   
    );
},

Further updates. You mixed & matched the code from above.

$result = mysql_query("SELECT * FROM events ORDER BY eventID DESC LIMIT 1");
if($result)
{
   // this has to go inside of this check.  This is where you *ASSIGN* $row.
   $row =  mysql_fetch_assoc($result);

   // You need to rekey $row before you output:
   $retVal = array('patientroom'=>$row['rumNr'],
                   'patientname'=>$row['inneboendeNamn'],
                   'eventtyp'=>$row['handelse'],
                   'timestamp'=>$row['timestamp']);

   // I'm not sure what you're doing with the incoming timestamp.  Should we just
   // return it back out?
   $retVal['ajax_timestamp'] = $_GET['timeStamp'];

   header('application/json');
   echo json_encode($retVal);
   exit; // this exits. Comment this out if you want, but don't try to write anything else out to the buffer.
}

// Not sure what you're trying to do here.  I'll comment out for now.
/*
$lastmodif = isset($_GET['timeStamp']) ? $_GET['timeStamp'] : 0;
$currentmodif = filemtime($result);

while($currentmodif <= $lastmodif)
{
    unsleepp(1000);
    clearstatcache();
    $currentmodif = filemtime($result);
}
*/

}

See my updated answer. However... be wary. It is dangerous to do a SELECT * and return that to the user. — John Green, May 21 '11 at 23:59
But that doesnt send it back to method waitForEvents, it prints it out? — , May 22 '11 at 01:18
Sorry, you had a bug in your AJAX processor as well. My answer ammended with solution. — John Green, May 22 '11 at 01:22
Sorry for the late response, but it was like 03:35 my last post. But check the update code based on your tip. — , May 22 '11 at 15:51
Hi John, I tried the new updates, but it dosent work. Have you check the other treads on long-polling. Maybay you will understand them better. And have your tried it your self? the code, the updates? — , May 22 '11 at 23:38
Honestly, I think you might be missing one of the key tenets of long polling. The point here is that your data changes through some external action. All you're doing is moving the polling out of the Ajax and directly onto the server. Because you're keeping your connection open, you're not saving anything... in fact, you're probably creating MORE issues due to the large # of open requests. What you want to do is store some bit of information, whether it is a file or something in memcached, that will be updated on data insertion. I get long polling... I designed a social network. : ) — John Green, May 23 '11 at 03:09
You are right John. I know what you mean, I did an research before I beging coding this. — , May 23 '11 at 15:48

score 0 · Answer 2 · answered May 23 '11 at 16:02

I understand know that you shouldnt do long-polling with php. It's to messy if you aint a pro php-hacker and for many other reasons like:

PHP is made for fast execution (not for waiting)
PHP will force you to do some kind of polling on the server side and relying on sleep()
PHP will eat your RAM while so are spawning processes for each requests (Apache will do so)

The thing I need for my project is to show new data without refreshing the hole site. I did it like this:

<script type="text/javascript">
        var timeoutId;
        var intervalId;

        function doIt(){
            $("#main").load("refresh.php");
        }
        $(document).ready(function(){
            timeoutId = setTimeout(function(){
                doIt();
                intervalId = setInterval(function(){
                    doIt();
                }, 5000); //Request the doIt() method every 5ms.
            }, 3000); //Delay calculated on the server to trigger the function at the appropriate time
        }); 
    </script>

long polling with PHP and AJAX... almost there

2 Answers2