[SOLVED] Display MYSQL table data by month with PHP?

[eddieblunt]

Hi,

I've got an image gallery where users can login and upload images. When an images is published it updates the "published" column with the current Unix timestamp

 

I want to be able to display the images on an archive page to view the uploads by month

 

Any ideas on how i should do this?

 

Here's my query string:

 

$query = "SELECT id, picture, user, title, published". 
         "FROM uploads". 
         "WHERE published is not null ".
         "ORDER BY published DESC ".  
         "LIMIT $offset, $rowsPerPage";

[AV1611]

date('Y-m-d H:i:s', $timestamp);

there's a hint

 

[eddieblunt]

Is that all you can give me?

[AV1611]

I really suck at dates myself.

 

If I understand you want to search for a given month? or loop through each month. If the latter, that's separate I think.

Either way, There are several ways I suppose but I would prolly determine the unix timestamp for the 1st day and last day of the month (midnight respectively) and add something like

"... WHERE field >= $monthstart AND field <= $monthend..."

 

 

[Barand]

You could

 

SELECT id, picture, user, title, DATE_FORMAT(published,'%M %Y') FROM ...

 

which will give dates in "October 2008" format

[eddieblunt]

AV1611

 

I was thinking, just links with each month at the top of the archive page and have the link read something like:

 

http://www.example.com/2008archive.php?month=Jan

 

 

[Barand]

Make that "?month=1"

 

$month = $_GET['month'];

$year = date('Y');  // current year

 

then

 

SELECT ... WHERE MONTH(published) = $month AND YEAR(published) = $year

[AV1611]

My way would work but Barand is sooo much smarter than me

 

Do it his way

 

Hey Barand! Long time no see!

 

[eddieblunt]

That doesn't seem to work?

 

My dates are stored in my database as "1200350730" for example, which is 14/01/08

 

does the

WHERE MONTH(published) = $month

know this?

 

 

I've also tried with and without the DATE_FORMAT on the SELECT line

[Barand]

WHERE MONTH(FROM_UNIXTIME(published)) = $month

[eddieblunt]

Genius!!

 

Thats done it, thanks for all your help!!