How can I use this method to loop through a mysql database table

Question

How can I use this method to loop through a mysql database table and display a timer for each record in the table based on a date/time stored in one of the fields?

$sql = "SELECT * FROM post";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
$Row = (mysqli_fetch_assoc($result));
$th = $Row['endtime'];    
}



var today = new Date();
var DD = today.getDate();
var MM = today.getMonth()+1; //January is 0!
var YYYY = today.getFullYear();
//let get the Difference in Sec btw the two dates
var _DateFromDBProgEndDate = '<?php echo $th; ?>';
var ProgEndTime = new Date(_DateFromDBProgEndDate);
var TodayTime = new Date();

var differenceTravel = ProgEndTime.getTime()- TodayTime.getTime() ;
var seconds = Math.floor((differenceTravel) / (1000));
////////////////////////////////
var SecDiffFromToday = seconds;
var seconds = SecDiffFromToday;
function timer() {
    var days        = Math.floor(seconds/24/60/60);
    var hoursLeft   = Math.floor((seconds) - (days*86400));
    var hours       = Math.floor(hoursLeft/3600);
    var minutesLeft = Math.floor((hoursLeft) - (hours*3600));
    var minutes     = Math.floor(minutesLeft/60);
    var remainingSeconds = seconds % 60;
    if (remainingSeconds < 10) {
        remainingSeconds = "0" + remainingSeconds; 
    }
    document.getElementById('countdown').innerHTML = days + ":" + hours + ":" + minutes + ":" + remainingSeconds;
    if (seconds == 0) {
        clearInterval(countdownTimer);
        document.getElementById('countdown').innerHTML = "Completed";
    } else {
        seconds--;
    }
}
var countdownTimer = setInterval('timer()', 1000);

and this is my javascript

Answer 1

Here is a possible solution. It collects the dates from the database in an array:

PHP

<?php
$sql = "SELECT * FROM post";
$result = mysqli_query($conn, $sql);
$th = [];
while ($row = mysqli_fetch_assoc($result)) {
    $th[] = "'" . date('U', $row['endtime']) . "'";
}
?>

Then in the part where you generate the javascript, you use this $th variable to populate an javascript array:

var _DateFromDBProgEndDate = [<?=implode(",", $th)?>];

The javascript part then continues as follows:

Javascript

function pad(n) {
   // utility function to pad a number to 2 digits:
   return ('0' + n).substr(-2); 
}

function timer() {
    var todaySeconds = Math.floor(new Date().getTime() / 1000);
    // collect the html first in an array
    var html = [];
    // keep track whether there are still counters not yet zero:
    var allDone = true;
    // go through the list of dates:
    endDatesInSeconds.forEach(function (endDateSeconds) {
        var totalSeconds = endDateSeconds - todaySeconds;
        var days        = Math.floor(totalSeconds/24/60/60);
        var seconds     = Math.floor(totalSeconds - days*86400);
        var hours       = Math.floor(seconds/3600);
        seconds         = Math.floor(seconds - hours*3600);
        var minutes     = Math.floor(seconds/60);
        seconds         = seconds % 60;
        // add a part of html
        html.push(
            totalSeconds <= 0 
               ? 'Completed'
               : days + ":" + pad(hours) + ":" + pad(minutes) + ":" + pad(seconds));
        // when at least one counter is not complete, we are not done:
        allDone = allDone && totalSeconds <= 0;
    });
    // now put the html in the document:
    document.getElementById('countdown').innerHTML = html.join('<br/>');
    if (allDone) {
        clearInterval(countdownTimer);
    }
}
var countdownTimer = setInterval('timer()', 250);

Here is a fiddle, which does not have the php part, but uses four dummy times to work with.

A few notes about your original code:

• it is dangerous to test for seconds == 0 as there is no guarantee that the timer will tick exactly every second, and there is no guarantee that your times returned by the database are not already in the past. In both cases you could get negative seconds, and your timer would in fact never stop.
• You decreased the seconds variable with one, but as it is not guaranteed that the timer will tick exactly every second, you will slip away from the actual time difference. Therefore it is better to always get the current time again, and calculate the difference again. I my answer I let the timer tick more often, to get more exact results.
• You had some code to pad the seconds part with a leading zero, but you'll want that too for minutes and maybe hours.