php - PHP MYSQL在PDO中使用CASE和like的IN()

我一直在尝试在我的PDO sql中实现IN语句。我已经成功使用Using PDO with IN中的解决方案实现了一个简单的解决方案

我的代码如下：

        $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);

但是，我的最终目标是也实现引用另外2个变量（$ srchBy和$ srchField）的CASE和Like子句。当我尝试执行此操作时，出现HY093错误。我在这段代码中做错了什么？

        $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);

最佳答案

当前，您的最终SQL将如下所示：

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')

除非计划将语句转换为MySQL视图，否则为什么不考虑使用php条件语句构建更简洁，更简单的SQL语句。

$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";
}