timmah1 Posted January 9, 2009 Share Posted January 9, 2009 I have this page that lists newsletters grouped by year, and the months listed. The months are entered in like this 'January', 'February', 'March', 'April', 'May', 'June','July', 'August', 'September', 'October', 'November', 'December') How can I order these correctly with the names? I can't change the way the form is inputted or I would, would make this easier. I just need it to order the month's correctly, instead of alphabetically This is what I have $archived = mysql_query("SELECT * FROM newsletter WHERE year = '$year''"); echo "<ul>"; while($n1 = mysql_fetch_array($archived)){ echo "<li><a href=newsletter.php?id=$n1[newsID]>$n1[month]</a></li>"; } echo "</ul>"; Right now it lists them in order alphabetically Can anybody help me out? Thanks in advance Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/ Share on other sites More sharing options...
premiso Posted January 9, 2009 Share Posted January 9, 2009 You will have to define it, I would suggest, instead of using the month name, use the months numerical digit and do the order by that. I am sure you can use the mysql date function to do that bit too. Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733515 Share on other sites More sharing options...
Brian W Posted January 9, 2009 Share Posted January 9, 2009 see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82948 they have the opposite of what you want, but it will help I'm sure. Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733517 Share on other sites More sharing options...
timmah1 Posted January 9, 2009 Author Share Posted January 9, 2009 The form this is being input from I don't have access to, so the way it is inserted, I cannot control. Like I said, that would make it easier Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733518 Share on other sites More sharing options...
PFMaBiSmAd Posted January 9, 2009 Share Posted January 9, 2009 If we assume that you cannot change the table or the data in the table, are the months always fully spelled out exactly like you have shown? Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733520 Share on other sites More sharing options...
timmah1 Posted January 9, 2009 Author Share Posted January 9, 2009 Yes Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733523 Share on other sites More sharing options...
PFMaBiSmAd Posted January 9, 2009 Share Posted January 9, 2009 Also, what format in the table is your year? Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733524 Share on other sites More sharing options...
PFMaBiSmAd Posted January 9, 2009 Share Posted January 9, 2009 Anyway, the following should work (assuming I did not make any typo's) - $archived = mysql_query("SELECT *, STR_TO_DATE(CONCAT_WS('-',year,month,'01'),'%Y-%M-%d') AS ymd FROM newsletter WHERE year = '$year' ORDER BY ymd"); Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733530 Share on other sites More sharing options...
timmah1 Posted January 9, 2009 Author Share Posted January 9, 2009 It's listed as VARCHAR(4) and has the dates in the format 2008, 2009 that query gives no results Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733532 Share on other sites More sharing options...
timmah1 Posted January 9, 2009 Author Share Posted January 9, 2009 This is what I did, and it is working The only flaw is, it's showing 3 listings for December 2008, even though there is only one listing for that month and year <?php $archived = mysql_query("SELECT * FROM newsletter WHERE year = '$year'"); echo "<ul>"; $packages = array("January"=>'',"February"=>'',"March"=>'',"April"=>'',"May"=>'',"June"=>'',"July"=>'',"August"=>'',"September"=>'',"October"=>'',"November"=>'',"December"=>''); while($t = mysql_fetch_array($archived)){ $packages[$t['month']] = $t['newsID']; } foreach ($packages as $f => $v) if($v == "") { $fn = ""; } else { if($f == "January") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "February") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "March") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "April") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "May") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "June") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "July") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "August") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "September") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "October") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "November") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } if($f == "December") { $fn = "<a href='newsletter.php?id=$v'>$f</a>"; } echo "<li>$fn</li>"; } echo "</ul>"; } ?> I'm sure it's something simple Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733545 Share on other sites More sharing options...
timmah1 Posted January 9, 2009 Author Share Posted January 9, 2009 Nevermind, I got it Thanks for everybody's help Quote Link to comment https://forums.phpfreaks.com/topic/140178-solved-order-by-month/#findComment-733547 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.