php - 如果值为空，则返回0

我的php脚本中有一个MySQL查询，目前运行良好。我唯一的问题是某些列返回空值。

如果这些列有数据，则它们返回值，但如果日期没有数据或记录，则它们返回null。我想要做的就是修改此查询，以便如果任何内容为null则返回“ 0”。

我不确定我应该使用IFNULL还是合并，但是我不熟悉将其应用于此查询的最佳方法。

任何帮助深表感谢。

$stmt3 = mysqli_prepare($conn2,
     "UPDATE ambition.ambition_totals a
        INNER JOIN
        (SELECT
            c.user AS UserID,
            COUNT(*) AS dealers,
            ROUND((al.NumberOfDealers / al.NumberOfDealerContacts) * 100 ,2)  AS percent
        FROM jfi_dealers.contact_events c
        JOIN jackson_id.users u
        ON c.user = u.id
        JOIN jfi_dealers.dealers d
        ON c.dealer_num = d.dealer_num
        LEFT JOIN (
          SELECT user_id, COUNT(*) AS NumberOfDealerContacts,
          SUM(CASE WHEN ( d.next_call_date + INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS NumberOfDealers
          FROM jackson_id.attr_list AS al
          JOIN jfi_dealers.dealers AS d ON d.csr = al.data
          WHERE al.attr_id = 14
          GROUP BY user_id) AS al
        ON al.user_id = c.user
        GROUP BY UserID) as cu
        on cu.UserID = a.ext_id
        SET a.dealers_contacted = cu.dealers,
          a.percent_up_to_date = cu.percent;
                ") or die(mysqli_error($conn2));

更新

带有IFNULL语句的版本：

UPDATE ambition.ambition_totals a
        INNER JOIN
        (SELECT
            c.user AS UserID,
            ifnull(count(*),0) AS dealers,
            ifnull(ROUND((al.NumberOfDealers / al.NumberOfDealerContacts) * 100 ,2),0)  AS percent
        FROM jfi_dealers.contact_events c
        JOIN jackson_id.users u
        ON c.user = u.id
        JOIN jfi_dealers.dealers d
        ON c.dealer_num = d.dealer_num
        LEFT JOIN (
          SELECT user_id, COUNT(*) AS NumberOfDealerContacts,
          SUM(CASE WHEN ( d.next_call_date + INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS NumberOfDealers
          FROM jackson_id.attr_list AS al
          JOIN jfi_dealers.dealers AS d ON d.csr = al.data
          WHERE al.attr_id = 14
          GROUP BY user_id) AS al
        ON al.user_id = c.user
        WHERE c.created_at >= CURDATE()
        GROUP BY UserID) as cu
        on cu.UserID = a.ext_id
        SET a.dealers_contacted = cu.dealers,
          a.percent_up_to_date = cu.percent;

最佳答案

是的，您可以为此使用IFNULL。但是请确保您确实想要这种行为。 PHP也熟悉NULL值，并且可以很好地处理它们。 0的含义非常不同。但是，如果您确实想要这种行为，只需将可能返回null的字段或语句包装在IFNULL中，例如：

SELECT IFNULL(user_id, 0);

但是您也可以在PHP本身中执行此操作，因此您不必修改查询：

if (is_null($result['field'])) {
  echo 0;
}

或者，如果您使用PHP 7+，则还可以使用null coalescing operator：

echo $result['field'] ?? 0;

关于php - 如果值为空，则返回0，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48263944/