Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

What I have tried:

<pre>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>
Posted
Updated 2-Sep-22 1:58am

1 solution

The technique you are looking for is called "pivoting" - it's not so easy in MySQL as others but there is a worked example here - Pivot Tables in MySQL[^]

To get your results exactly as you have shown them you will need to use IFNULL - MySQL :: MySQL 5.7 Reference Manual :: 12.5 Flow Control Functions[^]

Your expected results are mixing types 'E', 'M', 'N' then 0 (zero) so you will need to use something like IFNULL(`2022-08-02`,'0') etc i.e. the character '0' not the numeric 0
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900