How can you order before you search?

matchew

I'm working on a listing site which is mainly organised by zip code distances. My only problem is when the listing are shown they're scattered according to the database.

I cant use GROUP BY city because this will only show 1 for each city, I cant use ORDER BY because this orders the RESULTS of the cities. I need to find out how to order the cities either in order they were requested or before the search starts.

example:

$query = "SELECT * FROM listings WHERE goodtogo='y' AND city='Cranford' OR city='Clark' OR city='Elizabeth'";

so if there are 3 listings in each city but scattered in the database they would be listed in this order:
Cranford,Cranford,Cranford,
Clark,Clark,Clark,
Elizabeth,Elizabeth,Elizabeth

Because GROUP will show:
Cranford,Clark,Elizabeth

And ORDER will show:
Clark,Clark,Clark,
Cranford,Cranford,Cranford,
Elizabeth,Elizabeth,Elizabeth

ANY ideas?

bogu

If u want to make an order by city and zip_code u can use:

ORDER BY city ASC, zip_code DESC

U can use this type of orders ...

Or u want to do something else? I dont realy understand your question ...

rafadc

I don't know if I understood correctly but...

You can add the timestamp of the moment the cities were added to the db and then ORDER BY the timestamp.

matchew

not quite, this is for a classifieds site so the listings can be posted at any time in any order. When a query is submitted mysql will list results from the first database entry to the last unless you request an ORDER. But order will organise the results ASC or DESC. I need to figure out a way to either order the database before it is searched so all cities are together when searched or a way to create a specific ORDER other than DESC or ASC.

example:
Say my database has the following in this order.

Elizabeth
Cranford
Clark
Elizabeth
Clark
Cranford
Elizabeth
Cranford
Clark

And I have a request for listings and I need them listed according to distance. (I already have the order of distance according to the city.)

So say I needed all listings for Cranford, then Clark, then Elizabeth.

Cranford
Cranford
Cranford
Clark
Clark
Clark
Elizabeth
Elizabeth
Elizabeth

ASC and DESC will not give me this, and I cant do separate queries because i need to limit the listings. I hope this explains better.
The only thing I can think of is to organise the database every time its updated, any other suggestions?

Roger_Ramjet

Well if you have the distances stored somewhere then you just use that in your ORDER BY clause.

Now, if the distance is being calculated on the fly then you can still use that calculated column for your ordering.

There is a third way to do this by using CASE

$sql = "SELECT *, CASE city
     WHEN 'cranford' THEN 1
     WHEN 'clarke' THEN 2
     ELSE 3 END AS slick
      FROM listings WHERE goodtogo='y' AND (city='Cranford' OR city='Clark' OR city='Elizabeth') ORDER BY slick";

matchew

I love you dude. That was exactly what I was looking for. Other than joins is this about as complicated as a query can get?
I needed it for :

<a href="http://www.the-online-classifieds.com/search.php">http://www.the-online-classifieds.com/search.php</a>

If you wanted to see exactly what I was talking about

Roger_Ramjet

No, not by a long chalk. Queries can get far more complex and convoluted than that.

CASE in sql is a little known thing. But case selects (the generic term) exists in almost every language. It is the 'switch' statement in php, but not a lot of people seem to know about that either, at least those that post here. They are one of the most usefull and powerfull control structure in any language as you have found out.

Roger_Ramjet

PS I just edit the query as otherwise the OR's are going to override the goodtogo='y' and return everything with a match on a city regardless of goodtogo. I was concentrating on the Case and got sloppy with the rest of it.