How to query the database within a period of time in php

How to query the database within a period of time in php: 1. Use the "date_format" function of mysql to first find out all the orders of the current month in the subquery; 2. Use the "date_format" function to convert the enterdate into days; 3. Just "group by" group statistics by day.

The operating environment of this tutorial: Windows 7 system, PHP version 8.1, Dell G3 computer.

php How to query the database within a period of time?

PHP MySQL implements daily data statistics optimization operation example for a period of time

In Internet projects, data analysis of the project is essential. Usually, the trend of total daily data changes within a certain period of time is calculated to adjust marketing strategies. Let’s look at the following cases.

Case

There is usually an order form in the e-commerce platform to record all order information. Now we need to count the number of orders and sales amount per day in a certain month to draw the following statistical chart for data analysis.

The order table data structure is as follows:

order_id	order_sn	total_price	enterdate
25396	A4E610E250C2D378D7EC94179E14617F	2306.00	2017-04-01 17:23:26
#25397	EAD217C0533455EECDDE39659ABCDAE9	17.90	2017-04-01 22:15:18
25398	032E6941DAD44F29651B53C41F6B48A0	163.03	2017-04-02 07:24:36

At this time, how to query the number of orders placed on each day of a certain month and the total amount?

General method

First of all, the easiest way to think of is to use the php function cal_days_in_month() to obtain The number of days in the month, then construct an array of all the days in the month, and then query the total number of each day in the loop to construct a new array.

The code is as follows:

$month = '04';
$year = '2017';
$max_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);   //当月最后一天
//构造每天的数组
$days_arr = array();
for($i=1;$i<=$max_day;$i++){
  array_push($days_arr, $i);
}
$return = array();
//查询
foreach ($days_arr as $val){
  $min = $year.&#39;-&#39;.$month.&#39;-&#39;.$val.&#39; 00:00:00&#39;;
  $max = $year.&#39;-&#39;.$month.&#39;-&#39;.$val.&#39; 23:59:59&#39;;
  $sql = "select count(*) as total_num,sum(`total_price`) as amount from `orders` where `enterdate` >= {$min} and `enterdate` <= {$max}";
  $return[] = mysqli_query($sql);
}
return $return;

This SQL is simple, but it requires 30 queries each time, which seriously slows down the response time.

Optimization

How to use a sql to directly query the total quantity of each day?

At this time, you need to use the date_format function of mysql to first find out all the orders of the current month in the subquery, and convert the enterdate into days using the date_format function, and then group by Group statistics. The code is as follows:

$month = '04';
$year = '2017';
$max_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);   //当月最后一天
$min = $year.'-'.$month.'-01 00:00:00';
$max = $year.'-'.$month.'-'.$max_day.' 23:59:59';
$sql = "select t.enterdate,count(*) as total_num,sum(t.total_price) as amount (select date_format(enterdate,'%e') as enterdate,total_price from orders where enterdate between {$min} and {$max}) t group by t.enterdate order by t.enterdate";
$return = mysqli_query($sql);

In this way, reducing 30 queries to 1, the response time will be greatly improved.

Note:

1. Since all the data of the current month needs to be queried, this method is not suitable when the amount of data is too large.

2. In order to avoid data loss caused by no data on that day, after querying, the data should be processed according to needs.

Recommended learning: "PHP Video Tutorial"

The above is the detailed content of How to query the database within a period of time in php. For more information, please follow other related articles on the PHP Chinese website!