动态 SQL 查询 PHP 与 AND - dynamic sql query php with AND

dynamic sql query php with AND

本文关键字：AND PHP 查询 SQL 动态 | 更新日期: 2023-09-27

我有一些sql代码：

    $id = "2000";
    $model = $_GET["model"]
    $result = mysql_query("SELECT
    Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
    count(Base.Data.Data_ID) As `U_Count`
    FROM
        Base.Data
    WHERE
        Product = '$id'
        AND model in ('a','b','c')
    GROUP BY ");

我想使查询的 AND 部分成为动态的。像这样：

$m= $model;
switch ($m)
{
    case "basic":
        $m = "AND model in ('a','b','c')";
        break;
    case "upgrade":
        $m = "AND model in ('d','e','f')";
        break;
    default:
        $m = "AND model in ('a','b','c')";
}

我把$m放在查询中，但它不起作用：

WHERE
      Product = '$id'
      '$m'

任何建议将不胜感激。

你应该

使用OR运算符而不是AND，或者最好使用IN语句。因为我想模型不能同时等于a和b，这是不可能的因此，部分代码应如下所示：

$m = " AND model in ('a','b','c')";

编辑：您可以像这样简化代码：

$id = "2000";
$model = $_GET["model"]

$m= $model;
switch ($m)
{
    case "upgrade":
    $mIN = "'d','e','c'";
        break;
    case "basic":
    default:
    $mIN = "'a','b','c'";
        break;
}

$result = mysql_query("SELECT
Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
count(Base.Data.Data_ID) As `U_Count`
FROM
    Base.Data
WHERE
    Product = '$id'
    AND model in ($mIn)
GROUP BY ");

删除$m两边的引号。此外，添加空格并将 AND 替换为正确的条件：

$m= $model;
switch ($m)
{
    case "basic":
        $m = " AND model in ('a','b','c') ";
        break;
    case "upgrade":
        $m = " AND model in ('d','e','f') ";
        break;
    default:
        $m = " AND model in ('a','b','c') ";
}

然后做：

$result = mysql_query("SELECT
Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
count(Base.Data.Data_ID) As `U_Count`
FROM
    Base.Data
WHERE
    Product = '$id'
    $m ");

您还可以在传递 SQL 之前将其创建为字符串。If 还可以更轻松地在运行查询之前对其进行调试。

    $querytop = "SELECT
        Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
        count(Base.Data.Data_ID) As `U_Count`
        FROM
            Base.Data
        WHERE
            Product = '$id'";
    $m= $model;
    switch ($m)
    {
        case "basic":
            $m = " AND model = 'a'
        AND model = 'b'
        AND model = 'c' ";
            break;
        case "upgrade":
            $m = " AND model = 'd'
        AND model = 'e'
        AND model = 'f' ";
            break;
        default:
            $m = " AND model = 'a'
        AND model = 'b'
        AND model = 'c' ";
    }        
    $sqlquery = $querytop . $m;
    $result = mysql_query("$sqlquery");