PHP MYSQL Using IN() with CASE and Like in PDO

Question

I've been trying to implement an IN statment in my PDO sql. I've succesfully implemented a simple using the solution found in Using PDO with IN

My code is as follows:

        $in_array = array("INTERNAL SITE","SUPPLIER");
        $in  = str_repeat('?,', count($in_array) - 1) . '?';
        $dbname = $_SESSION['dbname'];
        $conn = new PDO("mysql:host=localhost;dbname=$dbname", $db->id, $db->pass); 
       //connect to db
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
      //error modes  
        $sql = "SELECT cs_id, name, alias, fk_cs_type as type, is_active as active 
        FROM companysite WHERE ( fk_cs_type IN ($in))";
        $stmt = $conn->prepare($sql);   
        $stmt->execute($in_array);

However my end goal is to also implement CASE and Like clause referencing an additional 2 variables ($srchBy and $srchField). When I try to do this I get a HY093 error. What am I doing wrong in this code?

        $in_array = array("INTERNAL SITE","SUPPLIER");
        $in  = str_repeat('?,', count($in_array) - 1) . '?';
        $srchBy = "ALIAS";
        $dbname = $_SESSION['dbname'];
        $conn = new PDO("mysql:host=localhost;dbname=$dbname", $db->id, $db->pass); 
       //connect to db
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);     
       //error modes     
        $sql ="SELECT cs_id, name, alias, fk_cs_type as type, 
               is_active as active FROM companysite
               WHERE ( fk_cs_type IN ($in)) AND 
              ((CASE WHEN $srchBy = 'ALIAS' THEN alias ELSE name END) 
              LIKE $srchField)"; 
        $stmt = $conn->prepare($sql);   
        $stmt->execute($in_array);

Your setting the variable "$srchBy" to the string `"ALIAS"`. So your MYSQL is effectively `CASE WHEN "ALIAS" = "ALIAS" ...`. Why not use a php conditional to build your sql instead of relying on MYSQL's CASE WHEN. — Jeemusu, Jun 23 '15 at 07:45
Understand but for this is for the purpose for the example. $srchBy is a search option where it can set to either ALIAS or NAME — Ka Tech, Jun 23 '15 at 07:48

Jeemusu · Answer 1 · 2015-06-24T01:10:05.523

1

Currently, your final SQL will look something like this:

SELECT 
    cs_id, 
    name, 
    alias, 
    fk_cs_type as type, 
    is_active as active 
FROM 
    companysite
WHERE 
    ( fk_cs_type IN ('INTERNAL SITE','SUPPLIER')) AND 
    ((CASE WHEN 'ALIAS' = 'ALIAS' THEN alias ELSE name END) LIKE 'value_of_$srchField_variable')

Unless you plan turning the statement into a MySQL View, why not consider using php conditionals to build a cleaner, less complex SQL statement.

$sql = "SELECT cs_id, name, alias, fk_cs_type as type, is_active as active 
        FROM companysite
        WHERE ( fk_cs_type IN ($in) )";

if($srchBy == 'ALIAS') 
{
    $sql.= " AND alias LIKE $srcField";
}
else
{
    $sql.= " AND name LIKE $srcField";
}

edited Jun 24 '15 at 01:10

answered Jun 23 '15 at 07:51

Jeemusu

10,415
3
42
64

I never thought of it that way, it could make things a lot cleaner. I will give it a go shortly, thank you! PS will $srchField be referenced ok in the sql statement or do I need to include it as well in the execute() part? If how would I do this? – Ka Tech Jun 23 '15 at 07:59
Thanks tried code, I had to put $srchField as '$srchField' and then it worked! Appreciate the help. – Ka Tech Jun 23 '15 at 11:39
Thanks tried code, I had to put $srchField as '$srchField' and then it worked! Appreciate the help. – – Ka Tech Jun 23 '15 at 11:53

PHP MYSQL Using IN() with CASE and Like in PDO

1 Answers1