How to extract MySQL data monthwise and daywise to populate table to draw graph using PHP libchart?

I have a table that has rows like CALL_ID, Timestamp, Date, First_Name, Last_Name, Status and so on..

What I need to do is draw graphs based on the data, filtered according to their dates. That is for the Month-to-date graph, I need to show the number of rows matching status=approved for each day of the month uptil current date.

And, for the Year-to-date graph, I need to show the number of rows matching Status=approved for each month of the year uptil current month.

My idea of doing it is this -

For Month-to-date:

$temp = date("Y-m-");
while($i<=date("d"))
{
$query = "SELECT call_id FROM main WHERE status='approved' AND date='".$temp.$i."'";
$result = mysql_query($query, $link) or die("",mysql_error());
$count[i]= mysql_num_rows($res开发者_如何学编程ult);
}

For Date-to-year:

while ($i <= date("m"))
{
    $query = "SELECT call_id FROM main WHERE status='approved' AND date BETWEEN'".$today_y."-".$i."-01' AND '".$today_y."-".$i."-31";
    $result = mysql_query($query, $link) or die("",mysql_error());
    $count_y[i]= mysql_num_rows($result);
}

And then I was thinking of pushing these counts present in the array by a "while" loop into another temporary tables (one for MTD and one for YTD) and then using PHP libchart to draw the graph from those tables.

Is there a better way to do this? Or is this the only way?

You can accomplish this a lot easier just using mysql and getting rid of a lot of your php loops. Something like this for month-to-date:

$query="SELECT COUNT(call_id) as 'num_records', date FROM main WHERE status='approved' AND CONCAT(YEAR(date),MONTH(date)) = CONCAT(YEAR(NOW()),MONTH(NOW())) GROUP BY date

That query will give a row showing the date and the total records for that day, for every day month-to-date.