[SOLVED] How to: Sort records into same month by timestamp?

[Hilly_2004]

Hi guys,

 

Bit of help. I have a table in a database called tbl_news.

 

How would I go about sorting all of the records in that table into the same month by the timestamps at which they were created....

 

e.g.

 

Basically I want the page to dynamically check if records were posted in a certain month of the year and if they were echo a link......

 

June 2008

May 2008

April 2008

 

Then that link would go to a page where all the news stories posted in that month would be displayed.

[ratcateme]

i think doing this would require doing it on the php side of the query using the date function like this

<?php
$month = date('m',$timestamp);
?>

 

Scott.

[Wuhtzu]

What exactly when do you want to sort them?

 

When exactly do you want to sort them?

 

Do you want to sort them when someone requests only post from May 2008 or sort them and write them to a text document or ?

 

 

What I have in mind is for example a blog or news article archive where you could click "April 2008" and see all posts from that month... is that what you wanna do?

[Barand]

The term "timestamp" is used ambiguously. Is your column

 

DATETIME  eg 2008-07-13 00:10:00

TIMESTAMP  eg 20080713001000

INT (unix timestamp value)  eg 1215904200

[Hilly_2004]

Spot on Wuhtzu...

 

In my mind I see 2 pages.

 

1st page is a script which gets all the records in tbl_news and checks to see which month and it's year a record has been added

 

e.g. Has there been anything posted in April 2008? No....don't display a link. Has there been anything posted in May 2008? Yes........display "May 2008" and a link to the next page.

 

The next page then displays all of the records added in May 2008 which might be 4 or 5.

 

Hope that makes sense?

[teynon]

SELECT * FROM `database` WHERE `date` LIKE '200805%' ORDER BY `date` Assuming you're using a mysql timestamp

[Hilly_2004]

Barand....the timestamp is formatted like:

 

2008-06-04 17:37:35

[teynon]

SELECT * FROM `database` WHERE `date` LIKE '2008-05%' ORDER BY `date`

[Hilly_2004]

SELECT * FROM `database` WHERE `date` LIKE '200805%' ORDER BY `date` Assuming you're using a mysql timestamp

The problem with that I'm guessing is that it requires me to enter the specific month e.g. May 2008.

 

I want the script to dynamically check if there have been any records created, and sort them into the relevant months....

 

e.g. The end list may look like:

 

June 2007

January 2008

April 2008

May 2008 etc....

[Barand]

this query will give the month/year which have items in the table so you can use them as links

 

SELECT DISTINCT DATE_FORMAT(tstampcol, '%M %Y') as monthyear FROM tbl_news ORDER BY tstampcol

[Hilly_2004]

$result = mysql_query("SELECT DISTINCT DATE_FORMAT(Date, '%M %Y') as monthyear FROM tbl_news ORDER BY Date",$connect);

 

while($myrow = mysql_fetch_array($result))

{

 

echo $monthyear;

echo "</br>";

 

}

 

Obviously I'm doing it wrong.

[Barand]

echo $myrow['monthyear'];

[Hilly_2004]

Works a treat....cheers Barand. One more, and I think this is the easy part.

 

Obviously with this it will echo out:

 

July 2008 etc...

 

On the next page I have to pass across a variable for the SELECT procedure to get all of the records in that month.

 

Will it be something like:

 

SELECT * FROM tbl_news WHERE Date =  '%M %Y' ORDER BY Date

[Hilly_2004]

....or something like this:

 

(Don't know how valid this is mind you)

 

SELECT * FROM tbl_news WHERE DATE = DATE_FORMAT('$VARIABLE', '%M %Y')  ORDER BY Date

[Barand]

You will need to select two date formats

 

- the one to be displayed (which you have now)

- the one for the link

 

<?php
$sql = "SELECT DISTINCT 
		DATE_FORMAT(Date, '%M %Y') as monthyear, 
		DATE_FORMAT(Date,'%Y-%m') as link
		FROM tbl_news ORDER BY Date";
$result = mysql_query($sql);

while($myrow = mysql_fetch_array($result))
{
echo "<a href='showItems.php?month={$myrow['link']}'>$monthyear</a></br>";
}
?>

 

On the linked page (showItems.php) you beed something like

 

<?php
$search = $_GET['month'];
$sql = "SELECT * FROM tbl_news WHERE Date LIKE '$search%' ORDER BY Date"

// process query
?>

 

[Hilly_2004]

Fantastic, works a treat, cheers Barand.

 

One thing I needed to add to get it to work was:

 

$monthyear = $myrow['monthyear'];

 

...but that's trivial anyway.

 

Once again, thank you!