[Resolved] [Resolved] order by multiple fields?

[perpetualshaun]

I am writing some code to store info for a customer's "routes" basically. The same customer may be on more than one route, so I have built the MySQL table w/ two fields - route_number_1 and route_number_2.

Now when someone selects a route number, I want to pull the results where route_number_1 OR route_number_2 = the number specified. That part is easy. However, I'm having trouble figuring out how to then order the result set by the stop numbers. So like if customer no. 1 has route_number_1 = 1, and stop_number_1 = 1, how do I get customer no. 2 who has route_number_2 = 1 and stop_number_2 = 2 to show up in the result set before customer no. 3 who has route_number_1 = 1 and stop_number_1 = 3.

Here's what I have, but it's not doing what I just described:

SELECT customer_id, customer_name, address, address_2, city, state, zip, phone, fax, notes, route_number_1 AS route_number, stop_frequency_1 AS stop_frequency, stop_number_1 AS stop_number, route_number_2 AS route_number, stop_frequency_2 AS stop_frequency, stop_number_2 AS stop_number FROM customers WHERE (route_number_1='9' OR route_number_2='9') ORDER BY stop_number_1 ASC, stop_number_2

Any ideas?

[zhabala]

You have wrong database design. You should have more tables

first for customer
second for routes with columns route_number and stop_number and key_field for customer_id

then you can easily sort them and you will not be restricted to have only two routes for customer.

Zdenek

[perpetualshaun]

Thanks! The more I thought about it, I thought that perhaps I would have to split out the routes from the rest of the data. Thanks for verifying.