showing a month from a selection of dates.

[runnerjp]

hey guys,

 

Ok so i have this in my database:

id      user      event date        time created

7    Admin    2010-06-14        1282745334

8    Admin    2010-07-14          1282745334

9    Admin    2010-07-24          1282745334

 

Now what i want to do is with this out put just show

 

JUNE

JULY

 

in an output, so i could show it on my website only if there is an event posted with that month

 

View all the events in June

 

 

.... how could i do that?

 

 

 

 

[schilly]

SELECT * FROM table WHERE DATE_FORMAT('%m',event_date) = $month_int

01-12 for month codes.

 

If they select events from a certain month just pass in the int value of the month via PHP and display the results.

 

DATE_FORMAT fn: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

[PFMaBiSmAd]

I think he wants to get the discrete list of months that exist in the data (probably over a specific date range) so that he can produce the month links (or perhaps year/month links).

[schilly]

so like

SELECT DATE_FORMAT('%M',event_date) as month FROM table GROUP BY month

?

[mikosiko]

so like

SELECT DATE_FORMAT('%M',event_date) as month FROM table GROUP BY month

?

 

more likely

SELECT DISTINCT DATE_FORMAT('%M',event_date) as month FROM table ORDER BY month

 

[PFMaBiSmAd]

I'm not sure where I got the idea these produced links. Anyway, the DATE_FORMAT() parameters are the date as the first parameter and the format parameter is second.

 

I would do it this way -

$start = '2009-01-01'; // start of data to look at
$end = '2010-08-25'; // end of data to look at
$query = "SELECT DISTINCT YEAR(event_date) as year, UPPER(MONTHNAME(event_date)) as month FROM your_table WHERE event_date BETWEEN '$start' AND '$end' ORDER BY event_date";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());

$current_year = '';
while($row = mysql_fetch_assoc($result)){
        // test for a change in the year
if($current_year != $row['year']){
	echo $row['year'] . '<br />';
	$current_year = $row['year']; // remember the new year
}
//echo "<a href='?year={$row['year']}&month={$row['month']}'>{$row['month']}</a><br />"; // in case you wanted links
echo "{$row['month']}<br />";
}

[runnerjp]

PFMaBiSmAd : This is perfect... i can use this and tinker it now... thanks loads guys for the help.