Retrieve missing dates from database via MySQL

So I have a table where I collect data for the jobs that I do. Each time I create a job I assign it a date. The problem with this is the days I don't have jobs aren't stored in the database therefore when I graph my data I never see the days that I had zero jobs.

My current query looks like this:

SELECT job_data_date, SUM(job_data_invoice_amount) as job_data_date_income 
FROM job_data 
WHERE job_data_date >= '2010-05-05' 
GROUP BY job_data_date 
ORDER BY job_data_date;

The output is:

|   job_data_date   |   job_data_date_income    |
|   2010-05-17   开发者_运维问答   |   125                     |
|   2010-05-18      |   190                     |
|   2010-05-20      |   170                     |

As you can see from the example output the 2010-05-19 would not show up in the results because it was never stored there.

Is there a way to show the dates that are missing?

Thank you,

Marat

One idea is that you could have a table with all of the dates in it that you want to show and then do an outer join with that table.

So if you had a table called alldates with one column (job_data_date):

SELECT ad.job_data_date, SUM(job_data_invoice_amount) as job_data_date_income 
FROM alldates ad left outer join job_data jd on ad.job_data_date = jd.job_data_date 
WHERE ad.job_data_date >= '2010-05-05' 
GROUP BY ad.job_data_date 
ORDER BY ad.job_data_date;

The down side is that you would need to keep this table populated with all of the dates you want to show.

There's no reasonable way to do this using pure SQL, on MySQL at least, without creating a table with every date ever devised. Your best option is to alter the application that's using the results of that query to fill in the holes itself. Rather than graphing only the values it received, construct its own set of values with a simple loop; counting up one day at a time, filling in values from the query wherever they're available.