ORDER BY relevance, while using parametarized values

i found the following query online:

SELECT company_title
  FROM companies 
 WHERE company_title like '%gge%'
 GROUP BY company_title 
 ORDER BY CASE WHEN company_title like 'gge%' THEN 0 
               WHEN company_title like '% %gge% %' THEN 1 
       开发者_如何学C        WHEN company_title like '%gge' THEN 2 
               ELSE 3 
          END, company_title 
          limit 100

that works perfectly. the thing is, i am passing a parameter as value, and then fails. so the question is, how to use this query with parametized values?

an example for a mysql programmer could be to define a variable at top SET @what = '%gge%' and then use it in the query.

Move the case when statement to the select part and refer to it the the order by clause by its alias.

SELECT company_title
       ,CASE WHEN company_title like CONCAT(@param,'%') THEN 0 
             WHEN company_title like CONCAT('% %',@param,'% %') THEN 1 
             WHEN company_title like CONCAT('%',@param) THEN 2 
             ELSE 3 
        END as relevance
  FROM companies 
 CROSS JOIN (SELECT @param:= ?) as trick_to_set_at_param_in_one_go
 WHERE company_title like CONCAT('%',@param,'%')
 GROUP BY company_title 
 ORDER BY relevance, company_title 
 LIMIT 100

You can't use variables in an order by clause.

You have to use Dynamic SQL, check this: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html