CODEWITHSUNDEEP
phpmysqlselect
Joe Mitchell
Joe Mitchell

Reputation: 13

Showing data from a mysql table grouped by the month

Basically I have a lot of data in a table and its creation date with it. I want to show all of the data from the table, however in its output it need to be in date order, and then with a heading of the month it was created in above it.

i.e.

January data1 data2 data3

February data4 data5

March data6 data7 data8 data9

Is this possible from one query? I'm using php and mysql.

Thanks

Table Layout will be something like:

ID | Type | Content | User | Colour | Creation Date

It's all theory at the moment, but I will be creating it later today and tomorrow. I just needed to know if it's possible.

Upvotes: 1

Views: 2043

Answers (3)

nobody
nobody

Reputation: 10645

$res = mysql_query( 'SELECT MONTHNAME(creation_date) AS month_name, data_name
                     FROM my_table ORDER BY creation_date' );
$currentMonth = null;
while( $row = mysql_fetch_assoc($res) ) {
    if( $currentMonth !== $row['month_name'] ) {
        echo '<h1>'.$row['month_name'].'</h1>';
        $currentMonth = $row['month_name'];
    }
    echo '<p>'.$row['data_name'].'</p>';
}

Upvotes: 1

Kerrek SB
Kerrek SB

Reputation: 477040

I'd simply add the month to the query, order the query by date, and then track the month during the output phase and insert a new heading every time the month changes:

SELECT *, MONTH(thedate) AS month FROM thetable ORDER BY thedate;

In the PHP:

$lastmonth = "";
while ($row = mysql_fetch_assoc($queryresult))
{
  if (empty($lastmonth)) { $lastmonth = $row['month']; }

  if ($lastmonth != $row['month'])
  {
    $lastmonth = $row['month'];
    // print new month header
  }

  // print row
}

Upvotes: 2

duffymo
duffymo

Reputation: 308763

Add a MONTH column to your schema and populate it from the creation date. You need two columns to do this.

It sounds more like a reporting function than something that's transactional. The solution would be easy if you off-loaded historical records from your OLAP database into a reporting star schema with a time dimension. It would cut down on the size of your OLAP data set as well.

Upvotes: 0

Related Questions