count the number of Database results based on the month and year(timestamp)

Question

I am trying to count the number of orders that have been made for each restaurant in my db, this month. I was wondering how you would count the number of restaurants based on the month and year.

I have had a attempt:

   $mm = date('m');
   $yy = date('Y');
   $month_sales = mysqli_query($dbc, "SELECT COUNT(Order_ID)FROM ord_dets WHERE 
   Resturant_ID='$Restaurant_id' AND DATEPART(yyyy,Date_Time_Placed ) ='$yy' AND DATEPART(mm,OrderDate)='$mm'");

   $month_sales_row = $month_sales->fetch_row()[0];
   printf("%d \n", $month_sales_row);

   mysqli_free_result($month_sales);

But i am getting this error:

Uncaught mysqli_sql_exception: FUNCTION one_delivery.DATEPART does not exist in..

Date_Time_Placed is the column in my table that holds the timestamp for my order.

I am using php 7

You might want to take a look at this: http://stackoverflow.com/questions/13402012/select-row-by-datepart — Chin Leung, Jul 02 '16 at 15:35
To add to what @ChinLeung said. DATEPART is a `T-SQL` or more commonly named, `mssql` function not `MySQL`. Please do not get these 2 confused as whilst they have their similarities, they have different functions. Here is a nice comparison http://troels.arvin.dk/db/rdbms/ — Matt, Jul 02 '16 at 15:38
You can use `YEAR()` and `MONTH()` functions in MySQL; e.g: `WHERE YEAR(my_datetime) = '2016' AND MONTH(my_datetime) = '6'` etc. — Darragh Enright, Jul 02 '16 at 15:43
Just realised that you indicated that at least one of your columns is a timestamp - which I assume is a UNIX timestamp, and not a datetime — Darragh Enright, Jul 02 '16 at 15:53

Darragh Enright · Accepted Answer · 2016-07-02T15:58:26.333

If Date_Time_Placed and OrderDate are valid date/datetime strings; e.g: 2016-07-02 16:57:00, you can use built-in MySQL functions YEAR() and MONTH():

SELECT COUNT(Order_ID)
  FROM ord_dets 
 WHERE Resturant_ID='$Restaurant_id' 
   AND YEAR(Date_Time_Placed) = '$yy' 
   AND MONTH(OrderDate) = '$mm'

If your dates are UNIX timestamps you will need to convert them first with FROM_UNIXTIME(); e.g:

YEAR(FROM_UNIXTIME(Date_Time_Placed))

Hope this helps :)

count the number of Database results based on the month and year(timestamp)

1 Answers1