Page 1 of 1

Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 2:19 pm
by Chrisaster
I'm having trouble creating a page that retrieves data from a SQL database every X seconds and I thought this would be a good place to ask.. This is the code I have so far:

Code: Select all

<html>
    <body>
        <meta http-equiv="refresh" content="5; URL=log.php">
        
        <?php
            if (isset($_COOKIE["user"]))
            {
                $host = -snip-
                $database = -snip-
                $user = -snip
                $password = -snip-
                
                $sqlhandle = mysql_connect($host, $user, $password) or die("Could not connect to server.");     
                $selected = mysql_select_db($database, $sqlhandle) or die("Could not connect to the database: '".$database."'.");
                $history = "SELECT * FROM Chat";
                $result = mysql_query($history) or die("Query failed.");
                
                $storeres = array();
                
                $amount = mysql_num_rows($result);
                $eleval = $amount;
                
                while($data = mysql_fetch_array($result))
                {
                    $storeres[$eleval]["User"] = $data["User"];
                    $storeres[$eleval]["Message"] = $data["Message"];
                    $eleval = $eleval - 1;
                }
                            
                for ($i=0;$i <= $amount;$i=$i+1)
                {
                    if ( ($storeres[$i]["User"] <> NULL) and ($storeres[$i]["Message"] <> NULL) )
                    {
                        print("<b>".$storeres[$i]["User"].":</b> ");
                        print($storeres[$i]["Message"]."<br/>");
                    }
                }
                
                //Newest message at the bottom
                
                /*while($data = mysql_fetch_array($result))
                {
                    if ( ($data["User"] <> NULL) and ($data["Message"] <> NULL) )
                    {
                        print("<b>".$data["User"].":</b> ".$data["Message"]."<br/>");
                    }
                }*/
            }
        ?>
        
    </body>
</html>
Although this works (Meta tag refreshes it every 5 seconds), I've found it to be very annoying.. Especially with a browser such as Internet Explorer which 'clicks' each refresh. I am using this page in an iframe on the main page and I need the refreshing to be discreet to the user, any ideas?

Re: Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 2:21 pm
by crazycoders
I'm not a huge fan of AJAX and didn't use much but this technology (AJAX) is definitely what you need. A few good AJAX libraries are scriptaculous (i think) and prototype! Look them up on the web.

Re: Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 4:09 pm
by Chrisaster
crazycoders wrote:I'm not a huge fan of AJAX and didn't use much but this technology (AJAX) is definitely what you need. A few good AJAX libraries are scriptaculous (i think) and prototype! Look them up on the web.
I just gave AJAX a go and ended up with this:

Code: Select all

 
<html>
    <script type="text/javascript">
        function readLog()
        {
            var xmlhttp;
            
            if (window.XMLHttpRequest)
            {
                xmlhttp = new XMLHttpRequest();
            }
            else if (window.ActiveXObject)
            {
                xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
            }
            else
            {
                alert("Your browser does not support XMLHTTP!");
            }
            
            xmlhttp.onreadystatechange = function()
            {
                if (xmlhttp.readyState == 4)
                {
                    document.getElementById("chatlog").innerHTML = xmlhttp.responseText;
                }
            }
            
            xmlhttp.open("GET", "log.php", true);
            xmlhttp.send(null);
            
            setTimeout("readLog()", 500);
        }
    </script>
    
    <body onLoad="readLog()">
        <div id="chatlog">Log</div>
    </body>
</html>
..which didn't seem to work. Can anyone explain to me what i'm doing wrong? (I've never really used AJAX before)

Re: Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 4:21 pm
by Darhazer
At first sight your AJAX code looks right. I didn't test it. But given the fact that the meta refresh also didn't work, I guess you are running in a caching problem... your browser just cache the result and because of this do not update the information. You have either send cache-control headers or when calling the URL, use a random parameter name and/or value, which PHP file will ignore, but the browser will think that it loads different page.

Hope this will help you!

Re: Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 4:23 pm
by Chrisaster
Darhazer wrote:At first sight your AJAX code looks right. I didn't test it. But given the fact that the meta refresh also didn't work, I guess you are running in a caching problem... your browser just cache the result and because of this do not update the information. You have either send cache-control headers or when calling the URL, use a random parameter name and/or value, which PHP file will ignore, but the browser will think that it loads different page.

Hope this will help you!
The meta refresh did work but like I said in the first post, it's very annoying with browsers like Internet Explorer.

Re: Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 5:14 pm
by Chrisaster
Turns out my site was just playing up when I tested it. It's all working fine now, thanks for introducing me to AJAX!

Re: Updating data from SQL server every X seconds

Posted: Fri May 15, 2009 5:16 pm
by Darhazer
I just tried your code at my hosting, it works:
http://it-light.net/test.php
My log.php file is this:

Code: Select all

echo rand();
So what's the problem?

Edit:
Sorry, I just saw that your problem is solved.