What is the best way to go about grouping rows by the same timestamp?

Question

Right due to not getting the right answer last time, I will reword this.

I want to create a page where all fixtures from 'tbl_fixtures' are shown.

id - compname - home_user - home_team - away_user - away_team - date

That is the table setup. The date column is a timestamp. I want to show all fixtures in blocks based on the date column.

There will be blocks of 10 fixtures with the same timestamp on them. I want to show it like this...

Fixture 1 - (timestamp in readable format) Then all 10 fixtures....

Fixture 2 - (timestamp) Then 10 fixtures...

etc etc.

Until all the fixtures are grouped by their timestamp.

How could I go about doing this?

Answer 1

If you want to "group" them by weeks you can use the WEEK() function. The best would be to order the rows by the timestamp and print a new week header if the week changes:

$result = $mysql_query("SELECT WEEK(timestamp) as week, * FROM myTable ORDER BY timestamp");

$current_week = null;
while ($row = mysql_fetch_assoc($result)) {
  if ($row["week"] != $current_week) {
    $current_week = $row["week"];
    echo 'Week ' . $current_week .': ';
  }
  echo $row["timestamp"];
  // echo other fields
}

Answer 2

I'll try to read your mind and suggest using DATE_FORMAT:

SELECT DATE_FORMAT(`add_dt`, '%u') AS `d`, `field1`, `field2`
FROM `table1`
ORDER BY `d`

Where %u is week number (ref).