CODEWITHSUNDEEP
phpmysqlpdo
b3tac0d3
b3tac0d3

Reputation: 908

PDO using MySql Case for ordering with bound variables

I've been researching this and I think I've found the best solution but I'm not 100% sure. I have a good handle on PDO but this is the first time I've encountered CASE in mysql. This code is working but I'm wondering if it's efficient? If I were to have multiple keys, I would have to write many arrays to be able to search and order. Is there a shorter way to write this code or is this the most efficient? Thanks!

$filters = "
    AND (name LIKE :keys
    OR note LIKE :keys
    OR tagnum = :skeys)
";
$order = "
    ORDER BY
        CASE
            WHEN tagnum = :skeys THEN 0
            WHEN name = :skeys THEN 1
            WHEN name LIKE :lkeys THEN 2
            WHEN name LIKE :rkeys THEN 3
            ELSE 4
        END
    ASC
";
$arr[':keys'] = "%$keys%"; // both wild cards
$arr[':skeys'] = $keys; // stripped keys, no wild cards
$arr[':lkeys'] = "$keys%"; // left key, right wild card
$arr[':rkeys'] = "%$keys"; // right key, left wild card

Upvotes: 0

Views: 36

Answers (1)

Shadow
Shadow

Reputation: 34305

If you want to have complete control over how you order your resultset, then your solution is perfect. However, if you have multiple keys, then I would definitely consider using fulltext search because it will be faster and lot less comlicated to code. However, it has a different ranking algorithm than you have now.

Upvotes: 2

Related Questions