PHP constant SQL query

Question

I need to constantly read from my database every 1 second to get the latest values. Here is my code:

    <?php
    // connect to the "tests" database
    $conn = new mysqli('localhost', 'root','','Test');

    // check connection
    if (mysqli_connect_errno()) {
     exit('Connect failed: '. mysqli_connect_error());
    }

    // SELECT sql query
   $sql = "SELECT * FROM `Value`"; 

   // perform the query and store the result
   $result = $conn->query($sql);

   if (!$result)
    print 'Error!';

   // if the $result contains at least one row
   if ($result->num_rows > 0) {
    // output data of each row from $result
     while($row = $result->fetch_assoc()) {
      $output=$row;
    print_r($output);
     }
   } 
   else {
    print '0 results';
   }

   $conn->close();
   ?>

My HTML code refreshes every 1 second as follows:

function reload (){
    setInterval(getData,1000);
}

function getData()
  {
    $.get('test.php', function(data) {

        var output = data;

        document.getElementById("output").innerHTML = "Info: " + output;
    }); 
  }
....
<body onload="reload();">

<p id="output"></p>

</body>

Everything works fine but after around 5-10 mins the MYSQL server crashes. I'm assuming it is being overloaded. My thoughts are that I keep running the php script every time which connects each second. Am I doing this incorrectly? Anyone have any suggestions on a better implementation?

if you are connecting every second and disconnecting it might affect the performance,you can leave the connection open,it might help by some margin — Bhadra, Mar 27 '13 at 05:54
What operating system is this on? I suspect the Mysql server isn't really crashing, what do you see? — John Carter, Mar 27 '13 at 06:03

score 2 · Answer 1 · answered Mar 27 '13 at 06:07

I think you are looking for something to have the database "unclosed" when the script has ended.

In mysqli you can prepend the hostname by adding p: to use a so called persistant database connection

// connect to the "tests" database
$conn = new mysqli('p:localhost', 'root','','Test');

Read more about persistant connections here: http://php.net/manual/en/features.persistent-connections.php

score 0 · Answer 2 · answered Mar 27 '13 at 06:00

You might try having it not start the next request until 1 second after the last one finishes by calling setTimeout() in the callback like so:

function getData()
  {
    $.get('test.php', function(data) {

        var output = data;

        document.getElementById("output").innerHTML = "Info: " + output;
        setTimeout(getData, 1000);
    }); 
  }
....
<body onload="getData();">

This is generally a better approach than using setInterval(), cuz you may end up having two concurrent connections and request A may start before request B, but may end after B, because something happened and it took more than a second to finish the request. This could cause some weird behavior.

This may also fix your issue, because maybe it fails because it ends up having several concurrent connections open from the same IP, etc.

score 0 · Answer 3 · edited May 23 '17 at 12:21

Yes it can be implement in a much better way. As you are using same Database connection configuration every-time, there is no need to connect and close database on page refresh.Connecting to database server every-second is very expensive call.

Why don't you just reload/refresh the query statement?

The idea is:

Use Persistent Database Connection

Refer BlaM answer in following post to know why persistent connections is optimal.

Put the queries in a separate div say #load.

      echo '<div id="load">';
// SELECT sql query
   $sql = "SELECT * FROM `Value`"; 

   // perform the query and store the result
   $result = $conn->query($sql);

   if (!$result)
    print 'Error!';

   // if the $result contains at least one row
   if ($result->num_rows > 0) {
    // output data of each row from $result
     while($row = $result->fetch_assoc()) {
      $output=$row;
    print_r($output);
     }
   } 
   else {
    print '0 results';
   }
       echo '</div>';

Use jquery function to refresh only #load div

<script type="text/javascript">
var auto_refresh = setInterval(
function ()
{
$("#load").load("filename.php #load");
}15000); // refresh every 1 second
</script>

I had implemented this for auto-refresh leaderboard page.It worked perfectly and server didn't crash even in 50 hrs.

This doesn't keep the MySQL connection alive. It still makes a new connection every refresh. bestprogrammerintheworld's answer is correct. — Paul, Mar 27 '13 at 06:40
Thank you @Paulpro. I realized my mistake and updated my answer. :) — Ritesh Kumar Gupta, Mar 27 '13 at 06:53

PHP constant SQL query

3 Answers3