user6456767 user6456767 - 1 year ago 63
PHP Question

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

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);


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

Answer Source

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():

  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:


Hope this helps :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download