PHP loop HTML table for each user

Question

I have a PHP loop which draws a table based off a MYSQLi query. What i would like to do is create this table based on a userid (in my case the column called 'badgeid')

At the moment its creating one table

$sql =  "SELECT first_name,last_name,signintime,signouttime FROM `signin_entries` WHERE iscleaner ='YES' AND signindate = curdate()";
$list_visitors_result=mysqli_query($con,$sql);
$count_visitors = mysqli_num_rows($list_visitors_result);
if($count_visitors != 0) {
    while($row = mysqli_fetch_array($list_visitors_result)){
        $signintime = $row['signintime'];
        $signouttime = $row['signouttime'];
        $firstname = $row['first_name'];
        $lastname = $row['last_name'];
        echo " <tr><td>$firstname $lastname</td>
<td>$signintime</td>";
        if ($signouttime == ""){
            echo "<td>Not Signed Out Yet</td>";
        } else {
            echo "<td>$signouttime</td>";
        }
        $timeFirst  = strtotime(date("Y/m/d") . " " . $signintime);
        $timeSecond = strtotime(date("Y/m/d") ." " . $signouttime);
//below checks if th second time is less than the first time than it must be from the day before so add 24 hours eg (signin time 23:30:00 signout time 07:30:30 would be 08:00:30 difference)
        if ($timeSecond < $timeFirst)
        {
            $timeSecond = $timeSecond + 86400;
        }
        if ($signouttime == ""){
            echo "<td>Cleaner Has Not Signed Out Yet</td>";
        } else {
            $differenceInSeconds = $timeSecond - $timeFirst;
            echo "<td class='rowDataSd'>".converttime($differenceInSeconds)."</td>";
        }
        echo "</tr>";
    }
}
//below function converts the seconds difference into hh:mm:ss format to the nearest second
function converttime($seconds) {
    $t = round($seconds);
    return sprintf('%02d:%02d:%02d', ($t/3600),($t/60%60), $t%60);
}
echo "<tr><th></th><th></th><th></th><th>Total Time Worked</th><tr><td></td><td></td><td></td><td class='totalCol'>Total:</td></tr>";
?>
</table>
                </div>
              </div>
            </div>
          </div>
          </div>
        </section>
      </div>

Which is great but i really need a table per 'badgeid'

Then design it that way. Put the table start and end within your while loop — aynber, Jul 08 '19 at 13:48
@aynber I think that would create a table per work record (the table appears to be a time-in, time-out log) while OP wants a table per worker. — Patrick Q, Jul 08 '19 at 13:52
@Patrick Q Thats correct a table per worker is what im trying to achieve, i just cannot work out the logic to do this — Retroisbest, Jul 08 '19 at 13:53
@Retroisbest In your query, you have to `ORDER BY` your workerid (or whatever unique ID you haver per worker) and then within your loop, check if the current ID matches the one of the previous iteration. If it does not, close the current table and start a new one. — Patrick Q, Jul 08 '19 at 13:55
@Patrick Q Thankyou, thats pointed me in the right direction.... trying it out now! — Retroisbest, Jul 08 '19 at 14:00
Possible duplicate of [Separate MYSQL results into separate HTML Tables](https://stackoverflow.com/questions/41128562/separate-mysql-results-into-separate-html-tables) — Patrick Q, Jul 08 '19 at 14:00

score 1 · Accepted Answer · answered Jul 08 '19 at 13:58

You could do it like that:

$sql =  "SELECT first_name,last_name,signintime,signouttime FROM `signin_entries` WHERE iscleaner ='YES' AND signindate = curdate() ORDER BY badgeid ASC";
$list_visitors_result=mysqli_query($con, $sql);
$count_visitors = mysqli_num_rows($list_visitors_result);
if ($count_visitors != 0) {
    echo '<table>';
    $current_badgeid = '';
    while ($row = mysqli_fetch_array($list_visitors_result)) {
        if ($current_badgeid == '') {
            $current_badgeid = $row['badgeid']; //define if empty, for the first table
        }
        if($row['badgeid'] != $current_badgeid){
            echo '</table><table>';
            $current_badgeid = $row['badgeid'];
        }
        $signintime = $row['signintime'];
        $signouttime = $row['signouttime'];
        $firstname = $row['first_name'];
        $lastname = $row['last_name'];
        echo " <tr><td>$firstname $lastname</td><td>$signintime</td>";
        if ($signouttime == "") {
            echo "<td>Not Signed Out Yet</td>";
        } else {
            echo "<td>$signouttime</td>";
        }
        $timeFirst  = strtotime(date("Y/m/d") . " " . $signintime);
        $timeSecond = strtotime(date("Y/m/d") ." " . $signouttime);
        //below checks if th second time is less than the first time than it must be from the day before so add 24 hours eg (signin time 23:30:00 signout time 07:30:30 would be 08:00:30 difference)
        if ($timeSecond < $timeFirst) {
            $timeSecond = $timeSecond + 86400;
        }
        if ($signouttime == "") {
            echo "<td>Cleaner Has Not Signed Out Yet</td>";
        } else {
            $differenceInSeconds = $timeSecond - $timeFirst;
            echo "<td class='rowDataSd'>".converttime($differenceInSeconds)."</td>";
        }
        echo "</tr>";
    }
    echo '</table>';
}

First, you add ORDER BY badgeid ASC to your query. Then you open a table before the loop starts, and you define a $current_badgeid var. Each time the badgeid changes, you close and reopen a table.

PHP loop HTML table for each user

1 Answers1