Hilly_2004 Posted July 12, 2008 Share Posted July 12, 2008 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. Quote Link to comment Share on other sites More sharing options...
ratcateme Posted July 12, 2008 Share Posted July 12, 2008 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. Quote Link to comment Share on other sites More sharing options...
Wuhtzu Posted July 12, 2008 Share Posted July 12, 2008 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted July 12, 2008 Share Posted July 12, 2008 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 Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 12, 2008 Author Share Posted July 12, 2008 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? Quote Link to comment Share on other sites More sharing options...
teynon Posted July 12, 2008 Share Posted July 12, 2008 SELECT * FROM `database` WHERE `date` LIKE '200805%' ORDER BY `date` Assuming you're using a mysql timestamp Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 12, 2008 Author Share Posted July 12, 2008 Barand....the timestamp is formatted like: 2008-06-04 17:37:35 Quote Link to comment Share on other sites More sharing options...
teynon Posted July 12, 2008 Share Posted July 12, 2008 SELECT * FROM `database` WHERE `date` LIKE '2008-05%' ORDER BY `date` Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 12, 2008 Author Share Posted July 12, 2008 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.... Quote Link to comment Share on other sites More sharing options...
Barand Posted July 12, 2008 Share Posted July 12, 2008 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 Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 12, 2008 Author Share Posted July 12, 2008 $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. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 12, 2008 Share Posted July 12, 2008 echo $myrow['monthyear']; Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 13, 2008 Author Share Posted July 13, 2008 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 Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 13, 2008 Author Share Posted July 13, 2008 ....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 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 13, 2008 Share Posted July 13, 2008 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 ?> Quote Link to comment Share on other sites More sharing options...
Hilly_2004 Posted July 13, 2008 Author Share Posted July 13, 2008 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.