Select all orders for the last 30 days and calculate the quantity per day

Question

I’m trying to select all orders for the last 30 days from one customer, so I need customer_id = "$ customer_id" to calculate how many orders I have for each day for this single customer.

I need to finish an 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 a table called "orders" with identifier, customer and date field.

I found these questions SQL query to calculate the total number of orders per day? but he doesn’t help me, or I don’t understand him very well, by the way, I'm a beginner. Thanks!

ps what I managed to do was to select all orders for the 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 the MySQL EXTRACT function to select a day from a date field, and then group by results accordingly. I have not tried, 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)