MySql search one column then another

[grant.baker]

Consider the following query:

//$fix and $limit are dynamic values
$query="SELECT ICAO_Ident AS ID, Location_Identifier AS LocID, Official_Facility_Name AS Name, Landing_Facility_Type AS AptType, Associated_State_Post_Office_Code AS State, Associated_City_Name AS City FROM APT WHERE `Location_Identifier` LIKE '%$fix%' OR `ICAO_Ident` LIKE '%$fix%' OR `Official_Facility_Name` LIKE '%$fix%' OR `Associated_City_Name` LIKE '%$fix%' ORDER BY `ICAO_Ident` LIMIT ".$limit."";

I would like to list the results that matched ICAO_Ident first, then Location_Identifier, then Official_Facility_Name, then Associated_City_Name. I've tried using sort, but sort alone doesn't give priority to a given column. I could use multiple queries, but I'd like to do it in one. Is there a way to do this in a single query?

[rproffitt]

Just a quick look, what about priority? Read this link about that and MySQL:
http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

[grant.baker]

Well, what I need is the results that match ICAO_Ident sorted and shown, then Location_Identifier sorted and shown, etc. I sort the results by ICAO_Ident.

For the first result where ICAO_Ident LIKE '%$fix%', ICAO_Ident might be "KEKM", so I would want it shown at the top. But other matches may have ICAO_Ident as say "KABC", so if I sort, then the wrong match would be shown first.

Possible to do that in one query?

[rproffitt]

For me, I wrap my queries in another language so for me it's possible to write code that sorts finely. Your post reminded my of the priority options in MySQL so I thought I'd share the relevant document.

So for me, I'd make the solution in my app and would not find it to be a problem to use more than one query. But that's me.

[grant.baker]

I do appreciate your help! I'm not too proficient with MySQL, so I was wondering if this could be done in a single query. It looks like it can't be done though, so I'll just write several queries and combine/sort the results in the rest of my PHP code.

[rproffitt]

In your last post you used the word combine. Maybe you need to think about the GROUP BY feature?
Some reading about that.

https://www.google.com/search?q=mysql+group+by+sort&ie=utf-8&oe=utf-8
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Sorry if I don't write your query for you. I would be guessing what the exact output was required and even then, it's best you understand your code.

[pritaeas]

If you split your query into four parts using UNION ALL, then the order is at your leisure:

    SELECT 1 AS Idx, ICAO_Ident AS ID, Location_Identifier AS LocID, Official_Facility_Name AS Name, Landing_Facility_Type AS AptType, Associated_State_Post_Office_Code AS State, Associated_City_Name AS City FROM APT WHERE `Location_Identifier` LIKE '%$fix%' 
UNION ALL
    SELECT 2 AS Idx, ICAO_Ident AS ID, Location_Identifier AS LocID, Official_Facility_Name AS Name, Landing_Facility_Type AS AptType, Associated_State_Post_Office_Code AS State, Associated_City_Name AS City FROM APT WHERE `ICAO_Ident` LIKE '%$fix%'
ORDER BY Idx, ICAO_Ident

[grant.baker]

A-ha, I had thought that there must be a way to do this! I hadn't thought to create an new ID column in the SELECT statement and use UNION ALL. This is much cleaner than trying to sort and combine several separate queries with PHP. Thank you pritaeas!