John Davidson

Message:

I have the following code which works correctly...

$serviceInput = $_POST['serviceSelection'];

    $words = explode(' ', $serviceInput); 

    $serviceQuery = "(SELECT service FROM services WHERE";

    $sql_1 = '';

    foreach($words as $word) {

        $sql_1 .= " AND service LIKE '%{$word}%'"; 

    }

    $sql_1 = substr($sql_1, 4); 

    $sql_1 .= " ORDER BY service ASC LIMIT 15) ";

    $sql_2 = " UNION "; 

    $sql_3 = '';

    $sql_3 = "(SELECT service FROM services WHERE";

    $sql_4 = '';

    foreach($words as $word) {

        $sql_4 .= " AND service_sub_keywords LIKE '%{$word}%'"; 

    }

    $sql_4 = substr($sql_4, 4); 

    $sql_4 .= " ORDER BY service ASC LIMIT 10) ";



    $serviceQuery = $serviceQuery.$sql_1.$sql_2.$sql_3.$sql_4; //STRING EVERYTHING TOGETHER

    $serviceQuery = $db->prepare($serviceQuery);

    $serviceQuery->execute();

but when I alter
$sql_1 .= " ORDER BY service ASC LIMIT 15) ";
to a CASE statement for improved result gathering as so...

$serviceQuery = "(SELECT service FROM services WHERE";

    $sql_1 = '';

    foreach($words as $word) {

        $sql_1 .= " AND service LIKE '%{$word}%'"; 

    }

    $sql_1 = substr($sql_1, 4); 

    $sql_1 .= " ORDER BY

                  CASE

                    WHEN service LIKE '{$word}%' THEN 1

                    WHEN service LIKE '%{$word}' THEN 3

                    ELSE 2

                  END, service ASC) ";

...the CASE statement doesn't execute...is this a syntax issue?

---

My Answer:
Yes, it looks like there is a syntax issue in your CASE statement. The correct syntax for a CASE statement in MySQL should be as follows:


$serviceQuery = "(SELECT service FROM services WHERE";
$sql_1 = '';
foreach($words as $word) {
$sql_1 .= " AND service LIKE '%{$word}%'";
}
$sql_1 = substr($sql_1, 4);
$sql_1 .= " ORDER BY
CASE
WHEN service LIKE '{$word}%' THEN 1
WHEN service LIKE '%{$word}' THEN 3
ELSE 2
END, service ASC) ";

$serviceQuery = $serviceQuery.$sql_1.$sql_2.$sql_3.$sql_4; //STRING EVERYTHING TOGETHER
$serviceQuery = $db->prepare($serviceQuery);
$serviceQuery->execute();


Make sure that the syntax of the CASE statement is correct and that it is properly enclosed within the ORDER BY clause.