month date sorting

[ddtpmyra]

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.

[dlite922]

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-and-natural-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

[ddtpmyra]

Here what's coming from MYSQL

Select  Date_Format(w.cal_date, '%c/%e/%Y') As Date,
  Date_Format(w.cal_date, '%c') As Month,
  Date_Format(w.cal_date, '%Y') As Year
From web w
Order By Year,  Month

[ddtpmyra]

how can i convert the month(varchar) to interger?

[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

[dlite922]

May I ask, why aren't you just sorting by w.cal_date?



Dan

[ddtpmyra]

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!

[dlite922]

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

[ddtpmyra]

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!

[dlite922]

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">Janua ry</option>

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

Hope that answered your question,







Dan