在SQL查询中使用PHP来确定查询的下一部分应该如何 - Using PHP inside a SQL query to determine how the next part of the query should be

Using PHP inside a SQL query to determine how the next part of the query should be

本文关键字：查询一部分 SQL PHP | 更新日期: 2024-04-04

StackOverflow的第一篇文章。我希望我的问题足够好，而且表述得很好。

我的项目中有一个订单列表，有3个下拉菜单，下拉菜单分别是年、月和付款类型。

我只要求其中一个，因为解决方案对所有人来说都是通用的。

对于月份下拉列表，我有一个所有月份（1月至12月）的列表，值为01-12。我还有一个值为00的选项，这个选项我想表示所有的月份，所以如果用户选择这个选项，SQL查询应该忽略AND MONTH（）=值；

我使用的SQL查询如下。如果所有的字符串都设置为一个值，那么这个就可以正常工作了。

$sql = "SELECT      oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date
    FROM        oc_order
    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)
    AND         MONTH(date_added) = '$chosenmonth'
    AND         YEAR(date_added) = '$chosenyear'
    AND         oc_order.payment_code = '$chosenpayway'
    ORDER BY orderID ASC";

我想做的是将AND MONTH（）替换为以下内容：

". $chosenmonth != '00' ? 'AND MONTH(date_added) = ''$chosenmonth''' : '' ."

提前感谢！

更新：我现在已经解决了这个问题！感谢Barmar的协助。我使用echo进行调试，最终找到了解决方案。。也许有更好的方法可以做到这一点，但这对我来说很好。

$chosenmonth = $_GET["month"];
$chosenyear = $_GET["year"];
$chosenpayment = $_GET["pay"];
$ifmonth = ($chosenmonth != NULL ? "AND MONTH(date_added) = '$chosenmonth' " : "");
$ifyear = ($chosenyear != NULL ? "AND YEAR(date_added) = '$chosenyear' " : "");
$ifpay = ($chosenpayment != NULL ? "AND oc_order.payment_code = '$chosenpayment' " : "");
$sql = "SELECT  oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date
    FROM        oc_order
    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)
    ". $ifmonth . $ifyear . $ifpay ."       
    ORDER BY orderID ASC";

您需要在三元表达式周围使用括号，因为默认的优先级是以不同于您需要的方式组合事物。

$sql = "SELECT  oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date
    FROM        oc_order
    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)
    ". ($chosenmonth != '00' ? "AND MONTH(date_added) = '$chosenmonth'" : "") ."
    AND         YEAR(date_added) = '$chosenyear'
    AND         oc_order.payment_code = '$chosenpayway'
    ORDER BY orderID ASC";

如果您在一个单独的语句中执行条件语句，将更容易理解，并且不易出错。

$monthcheck = $chosenmonth != '00' ? "AND MONTH(date_added) = '$chosenmonth'" : "";
$sql = "SELECT  oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date
    FROM        oc_order
    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)
    $monthcheck
    AND         YEAR(date_added) = '$chosenyear'
    AND         oc_order.payment_code = '$chosenpayway'
    ORDER BY orderID ASC";