How to fetch data with same id in a row but different value in another column and display one month into table in php

Question

I have the following data in MySQL table:

emp_id	date	shift
1001	2022-08-01	M
1001	2022-08-02	M
1001	2022-08-03	N
1002	2022-08-01	M
1002	2022-08-02	E
1002	2022-08-06	M

Output should be like this (One Month list show):

emp_id	2022-08-01	2022-08-02	2022-08-03	2022-08-04	2022-08-05	2022-08-06
1001	M	M	N	0	0	0
1002	M	E	0	0	0	M

My code is:

 <?php
for($j = 1; $j <=  date('t'); $j++)
{
$dat = str_pad($j, 2, '0', STR_PAD_LEFT). "-" . date('m') . "-" . 
date('Y');
$d[] = date('Y'). "-" . date('m') . "-" . str_pad($j, 2, '0', 
STR_PAD_LEFT);
$datess[] = str_pad($j, 2, '0', STR_PAD_LEFT)."<br>".date('D', 
strtotime($dat));
}
?>
<?php 
$link = mysqli_connect('localhost','root','','rostertest');
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>

<div class="table-responsive">          
  <table class="table">
    <thead>
      <tr>
<th>#</th>
<th>Employee</th>
<?php foreach($datess as $dates) {?>
<th>
    <?php echo $dates; ?>
</th>
<?php } ?>
</tr>
    </thead>
    <tbody id="tbl_body">
                
                <?php
                $i=0;
                $get_sql = "select * from ros";
                $run = mysqli_query($link,$get_sql);
                while($row = mysqli_fetch_array($run)){
                $date = $row['date'];
                $i++;
                ?>
                    <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['emp_id']; ?></td> 
                    <?php foreach($d as $da) { if($date==$da){?>
                    <td><?php echo $row['shift']; ?></td>
                    <?php }else{ ?>
                    <td><?php echo "0";?></td>
                    <?php }}?>
                    
                </tr>
                <?php }?>
            </tbody>
     </table>
    </div>

score -3 · Answer 1 · answered Aug 20 '22 at 09:20

for these kind of data you need to have one other Table, for example Table.emp_date with a relation between (emp_id from ros) and (id from emp_date) and you have to move 'emp_date' and 'shift' to this new table.

let me try to explain with your code too:

  <?php
            $i=0;
            $get_sql = "select * from ros";
            $run = mysqli_query($link,$get_sql);
            while($row = mysqli_fetch_array($run)){
                $get_sql2 = "select * from emp_date WHERE id=" . $row['emp_id'];
                $run2 = mysqli_query($link,$get_sql2);
                while($row2 = mysqli_fetch_array($run2)){
                   $i++;
                       ?>
                    <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['emp_id']; ?></td> 
                    <td><?php echo $row2['emp_date']; ?></td> 
                    <td><?php echo $row2['shift']; ?></td>
                    </tr>
              <?php }}?>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Aug 20 '22 at 12:16

How to fetch data with same id in a row but different value in another column and display one month into table in php

1 Answers1