sql php confuse complex

PHP code (relevant)

$db = '2011-02-28'; 开发者_如何学C$a = '2011-02-01';

and this is the part of the query

    LEFT JOIN
        abc
    ON
        abc.date between '$a' and '$db'

This query show following results :

1 2011-02-08
6 2011-02-09
6 2011-02-11
1 2011-02-13

but what i want is to get 0 as a result if there's n rows for other dates.

---

You can't do that (not without joining a table containing all dates, at least).

Displaying the 0s for dates with no data is the job of your application, not the database. It's trivial, just write a loop from the start date to the end date, and output the 0 for those dates with no rows in your result set.

SQL's job is to tell you what data is there, not what isn't.

while ($row = mysql_fetch_array($result)) {
    $results[$row['date']] = $row['count'];
}

for ($time = strtotime('2011-02-01'); $time <= strtotime('2011-02-28'); $time += 86400) {
    $datestr = date('Y-m-d', $time);
    if (isset($results[$datestr])) {
        echo "Count for date $datestr: " . $results[$datestr];
    } else {
        echo "Count for date $datestr: 0";
    }
}

---

From the limited information you give about what problem you are trying to solve I would guess you are trying to find "free appointments" or similar.

If so then this should help you out. http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#98

This website has a number of query patterns that will help your thinking and design of your database.