php - 如何使用php显示sql中开始日期和结束日期或开始时间和结束时间之间的数据？

Question

我开发了一个 Web 服务并将数据从 SQL 发送到 API。但是，是基于开始日期和结束日期或开始时间和结束时间，但结果并不完美。

CakePHP 查询代码:

$Home = $this->Home->find('all',array(
          'contain' => array('LiveVideo' => array(
            'conditions' => array(
                        array('OR' => array('DATE(LiveVideo.from_date) >=' => date('Y-m-d')),
                        array('DATE(LiveVideo.to_date) <=' => date('Y-m-d')))



                        //'LiveVideo.from_time >=' => date('h:i'),
                        //array('LiveVideo.to_time <=' => date('h:i'))

                )

            )),

          'fields' => array(),
          'order' => 'Home.created DESC',             
    ));

我想要在开始日期和开始时间或结束日期和结束时间之间得出结果。

Answer 1

这是日常应用中最基本的问题之一。如果问题仅与数据库相关，那么答案是最好的。

根据您应用的标签，我相信它与基于 PHP 的动态网页相关。如果您使用的是 Core PHP，那么您可以按照以下脚本部分进行操作，只需复制并粘贴即可。所有解释都以评论的形式存在:

<?php
/**
 * Connect to mysql server from php script
 * Please replace the credentials as per your environment configuration
 * Parameters details :
 * "localhost" -- It is Hostname where mysql server is installed
 * "root" -- Default username for mysql
 * "password" -- Password for MySQL Database user, Can be left blank if there is no password
 * "test" -- It is the database which contains project data
 */ 
$mysqli = new mysqli("localhost", "root", "password", "test");
/**
 * Now setting the query parameters
 */
$startDate="2015-01-01";
$endDate="2015-12-31";
/**
 * Lets execute query on MySQL
 * here used columns are
 * OrderDate -- replace it with your date field
 * OrderNo -- replace with fields you want in your results
 * orders -- replace with name of table you want to use 
 */
$mysqli->real_query("
     SELECT OrderDate,OrderNo
     FROM orders
     WHERE OrderDate BETWEEN '{$startDate}' AND '{$endDate}'
     ORDER BY OrderDate ASC
");
/**
 * Get result buffer
 */
$res = $mysqli->use_result();

echo "Result from table...<br />";
/**
 * Printing result on web page with while loop
 */
while ($row = $res->fetch_assoc()) {
   echo " OrderDate = " . $row['OrderDate'] . " OrderNo = " . $row['OrderNo'] . "<br />";
}

在 CakePHP 中

<?php

$start = new Time('2018-01-01');
$end = new Time('2018-01-31');

$table = $this->Tables->find('all')
  ->contain(['AssocTables' => function ($q) use ($start, $end){
    return $q->where(['AssocTables.start >=' => $start, 'AssocTables.end <=' => $end])
  }]);