selecting all orders for last 30 days, and counting how many per day

Question

I am trying to select all orders for last 30 days from one customer, so I need to have customer_id = "$customer_id" and count how many orders I have per each day for that one customer.

I need to end up with array like this

Array ( 
  [1] => Array (
                 [orders] => 41
                 [date] => 2011-06-13 17:43:50 
               )
  [2] => Array (
                 [orders] => 11
                 [date] => 2011-07-13 17:43:50  
               )
  [4] => Array (
                 [orders] => 2
                 [date] => 2011-12-13 17:43:50  
               )

  and so on... for 30 days, if some day I dont have any orders, I dont need array or [orders] = 0 ...

}

I have table named "orders" with id, customer_id and date field.

I found this questions SQL query for Calculating Total No. of Orders per Day? but its not helping me, or I dont understand it very well, btw I am beginner. Thanks!

p.s. what I managed to do, is to select all orders for last 30 days.

$this->db->query("SELECT * FROM orders WHERE customer_id=" . $customer['id'] . " AND date > ADDDATE(CURDATE(), INTERVAL -30 DAY)")->result_array();

Answer 1

Use MySQL EXTRACT function to fetch day from your date field and then group by results according to this. I haven't try it but the following query should work:

SELECT COUNT(*) AS orders, date_field
FROM your_table
WHERE customer_id=$my_cusotmer
GROUP BY EXTRACT(DAY FROM date_field)