4

I need to automatically refresh content from a mysql data table every 5 seconds, but showing only one distinct record at a time, going through every record in a endless loop.

I load news.php, that has this js :

<script type="text/javascript">
var auto_refresh = setInterval(function () {
    $('#canvas').load('content.php').fadein("medium");}, 5000);
     // refresh every 5 seconds
</script>

content.php has the db connection 

$query_Recordset5 = "SELECT * FROM news"; 
$Recordset5 = mysql_query($query_Recordset5, $connection) or die(mysql_error());
$row_Recordset5 = mysql_fetch_assoc($Recordset5);
$totalRows_Recordset5 = mysql_num_rows($Recordset5);

As well as the fields echoed to the page.

I understand that you would have to create a counter and bring back one different record everytime, but I am having a tough time with it.

Thanks

KNYX
  • 41
  • 4
  • `but showing only one distinct record at a time` What is this supposed to imply? Are you saying that you want to do `SELECT DISTINCT` so the rows you return are never `duplicates`? Or do you only want 1 row back from your query? or do you need something to handle the existing offset value so you don't get all records back always? Clarification is needed. – Ohgodwhy Nov 20 '15 at 18:16
  • 1
    If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Nov 20 '15 at 18:16
  • Is everyone to see the same record being displayed at the same time? – Professor Abronsius Nov 20 '15 at 18:23
  • Ohgodwhy - No. One record at a time going through the sequence. 1 row back each time, different than the previous. When records, end, begin again. – KNYX Nov 20 '15 at 19:24
  • RamRaider - No. the loop should be for each user. – KNYX Nov 20 '15 at 19:25
  • Why not keep the list of news items as a single list in memory that gets updated periodically and just loop over them as a user makes a request? If you have high number of users you will be making tons of unnecessary queries to the database – dreamwagon Nov 20 '15 at 21:24
  • jjhavokk - can you help and illustrate ? Thanks. – KNYX Nov 24 '15 at 11:27
  • @knyx Does the data in the news table get updated? Or is there more content being inserted into the table? Do the rows have an auto increment field? – DanceSC Nov 25 '15 at 21:19

1 Answers1

1

If your table has an auto increment field (say "id"). You start by passing page.php and id of 0, so it will grab the auto increment field greater than 0, and then you pass that fields ID back through jquery. When you send it a second time it will not be included because you will be using the greater than sign.

The if num_rows == 0 checks to see if there are any fields, if none, then it will assume that the auto increment field you sent it is the last one, and then it will run the sql statement with the very first auto increment value.

<?php
// page.php
$id = (int) $_REQUEST['id'];
$sq = "select * from news where id > ".$id." order by id asc limit 0,1";
$qu = $con->query($sq);

if ($qu->num_rows == 0) {
    $sq2 = "select * from news order by id asc limit 0,1";
    $qu2 = $con->query($s2);
    while ($fe = $qu->fetch_assoc()) {
       echo $fe['id']."|".$fe['content'];
    }
} else {
    while ($fe = $qu->fetch_assoc()) {
       echo $fe['id']."|".$fe['content'];
    }
}
?>

<script>
$(document).ready(function() {
    setInterval(function(){ updateNews(); }, 5000);
});
function updateNews() {
    var id = 0;
    id = $("#hidden-id").val();        

    $.get("page.php?id=" + id, function(data) {
       // I use $.get so that I can split the data that it returns before populating 
       // the #canvas. This way we can strip off the first part which is the auto 
       // increment
       var ref = data.split('|');
       $("#hidden-id").val(ref[0]);
       $("#canvas").html(ref[1]);
    });
}
</script>
DanceSC
  • 521
  • 1
  • 4
  • 14