PHP MySQLi - order by case not rly working

Question

This is my code:

   $q = '8gb pro';

   $sforeach = explode(' ', $q);

   $treffer = "when (titel LIKE '% $q %') then 1";

    $sortstring = "";       
    foreach($sforeach as $sf)
    {
        $sortstring .= "(titel LIKE '% $sf %') AND";
    }
    $sortstring = rtrim($sortstring, ' AND');

    $sortstring2 = "";
    foreach($sforeach as $sf)
    {
        $sortstring2 .= "(beschreibung LIKE '% $sf %') AND";
    }
    $sortstring2 = rtrim($sortstring2, ' AND');

    $sort = "order by case
                   $treffer
                    when $sortstring then 2
                    when $sortstring2 then 3
                    else 4
                    end, views DESC";

When I search for let's say 8gb pro then rows with title like Acer TravelMate P648-MG-71S5 are displayed before rows with title like Dell Radeon Pro WX 7100 8GB GDDR5 even though the condidion when $sortstring then 2 should come before when $sortstring2 then 3 and place it the other way around right?

Ah I see sry in my true code it's not really '$q' but $q it happend while putting a simple version here for better understanding. Please delete your comments ^^ — , May 06 '18 at 13:10
We just don't have enough clarity -- we need to see some table data and your rendered query in action. Are you able to mock us up an sqlfiddle demo link, so that we can see the issue being recreated? — mickmackusa, May 06 '18 at 13:20
It looks like the whole solution which I got from https://stackoverflow.com/questions/3609166/mysql-order-by-like isn't working because I tried it with simple: $sort = "order by case when titel LIKE '% 8gb %' then 1 end"; and it still not working, display another order again though. So I really need to look for another solution — , May 06 '18 at 13:36

score 0 · Accepted Answer · answered May 06 '18 at 14:06

Perhaps your intended sorting logic is to perform multiple ORDER BY conditions.

Adding a space to the left and right side of your search string will mean that you will not accurately match when the first word or last word is the match. For this reason, I must urge you to use the slower function REGEXP with its lengthy word boundary markers.

Code: (Demo) (A basic SQLFiddle Demo)

$q = '8gb pro';
$parts = explode(' ', $q);

$sort = "ORDER BY";
$sort .= " (CASE WHEN `titel` REGEXP '[[:<:]]{$q}[[:>:]]' THEN 1 ELSE 2 END),";
$sort .= " (CASE WHEN `titel` REGEXP '[[:<:]]" . implode("[[:>:]]' AND `titel` LIKE '[[:<:]]", $parts) .  "[[:>:]]' THEN 1 ELSE 2 END),";
$sort .= " (CASE WHEN `beschreibung` REGEXP '[[:<:]]" . implode("[[:>:]]' AND `beschreibung` LIKE '[[:<:]]", $parts) .  "[[:>:]]' THEN 1 ELSE 2 END),";
$sort .= " `views` DESC";

echo $sort;

Output:

ORDER BY
 (CASE WHEN `titel` REGEXP '[[:<:]]8gb pro[[:>:]]' THEN 1 ELSE 2 END),
 (CASE WHEN `titel` REGEXP '[[:<:]]8gb[[:>:]]' AND `titel` LIKE '[[:<:]]pro[[:>:]]' THEN 1 ELSE 2 END),
 (CASE WHEN `beschreibung` REGEXP '[[:<:]]8gb[[:>:]]' AND `beschreibung` LIKE '[[:<:]]pro[[:>:]]' THEN 1 ELSE 2 END),
 `views` DESC

A link explaining the word boundary syntax.

This is a very nice solution and works fine :D Thanks! – May 07 '18 at 08:30 — , May 07 '18 at 08:30

PHP MySQLi - order by case not rly working

1 Answers1