php/mysql ORDER BY Question

rakennedy75

I had a request to have my results that are being pulled from MySQL (using php) to be ordered in a specific way.

The fields specified are MAKE, AND CERTIFED
The Certified field are an ENUM with values 0,1.
0 - noncertifed
1 - certifed

So this is the request to have all certifed chevy vehicle show first.

Than non-certifed chevy's show first

Than all other vehicles show or ASC order.

Is there an easy way to do this through a MySQL statement? I could put and extra field in the database with a number attached to each situation and just order by that but that doesn't seem like a very efficient way to do this.

Thanks for your help.

andrewtayloruk

I'm sorry I do not understand your question.

Do you mean

SELECT * FROM table ORDER BY firstfield,secondfield

If this is not what you mean, please give us more details. I appreciate it is difficult if English is not your primary language.

rakennedy75

I think that might be what I am looking for. If I am to understand this, the firstfield gets listed first and than the second field and so on. I will give it a try and see if this works. I didn't know you can list them consecutively.

Thanks

Azala

I think he wants to do this:

Sort on MAKE = Chevy AND Certified = 1 then MAKE = Chevy AND Certified = 0 then Make = whatever except Chevy ASC in the same statement.

Azala

andrewtayloruk

Hmm, I didn't get that from the post. In that case I would think the best way to do it would be to get all the results in to an array then manually pull out of the array the Chevy stuff first. The easiest way (read least efficient) would be to hit the database twice, the first time for the Chevys the second time for everything else.

Anyone else?

Andrew

Catharsis

I read through the question at least three times and am still puzzled. Can you please explain what you want to do again?

rakennedy75

Yeah, sorry about the confusion, I should probably illustrate this to make it clear since I am not doing a great job explaining.

Say I have a TABLE (the dash will be the separator for the columns)..

id - make - certified
1 - BMW - 0
2 - BMW - 1
3 - BMW - 1
4 - BMW - 0
5 - CHEVY - 0
6 - CHEVY - 1
7 - CHEVY - 1
8 - CHEVY - 0
9 - DODGE - 1
10 - DODGE - 1
11 - DODGE - 0
12 - DODGE - 1

I need it to be ordered like this
id - make - certified
6 - CHEVY - 1
7 - CHEVY - 1
5 - CHEVY - 0
8 - CHEVY - 0
2 - BMW - 1
3 - BMW - 1
1 - BMW - 0
4 - BMW - 0
9 - DODGE - 1
10 - DODGE - 1
12 - DODGE - 1
11 - DODGE - 0

So if you look above, notice that chevy gets put on the top which go against the rules of ASC or DESC.
I don't know how I would do this.

Along with this certified will come before non-certified in all cases afterwards

After this all other fields will be ORDER BY make ASC

So there are quite of few parameters that the ORDER BY has to follow. Can this be done.

The id really has no relevance aside to help you see what I am doing.

I hope this clears up my question and sorry for the confusion.

Thanks for your time.

Roger_Ramjet

If you are going to do this often then either add a column for the sort order of the makes, or run a UNION query


SELECT * FROM table WHERE make='Chevy' ORDER BY certified DESC
UNION
SELECT * FROM table WHERE make='BMW' ORDER BY certified DESC
UNION
SELECT * FROM table WHERE make='Dodge' ORDER BY certified DESC

This will return the table you want.

Azala

If you are sorting make alphabetically I believe this will work also, although untested:


"SELECT * FROM table ORDER BY make ASC, certified DESC"

Roger_Ramjet

But he is not sorting make alphabetically, Azala. That is where the problem lies and why I offer the Union query as the answer. In a Union query the results of each subquery are appended to those from the previous query, so we get the ordering from the order of the parts in the Union.

rakennedy75

Thanks everyone, this has been very helpful. I have one question to add to this.

Referring back to the post from Roger Ramjet. This works for my purposes here. However I have to add a limit statement to it. If I only want 8 results to pull up on the first page, how would that work with a union statement like the one you have written. (Currently I have a pagination script setup for these results)

Thanks againg for you time

Roger_Ramjet

It would not work with a union query. At best you could limit each part of the union.

I think your best option is to add a table for makes. This would contain primary key, name, and sort order. By substituting foriegn key for the existing char make field in the main table you will also save disc space. It would also simplify your select query as union would not be neccessary: instead you would use the sort order column in the makes table. You could then use limit etc. Since the number of makes is limited in the real world the makes table primary key column could be smallint (64K possible makes) or even tinyint (256 possible makes). A major saving in disc space.

rakennedy75

That makes sense, unfortunately it won't be an easy conversion to this sense I am pulling in a number of third party data but maybe its worth it in the long wrong.

I appreciate the help and time.

Roger_Ramjet

So. Pull it into a work table and INSERT ... SELECT ... from their. Simple join with the makes table to match make and get makeID.

xblue

alternatively, it might be possible to create that additional order criteria on the fly, sth like (untested)

SELECT *, 
IF(make='Chevy', 1, 0) AS sortorder 
FROM table 
ORDER BY sortorder, make, certified

Roger_Ramjet

Yep. Lots of ways to do it.

'Best practice' when dealing with 'third party data' is to import it into a work table so that any crap, incomplete records, etc does not corrupt your main table, or break your insert query. You can validate it in the work table, check for missing fields duplicate records and the like, before adding it to the main table.