PHP / MySql Query and Group By Date

[hoopplaya4]

Hi All,

 

I'm currently querying some database fields that are dates.  They are all in the following format: 11/12/2008.

 

My question is this: How can I query the database to get all these fields, and then group them into separate (links) by Month.  For example, all the 11/xx/xxxx will be displayed under a link that would say "November."  Then users would click "November" and then all 11/xx/xxxx database entries would be displayed.

 

Is this going to be too complicated?  Or is there an easy way to achieve this?  Please let me know if I'm being vague!  Thanks.

[php.ajax.coder]

Use something like this

 

<?php
// Delimiters may be slash, dot, or hyphen

$date = "04/30/1973";
list($month, $day, $year) = split('[/.-]', $date);
echo "Month: $month; Day: $day; Year: $year<br />\n";
?>

 

Then sort by $month

[hoopplaya4]

Thanks for the reply.  That's getting me in the right direction.

 

However, how can I implement names of months (e.g., November, December, etc..) as opposed to just having the numbers?

 

Thanks!

[hoopplaya4]

Does anyone else have any ideas?  Thanks.

[bobbinsbro]

well, to get the name of the month from the numeric date you could do something like:

$date = "11/12/2008";
$timestamp = strtotime($date);
$monthName = date("F", $timestamp);

 

i'm not 100% this works, but i don't see why it shouldn't.

[hoopplaya4]

That worked, thanks.  But the main point of my page is because it acts as an "archive" for all of the MySql records.  Thus, how might I display it in a way like below:

 

> 2008

 

> 2007

 

> 2006, etc

 

Then, a user will click the year, which will then spill out all the MySql records for that year.  Does that make sense?  Is there a tutorial anywhere out there that teaches how to make an archive?

[bobbinsbro]

is the date column in the database table of datatype DATE?

[hoopplaya4]

Thanks for the reply. 

 

No, the date column is just plain text.  For example, 10/22/08.  It is posted from a date picker.

[bobbinsbro]

well, i can think of 3 ways:

1) if you know the range of years, you can pass the year with the link as an argument like:

<a href="somePage.php?year=2008>2008</a>

and then $_GET['year'] on somePage.php, and query the DB table for all rows that match:

"SELECT * FROM tableName WHERE date LIKE '%{$_GET['year']}'";

using the example of 2008, this will find all rows which have the digits 2008 at the end of the value in the date column. then just display them on the page.

 

2) if you don't know the range of years, things get a little trickier, since you have to get dates from the DB, but the dates are treated as strings, so you have to get all the unique rows, and either get the year off each row (by using explode() or split() by the "/" character), and then making a list of the unique years, and then doing option 1 stated above.

or you could get all unique dates from the database, convert them to timestamps with strtotime(), and then find the highest timestamp value and the lowest timestamp value, getting the year value from each (by using date("Y", $timestamp)) and then looping through the whole range to populate the link list with all intermediate years and then doing option 1 stated above.

 

3) (again, if you don't know the range of years) change the table to use a DATE datatype for dates, and then just do:

"SELECT date FROM tableName ORDER BY date DESC LIMIT 1";

and get the year off of that. that will be your $yearMax.

then do:

"SELECT date FROM tableName ORDER BY date LIMIT 1";

this will be your $yearMin.

then loop through the intermediate years to populate the link list, and do option 1 as stated above.

 

there are possibly better ways to do this, but i'll let the big guyz come up with them.

[hoopplaya4]

Thanks for your help.  I went ahead and went with your first suggestion.  This should suffice for me to get by right now.

 

Take care!