Gayner Posted August 29, 2009 Share Posted August 29, 2009 I want the pm to be ontop of the am! here is my sql query order by: ORDER By date_time Quote Link to comment Share on other sites More sharing options...
ignace Posted August 29, 2009 Share Posted August 29, 2009 ORDER By date_time DESC Quote Link to comment Share on other sites More sharing options...
Gayner Posted August 29, 2009 Author Share Posted August 29, 2009 ORDER By date_time DESC That's what it is at.. and it still shows pm at bottom Here's full query take a look pelase. $month = ( isset($_POST[month]) ? $_POST[month] : date(F) ); $year = ( isset($_POST[year]) ? "20" . $_POST[year] : date(Y) ); WHERE receiver_id = '{$ibforums->member["id"]}' && month = '$month' && year = '$year' or sender_id = {$ibforums->member["id"]} && month = '$month' && year = '$year' ORDER By date_time DESC Quote Link to comment Share on other sites More sharing options...
ignace Posted August 29, 2009 Share Posted August 29, 2009 ORDER By date_time DESC That's what it is at.. Then why did you say your code was: ORDER By date_time Try: ORDER By date_time ASC Which is actually the same as the previous statement Quote Link to comment Share on other sites More sharing options...
Gayner Posted August 29, 2009 Author Share Posted August 29, 2009 ORDER By date_time DESC That's what it is at.. Then why did you say your code was: ORDER By date_time Try: ORDER By date_time ASC Which is actually the same as the previous statement Lol yea ASC but when people are still on am i dont it to asc.. i want it to DESC like i have.. but when it hit's pm it starts to asc which is my screenshot above.. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 29, 2009 Share Posted August 29, 2009 Unless your date_time column is an actual mysql DATETIME data type, it won't ORDER BY correctly. What is your table definition? Quote Link to comment Share on other sites More sharing options...
Gayner Posted August 29, 2009 Author Share Posted August 29, 2009 Unless your date_time column is an actual mysql DATETIME data type, it won't ORDER BY correctly. What is your table definition? date_time varchar(250) latin1_swedish_ci No None Browse distinct values Change Drop Primary Unique Index Quote Link to comment Share on other sites More sharing options...
.josh Posted August 30, 2009 Share Posted August 30, 2009 I don't know what kind of effort you have been putting into trying to figure it out on your own, but you definitely need to make an increased effort in communicating your problem, here. Every time someone responds, you respond by giving a little bit more detail. You will get an answer faster, the more details you give. just lay all your cards out on the table from the get-go. Lol yea ASC but when people are still on am i dont it to asc.. i want it to DESC like i have.. but when it hit's pm it starts to asc which is my screenshot above.. what does this mean? Are you saying if there are only "am" dates, you want it to count from earliest time to latest time, but if there there is a "pm" date somewhere in the mix, to order it latest to earliest? Or only if it's all "pm"? Quote Link to comment Share on other sites More sharing options...
awpti Posted August 30, 2009 Share Posted August 30, 2009 Wow, talk about bad table design. Change date_time to DATETIME and use NOW() in your insert to set it. ORDER BY will never do anything with a varchar(250) field. (Really? 250 characters?) Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 30, 2009 Share Posted August 30, 2009 You also apparently have month and year columns (probably because you can not do anything with your existing date_time column, except to display it "as is"), along with the date_time column. As awpti posted above, you need to make the column a DATETIME data type. That will eliminate the need for separate month/year columns and all the extra code to populate them because you can test for the month/year of a DATETIME data type directly in a query. Quote Link to comment Share on other sites More sharing options...
.josh Posted August 30, 2009 Share Posted August 30, 2009 uh, yes you can ORDER BY a varchar field. The problem would be that it would sort it alphabetically, and that may or may not coincide with how he has his date/time format. Example: This is what he expects the asc to be sorted by, because its in chronological order: Jan 15th, 2009 Feb 15th, 2009 Mar 15th, 2009 Apr 15th, 2009 May 15th, 2009 Jun 15th, 2009 Jul 15th, 2009 Aug 15th, 2009 Sep 15th, 2009 Oct 15th, 2009 Nov 15th, 2009 Dec 15th, 2009 This is what ORDER BY ASC will give him, since it's treated as arbitrary text in a string: Apr 15th, 2009 Aug 15th, 2009 Dec 15th, 2009 Feb 15th, 2009 Jan 15th, 2009 Jul 15th, 2009 Jun 15th, 2009 Mar 15th, 2009 May 15th, 2009 Nov 15th, 2009 Oct 15th, 2009 Sep 15th, 2009 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.