While loop show once

[ecabrera]

ok so im check to see if the date is in the database if it is show that data if its not display the day. But when the day is not there it shows 4 times any idea i want it to appear only once

 

			$get = "SELECT * FROM `timesheet` WHERE `date` >= CAST('$start' AS DATE) AND `date` <= CAST('$end' AS DATE);";
			//query
			$getquery = mysqli_query($db,$get);
			
			while($rows = mysqli_fetch_assoc($getquery)){

      //setting the db info into varibles
      $s = $rows['c_date'];
      $startdb = $rows['start'];
      $enddb = $rows['end'];
      $dayl = $rows['day'];
      $daytotal = $rows['day_total'];


      if($cole[0] == $s){
    echo "<tr><td class='tg-031e'>M      $cole[0]</td>   
    <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }else{
     echo "<tr><td class='tg-031e'>M      $cole[0]</td>   
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>
    <td class='tg-031e'></td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";       
      }
      if($cole[1] == $s){
    echo "<tr><td class='tg-031e'>T      $cole[1]</td> 
    <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }
      if($cole[2] == $s){
    echo "<tr><td class='tg-031e'>W     $cole[2]</td>   
        <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }
      if($cole[3] == $s){
     echo "<tr><td class='tg-031e'>TH    $cole[3]</td>
         <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }
      if($cole[4] == $s){
    echo "<tr><td class='tg-031e'>F      $cole[4]</td>   
        <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }
      if($cole[5] == $s){
     echo "<tr><td class='tg-031e'>S      $cole[5]</td> 
         <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }
      if($cole[6] == $s){
     echo "<tr><td class='tg-031e'>S $cole[6]</td>    
         <td class='tg-031e'>$startdb</td>
    <td class='tg-031e'>$enddb</td>
    <td class='tg-031e'>$daytotal</td> 
    <td class='tg-031e'></td>
    <td class='tg-031e'></td>";
      }

[Barand]

That code looks terribly repetitive. What is in the $cole array?

[Psycho]

You have an if/else condition at the beginning of the loop. That will run on each iteration of the loop. That is the cause of your problem. The logic will need to be rewritten, but I see other problems as well. For example, you are storing a calculated value for the total hours, that should be calculated in the query - not stored. Plus, I see there are two date values in the DB date & c_date. I think c_date is a formatted date - again you can format the date at run time - don't store duplicate data. Lastly, if you are only going to run this for a week, then you only need to define the start date.

 

This code may have some minor errors as I did not create a DB or test data to ensure it was 100% accurate. But, the general logic is sound.

 

 

<?php
 
//This code assumes:
// date is a date field type
// start & end are datetime field types
 
$start = "2015-3-30"; //March 30th
 
$query = "SELECT date, start, end, TIMESTAMPDIFF(SECOND, start, end) as day_total
          FROM `timesheet`
          WHERE `date` >= CAST('$start' AS DATE)
            AND `date` <= DATE_ADD(CAST('$start' AS DATE), INTERVAL 6 DAY)";
$result = mysqli_query($db, $query);
 
//Dump results into an array
$records = array();
while($row = mysqli_fetch_assoc($result))
{
    $records[$row['date']] = $row;
}
 
//Create a loop from first to last date
//for each day, check if there is a matching records
//in the records array. If so, use that data
//Else, there was no data for that date
 
for($day=0; $day<7; $day++)
{
    $timeStamp = strtotime("{$start} +{$day} day");
    $dateStamp = date("Y-m-d", $timeStamp);
    $DOW       = date("D", $timeStamp);
    $dateStr   = date("m/d/Y", $timeStamp);
 
    //Set default values
    $startTime = ''
    $endTime = '';
    $totalHours = '';
    if(isset($records[$dateStamp]))
    {
        $startTime = $records[$dateStamp]['start'];
        $endTime   = $records[$dateStamp]['end'];
        $totalTime = $records[$dateStamp]['day_total'];
    }
 
    //Output results
    echo "<tr>\n";
    echo "<td class='tg-031e'>{$DOW}    {$dateStr}</td>\n";
    echo "<td class='tg-031e'>{$startTime}</td>\n";
    echo "<td class='tg-031e'>{$endTime}</td>\n";
    echo "<td class='tg-031e'>{$totalTime}</td>\n";
    echo "<td class='tg-031e'></td>\n";
    echo "<td class='tg-031e'></td>";
    echo "</tr>\n";
}
 
?>