Solve the display duplicate in the table rows

Yong Scott
Yong Scott used Ask the Experts™
on
I want make a display Candy name only display icon tick in the column Candy without duplicate in column melvin and angel . But right now my data is duplicate over in every name column .
 
question1.JPGThis is my table display right now

      <div class="card mb-3">
        <div class="card-header">
          <i class="fa fa-table"></i> Calendar</div>
        <div class="card-body">
          <div class="table-responsive">
            <table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
              <thead>
			  <tr>
			  <th>Time</th>
			  <?php 
			  $namearr = array(); 
			  $sql="select * from admin_staff";
			  $result3= mysqli_query($DBcon,$sql);
			  while ($row3= $result3->fetch_array()){ 
			  ?>
			  <th><?php echo $row3['name']; }?></th>
			  </tr>
			  </thead>
			  <tbody>
			  <?php 
				
				$open_time = strtotime("9:00");
				$close_time = strtotime("21:00");
				
			  for($i=$open_time; $i<$close_time; $i+=3600){
			  ?>
			  <tr>
			  <td><?php echo date("l - H:i",$i); ?></td>

			  <?php 
			  $sql_count="select count(*) as counter from admin_staff";
			  $result_count= mysqli_query($DBcon,$sql_count);
			  $rowcount= mysqli_fetch_assoc($result_count);
			  for($count=1;$count<=$rowcount['counter'];$count++)
			  {
			  ?>
			  <td>
			  <?php 
				$new_time= date("H:i:s",$i);
				
			  	$sql= "select * from admin_calendar where time='$new_time'";
				$result4= mysqli_query($DBcon,$sql);
				$row4= mysqli_fetch_assoc($result4);												
				echo $row4['user'];
			  ?>
			  </td>
			  <?php } }?>
			  </tr>
		  
			  </tbody>
            </table>
          </div>
        </div>
        <div class="card-footer small text-muted"></div>
      </div>

Open in new window


admin_calendar tableThis is admin calendar table

admin_staff tableThis is admin staff table
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
select * from admin_staff

Open in new window


Instead of selecting the records like this, you can apply the PIVOT logic and then use the PIVOT query to select the data converted to the required format and then easily display it in your PHP page..
More info on how to PIVOT data with an example here..
http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/
sorry, i'm using a keyboard that has no parenthesis so you'll need to convert < > to regular brackets

select
if < user = "melvin" , "yes" ,"" > as melvin,
if < user = "candy" , "yes" ,"" > as candy,
..
from calendar
...


the equivalent in php will work as well by comparing your $row3 with the current user.


whichever way you pick, there is no reason to run more than a single query to display the whole thing.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial