Retrieving archived news by month

popcop

im using unix_timestamp() when i send a news story to the database so that i can display the 5 most recent stories on my hompage.

then to retrieve the story i use...

$q = "SELECT id, thedate, header, description, date_txt, photo, type FROM $tablename ORDER BY thedate DESC LIMIT 5";

i want to make a part on the site that has an option of reading the stories from each month

i want to have buttons like... jan, feb, mar, apr, may....ect

when apr is clicked for example a page will open with a list of the stories from that month.

how would i code the button to retreie that list of stories ?

devinemke

i might suggest that you use the standard date or datetime field type rather then using timestamps, it would make things such as this musch easier. for example, to grab all stories from april of this year you could simply do:

SELECT * FROM tablename WHERE MONTH(date) = '4' AND YEAR(date) = '2004'

however if you insist on using timestamps you could do something like this:

SELECT * FROM tablename WHERE MONTH(FROM_UNIXTIME(thedate)) = '4' AND YEAR(FROM_UNIXTIME(thedate)) = '2004'

popcop

if i was to change... unix_timestamp() to datetime()

would it make much diffrence ??

would i have to change this line....

$q = "SELECT id, thedate, header, description, date_txt, photo, type FROM $tablename ORDER BY thedate DESC LIMIT 5";

also ....

what does the 4 refer to in the code u gave me..

SELECT * FROM tablename WHERE MONTH(date) = '4' AND YEAR(date) = '2004'

devinemke

would i have to change this line...

no, you can use the ORDER BY clause for date and datetime types

what does the 4 refer to in the code u gave me...

4 = April, 5 = May, 6 = June etc...

popcop

one last thing...

what is the diffrence between unix_timestamp() and datetime()

devinemke

unix_timestamps are simply integers that contain the number seconds since 1970-01-01 00:00:00 GMT. datetime is stored as YYYY-MM-DD HH:MM:SS.

popcop

sorry im gettin a little confused here...

right i understand the diffrence between them and why its easier to use datetime()

but how would i code the button to open the list the stories...

if u got my site http://www.popcop.co.uk/ then click on a story u see it opens the full story

when i click one of the months i want a list of stories to appear

whats the easiest way to do it

devinemke

create a form where the user chooses the month/year:

<select name="month">
<option value="1">January
<option value="2">February
<option value="3">March
...etc...
</select>

then use the submitted form data to construct your SQL query.

popcop

Im not quite following you... can u explain a bit more for me please

devinemke

Originally posted by popcop
Im not quite following you... can u explain a bit more for me please

you want to setup a system whereby users can see an index of all of the stories for a given month/year, right? if so, create a form where the user chooses which month/year they want. when the form is submited you use that data to create an SQL query to grab the appropriate records:

$sql = "SELECT header FROM stories WHERE MONTH(date) = '" . $_POST['month'] . "' AND YEAR(date) = '" . $_POST['year'] . "' ORDER BY date";
$result = mysql_query($sql);
echo 'here are the headers for all of the stories from ' . $_POST['month'] . ' ' . $_POST['year'];
while ($row = mysql_fetch_assoc($result))
{
	echo $row['headers'] . '<br>';
}

this is just a simplified example. you would probably want to make each header a link to the actual story.

popcop

would this work... on my buttons

<a href="list.php?month=8?year=2004" target="news">august</a>

popcop

datetime() is not a function