php - 如果一天没有结果，MySQL 查询将得到 0

Question

我在 flot 中创建了一个折线图，除了没有结果的日子我需要它们返回结果 0 + 日期外，我都可以正常工作。这在mysql中可能吗？这是我当前的查询:

$chartQuery = "SELECT count(date) as counted_leads, UNIX_TIMESTAMP(date) as time FROM enquiries WHERE visibility != 'deleted' group by date";

或者我是否需要在我的 php 中执行此操作？这是我的代码:

<?php 
        $last_key = end(array_keys($chartResults));
        foreach ($chartResults as $item => $value)
        {
            $timestamp = round($value['time'] * 1000);

            if ($item == $last_key)
            {
                // last element
                echo '['.$timestamp.', '.htmlentities($value['counted_leads']).']';
            }
            else 
            {
                // not last element
                echo '['.$timestamp.', '.htmlentities($value['counted_leads']).'],';
            }
        }
        unset($value);
        ?>

Answer 1

SELECT 
     ifnull(count(date),0) as counted_leads, 
     UNIX_TIMESTAMP(date) as time 
FROM enquiries WHERE visibility != 'deleted' 
group by date

使用if null作为默认值