2

The first image are my records in my database, 'enr_biometrics_id' is a user_id i just name it like that. nevermind the 'log_no'

datatable datatable

Now, I want to display the records of enr_biometrics_id = '000000001' from date 2021-07-01 - 2021-07-15, just like the image below using and display blanks on no record on the date 2021-07-01 - 2021-07-15. Thanks for the help in advance!

2nd image

I have a code right now:

    <?php include('config/config.php'); ?>
<table border="1">
<tr>
    <th>Log Date</th>
    <th>Logs</th>
    <th>Logs</th>
    <th>Logs</th>
    <th>Logs</th>
    <th>Logs</th>
</tr>
<?php
$start_period = "2021-07-01";
$end_period = "2021-07-15";

date_default_timezone_set('UTC');

while (strtotime($start_period) <= strtotime($end_period)) {
    
?>
<tr>
    <td><?php echo "$start_period"; ?></td>
    <?php
    $logs_query = mysqli_query($conn, "SELECT * FROM tb_daily_time_record WHERE date_added BETWEEN '$start_period' AND '$end_period' AND enr_biometrics_id='000000001' ")or(die(mysql_error()));
        
            while($row_logs = mysqli_fetch_assoc($logs_query)){
                $date_logs = $row_logs['date_added'];
                $t_logs = $row_logs['time_log'];
    ?>
        <td>
        <?php 
            echo $t_logs;
        ?>
    </td>
    <?php 
        }
    ?>
</tr>
<?php 
 $start_period = date ("Y/m/d", strtotime("+1 days", strtotime($start_period)));
}
?>
<tr>
</table>

Getting a result like this MyCoderightnow

Zeus
  • 21
  • 2
  • What exactly your problem? Get data from DB? Build HTML? Something else? – Slava Rozhnev Jul 26 '21 at 08:01
  • Show us the code you've tried, and describe in detail what problems you are having with it. – droopsnoot Jul 26 '21 at 08:06
  • displaying the records to with that format using the records from the db above.
     – Zeus Jul 26 '21 at 08:08
  • That's a requirement, not a problem. What have you tried? Where are you stuck, exactly? We're happy to help people here but it's not a free write-my-code service. Please demonstrate that you have made some effort of your own before asking for the free time of others. Also please take the [tour] and read [ask] so you know more about how stackoverflow works. – ADyson Jul 26 '21 at 08:11
  • P.s. it's a bit of an odd output format anyway if you ask me, if one day has many logs compared to the others then you end up with a lot of wasted space like in your picture. There is likely to be a more user-friendly way to present this information. – ADyson Jul 26 '21 at 08:13
  • 2
    I just edited my questions above guys. Sorry earlier Im a newbie here.. also MAX Logs is 5 columns – Zeus Jul 26 '21 at 08:25
  • Thanks. Side note: `mysql_error()` isn't going to do anything if there's an error. That's from the old mysql library. You'd need `mysqli_error($conn)` for it to be useful. But there's a better way to handle mysqli errors anyway - see [mysqli or die, does it have to die?](https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die) for more information. – ADyson Jul 26 '21 at 08:27
  • Anyway your code simply isn't checking whether the row you're reading from the database has a date which matches the row in the table. You need to compare the dates before allowing it to output anything. – ADyson Jul 26 '21 at 08:28

1 Answers1

1

This would be my workaround on the information you are giving. It's probably posible to optimise thise but it gives a good idea of a solution.

Short sum, i check if the current row has the same string as the row before, if this isnt the case i create a new row

$result = 'SELECT * FROM table'; // query .. 

$checkdate='';

echo '<table>';
foreach ($result as $row) {
  if ($row['date_added'] != $checkdate) {
    $checkdate = $row['date_added'];

    echo '</tr>
    <tr>
    <td>'.$row['time_log'].'</td>
    <td>'.$row['time_log'].'</td>';
    continue;
  }
  echo '<td>'.$row['time_log'].'</td>';
} 
echo '</tr></table>';
Kaede
  • 198
  • 1
  • 11