Detailed explanation of Symfony2 input time for query

This article mainly introduces the method of Symfony2 querying the input time, and analyzes the relevant operation skills of Symfony2 for converting and querying the input time of mysql and MongoDB in combination with examples. Friends who need it can Refer to the following

The example of this article describes the method of querying the input time in Symfony2. Share it with everyone for your reference, the details are as follows:

Under normal circumstances: when the front end inputs a time, we usually modify the time to a timestamp

strtotime — Parse any English text datetime description into a Unix timestamp

For example:

$startTimestamp = strtotime($startDate);
$endTimestamp = strtotime($endDate);

then : If it is just time, in order to prevent the time passed by others from being falsified, you need to modify the time into the form of Y-m-d

$start = date('Y-m-d 00:00:00', $startTimestamp);
$end = date('Y-m-d 23:59:59', $endTimestamp);

1. Use in MySQL

if(empty($startDate)) {
  throw new \Exception('起始时间不为空', BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
if (empty($endDate)) {
  $endDate = $startDate;
}
$startTimestamp = strtotime($startDate);
$endTimestamp = strtotime($endDate);
if ($startTimestamp > $endTimestamp) {
  throw new \Exception('时间参数错误', BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
if ($status == InventoryOrder::STATUS_SUBMITTED) {
  $index = 'i.submitTime';
} else if ($status == InventoryOrder::STATUS_UNSUBMITTED) {
  $index = 'i.createTime';
} else if (empty($status)) {
  $index = 'i.createTime';
} else {
  throw new \Exception('时间格式不正确', BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
$sql = 'SELECT i FROM AppBundle:InventoryOrder i WHERE ';
$sql .= $index;
$sql .= ' BETWEEN :startDate AND :endDate ';
$start = date('Y-m-d 00:00:00', $startTimestamp);
$end = date('Y-m-d 23:59:59', $endTimestamp);
$params['endDate'] = $end;
$params['startDate'] = $start;
if (!empty($status)) {
  $sql .= ' AND i.status = :status';
  $params['status'] = $status;
}
$sql .=' ORDER By i.createTime DESC';
$query = $this->entityManager->createQuery($sql);
$orderList = $query->setParameters($params)->getResult();

2. Time input and query examples in mongodb

There are actually two pitfalls in this ：

@ ->field('submit_time')->gt(new \DateTime($start))
->field('submit_time')->lt(new \DateTime($end))

Here, for time query, greater than and less than, an object must be passed.

$query->field('status')->equals($status);

Here, mongodb will not help you identify this as an int type by default. What type it is, it must be passed in manually.

$data = array();
if (!isset($startDate)) {
  throw new \Exception("参数不正确", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
if (empty($endDate)) {
  $endDate = $startDate;
}
$startTimestamp = strtotime($startDate);
$endTimestamp = strtotime($endDate);
if ($startTimestamp > $endTimestamp) {
  throw new \Exception("参数不正确", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
$start = date('Y-m-d 00:00:00', $startTimestamp);
$end = date('Y-m-d 23:59:59', $endTimestamp);
$scanner = Order::FROM_TYPE_SCANNER;
$query = $this->documentManager
  ->createQueryBuilder('AppBundle:Order')
  ->field('submit_time')->gt(new \DateTime($start))
  ->field('submit_time')->lt(new \DateTime($end))
  ->field('from_type')->equals("$scanner");
if (!empty($status) && in_array($status, array(Order::STATUS_CANCELLED, Order::STATUS_SUBMITTED))) {
  $status = $status + 0;
  $query->field('status')->equals($status);
} else if (empty($status)) {
  $status = Order::STATUS_SUBMITTED + 0;
  $query->field('status')->equals($status);
} else {
  throw new \Exception("参数不正确", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
$orderList = $query->sort('create_time', 'DESC')
  ->getQuery()
  ->execute();

The above is the detailed content of Detailed explanation of Symfony2 input time for query. For more information, please follow other related articles on the PHP Chinese website!