Ordering results from MySQL

Is this possible?

[wruk999]

Okay, this isn't a newbie post, but quite a complex problem I think.

I have a VARCHAR field in my database, and most of the entries are a date, in the format: 28/04/03.
The reason that it is VARCHAR and not a date field is that sometimes it needs to be a word value.

Now, here comes the problem: I am displaying a list of records and I want to sort them by the date in the VARCHAR field. When I do sort them, it sorts them like so:

28/03/03
28/04/03
29/03/03
29/04/03

but what I need it to do: is sort it by year THEN month THEN day. Is there any way, of being able to keep the english date format, and aloos PHP to sort it how I want it to?

Any help will be very much appreciated!

Regards,
wuk999

[Birdman]

Sorry, I don't think I can actually build the solution(at least not in a reasonable amount of time :)), but I think array_multisort() [php.net] is the key.

[daisho]

This is kindof a klungy fix. You'd have to watch the DB Performance since this would be rather intensive:

SELECT tblstuff.*,
'20' ¦¦ SUBSTRING(tblstuff.date,7,2)
'-' ¦¦ SUBSTRING(tblstuff.date,4,2)
'-' ¦¦ SUBSTRING(tblstuff.date,1,2) AS klungy_date
FROM tblstuff
ORDER BY klungy_date DESC

If you have dates before 2000 you may want to look at the "IF" function from MySQL to decide if the first 2 chars should be 19 or 20.

Also since I'm from an Oracle background I have ¦¦=Contact turned on. If your mysql does not have that (¦¦ does not concatinate strings) then look up the CONCAT function at mysql for details. Should be very easy to switch.

daisho

[edit]Stilly me used 0 based rather than 1 based. PHP substr is 0 based, mysql substring is 1 based...[/edit]

[edited by: daisho at 6:03 pm (utc) on April 28, 2003]

[bonanza]

ok, I'll bite.

select date, 
SUBSTRING(date,1,2)"day",
SUBSTRING(date,4,2)"month",
SUBSTRING(date,7,2)"year"
from table
order by 
SUBSTRING(date,7,2),
SUBSTRING(date,4,2),
SUBSTRING(date,1,2)

You don't need to have those three substrings in the output, but it helps to see what's going on. All you need is this:

select date
from table
order by 
SUBSTRING(date,7,2),
SUBSTRING(date,4,2),
SUBSTRING(date,1,2)

I don't have a test case, so check my string math.

[daisho]

bonanza,

looks good.

[wruk999]

Thanks everyone for your speedy replies, and especially bonanza - terrific, thank you! :)

Just one question - how can I get it to order them the *other* way now?

It is printing like this;
28/02/03
22/04/03
25/04/03
26/04/03
28/04/03
which is great..could just really doing with them being in the opposite order. Any Ideas?

Regards,
wruk999

[Birdman]

I think you just add either DESC or ASC at the end to reverse the order.

select date
from table
order by
SUBSTRING(date,7,2),
SUBSTRING(date,4,2),
SUBSTRING(date,1,2) DESC

Yes, thanks daisho and bonanza! I have been enlightened :)

[wruk999]

Hi Birdman,

I had tried that, but when adding ASC, it doesn't change anything (sames as without)..but..when adding DESC:

[/quote]28/02/03
28/04/03
26/04/03
25/04/03
22/04/03[/quote]

It is ordering the days right, just not the month? :(

Any ideas?

[daisho]

In my example all you need to do is change "DESC" to "ASC" since I am building a mySQL Date rather than dealing with each field seperatly. With the "Klungy_Date" virtual field in my example you can also use any date function listed at mysql dot com. Will help if you only need a subset. You can add a simple BETWEEN clause.

With bonanza's example it is also easy to do. You just have to order each field ie:

select date
from table
order by
SUBSTRING(date,7,2) DESC,
SUBSTRING(date,4,2) DESC,
SUBSTRING(date,1,2) DESC

This is because each field is seperate. birdman was on the right track but it would only be ordering the days within a month. Unfortunatly the months and years still come out random.

At this point you may want to do some tests and I am not sure which query (Mine or Bonanza's) will be faster against your data or if it even makes a difference to you.

daisho

[edited by: daisho at 6:48 pm (utc) on April 28, 2003]

[andreasfriedrich]

ASC is the default, so that´s why you did not see any changes. You need to add the DESC to each column that you want to order that way.

Andreas

<added>too slow</added>

[wruk999]

Hey daisho & andreas,

Thanks a lot - thats works a treat! :)

Cheers

[lorax]

Is there a particular reason you're not using the DATE data type?

[daisho]

Hey lorax,

I thought the same thing. When I reread the first post he said that he would except that some values are not date values.

daisho

[lorax]

Thanks daisho - missed that line.

But still makes me wonder. There must be a way to normalize the db so that the data can be seperated into their native types so you can avoid the hoop jumping.

For example, add an extra field specifically for when the data IS a DATE type. If it's a word, then use the VARCHAR field. Check both and sort accordingly.

[Allen]

Or you could make your life even easier and use the MySQL style date: YYYYMMDD

That'll get rid of that horrible query line :)

Allen

[daisho]

Lorax I was thinking the same thing. I normally don't like NULL data but a NULL Date field could signal the fact the data is in the varchar. Not quite as normalized as I like since I strive for no NULL values but without seeing the full picture it seems like that may be the best solution.

daisho

[bonanza]

The application may be such that it's possible that there will not ever be enough traffic/data to cause performance issues with the multi-substr sorting.
(Always plan for success!)

So if there is potential for performance issues, I'd agree with Daisho, Lorax, et. al., a reasonable alternative would be to denormalize into a second date column with a NULL representing that the record has a text value to present.

[wruk999]

Hi Guys,

I think bonanza summed it up when he said "there will not ever be enough traffic/data to cause performance issues with the multi-substr sorting.".

It is not a "public" system, and more of an "intranet" system used on the web, where users from mainly UK and the odd European log in and check the status of their stock on order.

The page where this particular query is used, is on a page which only the admins use to have an overview of the stock. It shows them stock available, which has a value "Immediate" or a date of estimated arrival.
I think it would have been far more work to have had two fields, and then make the script set to check for null values in the varchar field etc etc.

Afterall, the admins are probably only checking this list 3 or 4 times a day (at most) and so the server load is not a real issue.

How much more code would there be to check for null values in VARCHAR and then if null, print the date field? Quite a bit I presume - maybe I'm wrong!

Thanks for all your help everyone - much appreciated!

Regards,
wruk999

[daisho]

If the only other text value would be Immediate you could do:

(assuming $row is an associative array of a database row and your date field is "rec_date")

print (""==$row['rec_date'])?"Immediate":$row['rec_date'];

Or if you could have different types other than Immediate then say the varchar field is "rec_note" you could do:

print (""==$row['rec_date'])?$row['rec_note']:$row['rec_date'];

daisho