如何从星期一到星期日将数据分组为几周

Question

I'm displaying some database user statistics from my mySQL table. I want to group the data by weeks so I can have individual tables for each week, Monday to Sunday. The dates in the database are stored as YYYY-MM-DD.

Currently I have the following PHP to access the data:

try {
        $sql   = "SELECT * 
                  FROM metric  
                  WHERE userid='".$tuserid."';";
        $result = $pdo->query($sql);
    }
    catch (PDOException $e) {
        $error =  $e->getMessage();
        include $errorpage;
        exit();
    }
    while ($row = $result->fetch()) {
                $metrics[] = array(
                    'id' => $row['id'],
                    'date' => $row['date'],
                    'value1' => $row['value1'],
                    'value2' => $row['value2'],
                     'value3' => $row['value3'],
                     'value4' => $row['value4']
        );
    }

And then I output it with this:

<table border="1" cellpadding="2px">
 <thead>
      <tr>
          <th bgcolor="#CECECE">Date</th>
          <th bgcolor="#CECECE">Candidates Added</th>
          <th bgcolor="#CECECE">Candidates Edited</th>
          <th bgcolor="#CECECE">Notes Added</th>
          <th bgcolor="#CECECE">Candidates Forwarded</th>
      </tr>
   </thead>
   <tbody>
<?php   
    for ($i=0;$i<count($metrics);$i++){
    ?>
    <tr>
          <td><center><?php echo $metrics[$i]['date'] ?></center></td>
          <td><center><?php echo $metrics[$i]['value1'] ?></center></td>
          <td><center><?php echo $metrics[$i]['value2'] ?></center></td>
          <td><center><?php echo $metrics[$i]['value3'] ?></center></td>
          <td><center><?php echo $metrics[$i]['value4'] ?></center></td>
  </tr>

<?php   
    }
?>
</tbody>
</table>

And here's an example of the output:

How can I separate the data into weeks starting from Monday to Sunday?

Answer 1

Add DAYOFWEEK() to your query:

$sql   = "SELECT *, DAYOFWEEK(date_field) as DWNum
            FROM metric  
            WHERE userid='".$tuserid."';";

And add that where you put the fields in the $metrics array.

As you loop through and build the table:

if($metrics[$i]['DWNum']==1) //1=Sunday
{
   echo "<tr><td colspan='5'>NEW WEEK</td></tr>";
}