By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,786 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,786 IT Pros & Developers. It's quick & easy.

month date sorting

ddtpmyra
100+
P: 333
Im using PHP report and I convert the month date into numbers but when I generate and publish it now displaying like this...

1
10
11
2
3
4
5
6 and so forth. How can I make it happen to sor it by correct ascending orders not sorting the first digits only.
Dec 1 '09 #1

✓ answered by dlite922

This is how I thought you'd have it exactly too.

You don't need to convert anything, You just need to tell MySQL what to sort by. In this case you're telling it to sort by Month and Year, which are VARCHARS. If you say ORDER BY Year+0, Month+0 it should work for you.

You're basically telling mysql to automatically convert that number to do addition operation, then add zero. The result is an integer, thus when given to the sort engine, it will sort it like a number, instead of varchar.

Try that and let me know how it works out for ya,




Dan

Share this Question
Share on Google+
9 Replies


dlite922
Expert 100+
P: 1,584
There's multiple solutions, what's your code like? is any of them coming from mysql?

Here's a good article from google:
http://www.wellho.net/mouth/54_PHP-a...l-sorting.html

The Numeric sort is what you want. Which is accomplished by adding zero, turning it into an number. You could also use intval()

Dan
Dec 1 '09 #2

ddtpmyra
100+
P: 333
Here what's coming from MYSQL

Expand|Select|Wrap|Line Numbers
  1. Select  Date_Format(w.cal_date, '%c/%e/%Y') As Date,
  2.   Date_Format(w.cal_date, '%c') As Month,
  3.   Date_Format(w.cal_date, '%Y') As Year
  4. From web w
  5. Order By Year,  Month
Dec 1 '09 #3

ddtpmyra
100+
P: 333
how can i convert the month(varchar) to interger?
Dec 1 '09 #4

dlite922
Expert 100+
P: 1,584
This is how I thought you'd have it exactly too.

You don't need to convert anything, You just need to tell MySQL what to sort by. In this case you're telling it to sort by Month and Year, which are VARCHARS. If you say ORDER BY Year+0, Month+0 it should work for you.

You're basically telling mysql to automatically convert that number to do addition operation, then add zero. The result is an integer, thus when given to the sort engine, it will sort it like a number, instead of varchar.

Try that and let me know how it works out for ya,




Dan
Dec 1 '09 #5

dlite922
Expert 100+
P: 1,584
May I ask, why aren't you just sorting by w.cal_date?



Dan
Dec 2 '09 #6

ddtpmyra
100+
P: 333
I'm making a cross tab report or display that why I need to sort it by month. And it works when it was converted back to integer. Thanks for all your help Dan! your the BEST!
Dec 2 '09 #7

dlite922
Expert 100+
P: 1,584
You're welcome, appreciate the compliment.

I didn't clarify my last question. Reason I asked is, sorting by date /is/ still sorting by month and year isn't it? You'll also sort by day, but if you don't need the date it won't hurt anything.

Here you're doing two operation when one PHP operation can do it: strtotime()

This way you have an integer unix epoch date value that you can plug in to the date() function and get out so much more than just the month number.

Cheers,



Dan
Dec 2 '09 #8

ddtpmyra
100+
P: 333
Hey Dan,

Funny now i have another problem back to varchar and date type again... this time users select name of the month from the pull down menu and when I create a cross tab report as Month for the column it was sorted by character not by date as its equivalent month.

Now, can you tell me how to convert eg. January to 1?

thanks!
Dec 3 '09 #9

dlite922
Expert 100+
P: 1,584
you have to do that in code.

Write a generic function, put it somewhere accessible from your entire application, that has an array with all the months in it. The key is the month number, and the value is the name.

Why do they select January?

You're drop down menu should not have January as the option. Code works better with numbers than "Strings".

It should look like this: <option value="1">January</option>

So even though they select January, your program sees the number 1.

Hope that answered your question,







Dan
Dec 3 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.