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.
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
9 2719
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
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
how can i convert the month(varchar) to interger?
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
May I ask, why aren't you just sorting by w.cal_date?
Dan
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!
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
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!
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dani |
last post by:
Hi,
I´m no superuser when it comes to MS Access. So I use MS Query to pull
out info from an SQL database to Excel.
I have a tabel containing different "titles" or "colums".
One contains a date....
|
by: bmoos1 |
last post by:
I need to make a Report of all people starting in 2004 by "Date
Requested". Dates are entered as mm/dd/yyyy. I have a query that has
all the people that started in 2004, but there are numerous...
|
by: Tony Miller |
last post by:
All
I have an aggregate query using the function Month & Year on a
datereceived field ie:
TheYear: Year()
TheMonth: Month()
These are the group by fields to give me a Count on another field by...
|
by: Katarina |
last post by:
Hello all,
I need help for sort date("Table"). How can I sort a month, then year?
Thanks in advance,
Catherine
|
by: Ante Perkovic |
last post by:
Hi,
How to declare datetime object and set it to my birthday, first or
last day of this month or any other date.
I can't find any examples in VS.NET help!
BTW, what is the difference...
|
by: LimaCharlie |
last post by:
Good Day to Everyone,
Need help!
I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06...
|
by: Jim |
last post by:
I'm sure this has been asked before but I can't find any postings.
I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the...
|
by: HowHow |
last post by:
I need to sort the "DateOfBirth" by the day (dd) regarless of month (mm)and year (yyyy). I have a query called q_DC_Client, in criteria, I am using this code below:
Like "*" & "/" & !! & "/" & "*"...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |