Fahad Kazmi Fahad Kazmi - 4 months ago 7
MySQL Question

SQL - sorting data according to the first priority when using LIKE in Clause

After googling I could not find my answer to sort data according to the priority when using

like
in
clause


I am using the following query to sort and view data:

SELECT i.name,i.add_time,round(i.price),s.store_address
FROM store_items i,stores s
WHERE i.store_id = s.store_id
AND (lower(i.name) LIKE '%samsung glaxy%'
OR lower(i.name) LIKE '%samsung%'
OR lower(i.name) LIKE '%glaxy%')
ORDER BY i.price ASC LIMIT 0,25


How I can sort resulted rows firstly for samsung glaxy first
like
operator as first priority
and then rows for samsung and glaxy as second priority

Please Note:

first priority means that result rows should be shown firstly and second priority means that other resulted rows should be shown after

I have to fit it in my
PHP
function.
@10086'answer I am not able to fit it in
PHP CODE


Please Help Me:
PHP CODE IS FOLLOWING


$sort = $_COOKIE['sort'];
$price_sort = $_COOKIE['price_sort'];
$currency_value = $_COOKIE['currency_value'];

if (!isset($_GET['number'])) {
$limit = 0;
}else{
$limit = filter_var($_GET['number'], FILTER_SANITIZE_NUMBER_INT);
}

$keyword_exp = explode(" ", $keyword); //separating keywords
$like = ""; //for use like clause for every keyword
$case = ""; // for priority selection case in query order
$case_inc = 2;
foreach ($keyword_exp as $value) {
$like .= "AND lower(i.name) like '%$value%'";
$case .= "WHEN lower(i.name) like '%$value%' THEN $case_inc";
$case_inc += 1;
}
//query performing to show result
$sql_query = "SELECT i.name 'title',i.add_time 'time',round(i.price) 'price',round(i.new_price) 'new_price',s.store_address 'address' FROM store_items i,stores s WHERE i.store_id = s.store_id $like ORDER BY CASE WHEN lower(i.name) LIKE '%$keyword%' THEN 1 $case END ASC LIMIT 0,25";
$query = mysql_query($sql_query) or die(mysql_error());
while ($row = mysql_fetch_array($query)) :
extract($row);
endwhile;


My SQL query in PHP code gives me following error


You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lower(i.name) like '%refurbished%' THEN 3 END ASC LIMIT 0,25' at line 1

Answer

Use CASE WHEN:

SELECT    i.name,i.add_time,round(i.price),s.store_address
FROM      store_items i,stores s
WHERE     i.store_id = s.store_id 
AND       (lower(i.name) LIKE '%samsung glaxy%' OR lower(i.name) LIKE '%samsung%' OR lower(i.name) LIKE '%glaxy%')
ORDER BY
CASE WHEN lower(i.name) LIKE '%samsung glaxy%' THEN 1
     WHEN lower(i.name) LIKE '%samsung%' THEN 2
     WHEN lower(i.name) LIKE '%glaxy%' THEN 3
     ELSE 4 END,
i.price ASC
LIMIT 0,25
Comments