php - 在MySQL查询中添加字段

我需要在我们的查询之一中添加一个字段。我不是PHP程序员，我可以稍微解决一下。查询是：

if (_QUERYSTRING_) {
    switch ($intMode) {
        case -1:
            $result = mysqli_query($mysql,"
            SELECT   orders.id,
                     orders_addresses.strlastname,
                     orders_addresses.strfirstname,
                     orders_addresses.intprovince,
                     9 AS intmode,
                     Date(orders.dtimeorder) AS datepayment,
                     orders_costs.dbltotal AS dblamount,
                     orders_notes.strcod AS strtxn,
                     0 AS dblfee,
                     shipping_postalservices.strtracking"._LANG_." as strtrackingurl,
                     'À recevoir' AS strmode,
                     NULL AS strvendor
            FROM     orders
            JOIN     orders_costs
            ON       orders_costs.id = orders.id
            JOIN     orders_addresses
            ON       orders_addresses.id = orders.id
            JOIN     orders_notes
            ON       orders_notes.id = orders.id
            JOIN     shipping_postalservices
            ON       shipping_postalservices.id = orders_costs.intpostalservice
            WHERE    date(orders.dtimeorder) BETWEEN '".date("Y-m-d",$timeStart)."' AND      '".date("Y-m-d",$timeEnd)."'
            AND      orders.boolshipped = 1
            AND      orders.boolcanceled = 0
            AND      orders_costs.boolcod = 1
            AND      orders_costs.dbltotal > 0
            AND      NOT EXISTS
                     (
                        SELECT *
                        FROM   orders_payments
                        WHERE  orders_payments.intorder = orders.id
                        AND    orders_payments.intmode = 9
                        AND    orders_payments.dblamount > 0)
        GROUP BY orders.id
        ORDER BY orders.dtimeorder,
                 orders.id");
            break;
        default:
            $result = mysqli_query($mysql,"
            SELECT    orders.id,
                      orders_addresses.strlastname,
                      orders_addresses.strfirstname,
                      orders_addresses.intprovince,
                      orders_payments.intmode,
                      Date(orders_payments.dtimepayment) AS datepayment,
                      orders_payments.dblamount,
                      orders_payments.strtxn,
                      orders_payments.dblfee,
                      shipping_postalservices.strtracking"._LANG_." as strtrackingurl,
                      payments.strname"._LANG_." AS strmode,
                      payments.strvendor
            FROM      orders_payments
            JOIN      orders
            ON        orders.id = orders_payments.intorder
            JOIN      orders_costs
            ON        orders_costs.id = orders.id
            JOIN      orders_addresses
            ON        orders_addresses.id = orders.id
            JOIN      shipping_postalservices
            ON        shipping_postalservices.id = orders_costs.intpostalservice
            LEFT JOIN payments
            ON        payments.id = orders_payments.intmode
            WHERE     date(orders_payments.dtimepayment) BETWEEN '".date("Y-m-d",$timeStart)."' AND       '".date("Y-m-d",$timeEnd)."'".(!empty($intMode) ? "
            AND       orders_payments.intmode = '".$intMode."'" : NULL)."
            GROUP BY  orders.id,
                      orders_payments.intpayment
            ORDER BY  orders_payments.dtimepayment,
                      orders.id");
            break;
    }

需要添加的字段是orders_addresses.intProvince，以便可以将其显示在结果中。我试着了解一点，但我想这比我想的要复杂一些。它确实显示了省份，即数字。我的问题是，我该如何用实际名称“翻译”这些数字，使其显示为“ Ontario”而不是9？省的名称在另一个名为Province的表中。

最佳答案

您将需要添加另一个JOIN：

JOIN Province ON orders_addresses.intprovince = Province.x

然后在SELECT部分​​中，将orders_addresses.intprovince替换为Province.y

哪里

x =表Province中包含省ID的列
y =表Province中包含省名的列

关于php - 在MySQL查询中添加字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36920114/