Welcome Guest, Not a member yet? Register   Sign In
Orderby date not working as expected
#1

[eluser]Nathan Pitman (Nine Four)[/eluser]
Hi all. I have written a small web application where we are recording data and logging the date upon which it is entered by passing a PHP date string to MySQL to store in a date field. The data all goes in well, we can see the dates in the DB via PHPMyAdmin and return them from the DB using CI and re-format the dates to '%d/%m/%Y' correctly. However if we try to run a query on the data and 'orderby' date the results are returned as if the date is a string. So for example we get:

'orderby log_date desc'

30/05/08
27/08/07
25/10/07
18/10/07

Obviously as you can see above the dates are returned in a seemingly random order, I think ot's actually ordering by 'day' rather than 'y/m/d'. So the 2nd and 3rd dates in the result are in the wrong order.

Any ideas??? Smile
#2

[eluser]Doosje[/eluser]
Are you sure that it is a date-field where you're ordering on ?
If it's a text-field you can expect these type of results.

And what happens if you query the database manual?
#3

[eluser]Nathan Pitman (Nine Four)[/eluser]
Hi Doosje, thanks for the quick response. I think I just worked out my error. In my select that occured prior to the order_by I was formatting the date column in question and this was being done 'prior' to the order_by.

So I was doing:

Code:
$select = "DATE_FORMAT(date_prod1, '%d/%m/%Y') AS date_prod1";

and this was returning the data in a different format to which it was stored, prior to the order_by. By changing my select to:

Code:
$select = "DATE_FORMAT(date_prod1, '%d/%m/%Y') AS fdate_prod1";

Note the different variable name for the formatted date, I have fixed the problem! I guess it's one of those scenarios where posting the question helps you to see the actual problem! Thanks for your help regardless! Much appreciated.

Nathan
#4

[eluser]Doosje[/eluser]
I understand the "select" thing .. but you wondered why the orderby wana't corrrect .... that isn't controlled by what you "select"

Even if you wouldn't select the date_prod1 you can still orderby !

I think you want to
Code:
ORDER BY DATE_FORMAT(date_prod1, '%Y-%m-%d')
at the end of you're query




Theme © iAndrew 2016 - Forum software by © MyBB