using ORDER_BY on two columns. the first being a date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

using ORDER_BY on two columns. the first being a date

Post by deejay »

Hi

What I am trying to do is get my results to return in date order firstly and then by alphabetical order.
I thought I'd be able to do this by adding 'ORDER BY b.arrival_date ASC, c.name ASC' to my query. And that does put some in the order I wish but not all.

I then thought that it must have something to do with the date's that are stored in mysql under the arrival_date coloumn, but they are exactly the same ie have no hours, seconds attached.

Any ideas welcome .

Code: Select all

 
$query = "SELECT b.arrival_date, b.departure_date, c.name, b.rid FROM booking_admin AS ba LEFT JOIN bookings AS b ON ba.booking_id = b.booking_id LEFT JOIN customers AS c ON b.cust_id = c.cust_id WHERE b.arrival_date >= '$date_commencing_original' AND b.arrival_date <= '$date_ending_original' AND ba.status != 'cancelled' AND ba.status != 'denied' AND ba.status != 'historic'  ORDER BY b.arrival_date ASC, c.name ASC ";
 
Thanks
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: using ORDER_BY on two columns. the first being a date

Post by onion2k »

The fact you're storing your statuses as strings makes me wonder what you're storing the dates as. Are they strings too? Or are they actual proper dates?
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: using ORDER_BY on two columns. the first being a date

Post by deejay »

hi

yeah the field is a 'date'. 0000-00-00
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: using ORDER_BY on two columns. the first being a date

Post by crazycoders »

Can you give us an example of the sorting errors your have. I think it might be a collation issue in this case!
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: using ORDER_BY on two columns. the first being a date

Post by deejay »

an example of the kind of result i'm getting ( all on same date)
Curt
Robin
Cam
Cam
mull
Purt
Smith
the collation is ' latin1_swedish_ci' in both tables.

Thanks for any advice.
Post Reply