Q:

Add temp colunm to mysql query with value dependant on order clause

I have mutliple selects on my search collecting all my look_up_to_cat_id's each been ordered by relevance with this order clause.
WHEN '.$searchProperties['col'].' LIKE \''.$term.'\' THEN '.$c_0++.' 
WHEN '.$searchProperties['col'].' LIKE \''.$term.'%\' THEN '.$c_20++.'
WHEN '.$searchProperties['col'].' LIKE \'%'.$term.'%\' THEN '.$c_40++;

My problem is when I start combining all my results, my ordering goes all messed up. I want to be able to add a column on to my query that will have one of the above numbers $c_0 or $c_20 or $c40 depending one which order clause applies to the result so that I can combine all the recordsets into an array and sort them correctly in the array using those numbers
This is one of my actual SQL statements to give you an idea.
SELECT `catelogue`.*,`release_date`.`RELEASE_DATE` FROM catelogue 
LEFT JOIN `release_date` ON `catelogue`.`RELEASEDATE_ID` = `release_date`.`ID` 
WHERE TITLE LIKE '%barbie%' 
GROUP BY ID 
ORDER BY 
(CASE WHEN TITLE LIKE 'barbie' THEN 0 
WHEN TITLE LIKE 'barbie%' THEN 200 
WHEN TITLE LIKE '%barbie%' THEN 400 
ELSE 600 END ),
( CASE WHEN TITLE LIKE 'barbie' THEN RELEASE_DATE 
WHEN TITLE LIKE 'barbie%' THEN RELEASE_DATE 
WHEN TITLE LIKE '%barbie%' THEN RELEASE_DATE END) desc,TITLE

So basically everything that matches 'barbie' will have 0 in the temp col then everything that matches 'barbie%' will have 200 and so on
I hope I have been clear enough

A:

Just move the case statements into the select clause:
SELECT `catelogue`.*,`release_date`.`RELEASE_DATE`,
       (CASE WHEN TITLE LIKE 'barbie' THEN 0 
             WHEN TITLE LIKE 'barbie%' THEN 200 
             WHEN TITLE LIKE '%barbie%' THEN 400 
             ELSE 600
        END ) as order1,
FROM catelogue 
LEFT JOIN `release_date` ON `catelogue`.`RELEASEDATE_ID` = `release_date`.`ID` 
WHERE TITLE LIKE '%barbie%' 
GROUP BY ID 
ORDER BY 
order1,
( CASE WHEN TITLE LIKE 'barbie' THEN RELEASE_DATE 
WHEN TITLE LIKE 'barbie%' THEN RELEASE_DATE 
WHEN TITLE LIKE '%barbie%' THEN RELEASE_DATE END) desc,TITLE;

You can now use the value in other queries.

