从我的tabel中选择日期=特定日期

Question

我在表中的日期是 TIMESTAMP 2016-05-28 18:39:00

现在我尝试从表中选择日期= 2016-05-28

这是我的查询

    $getReport = $db->prepare('SELECT a.proId, a.userId, DATE(a.date), a.status, a.canceledBy, b.id, b.pName, b.pPrice FROM purchaseshistory AS a INNER JOIN products AS b ON(a.proId=b.id) WHERE a.date=?');
    $getReport->bind_param('i', $dateOfBirth);
    $getReport->execute();
    $getReport->bind_result($proId, $userId, $date, $status, $canceledBy, $id, $pName, $pPrice);
$getReport->store_result();
while ($getReport->fetch()) {

日期来自这里

$dateOfBirth = $_POST['dateOfBirth'];

结果为空

Answer 1

可能是您需要正确的日期格式

SELECT a.proId, a.userId, 
DATE(a.date), a.status, a.canceledBy, b.id, b.pName, b.pPrice 
FROM purchaseshistory AS a 
INNER JOIN products AS b ON(a.proId=b.id) 
WHERE date_format(a.date,'%Y-%m-%d') = ?

Answer 2

您已将a.date转换为选择的日期，但在where子句中不转换为日期，我建议修改您的查询如下：

SELECT 
  ph.proId, ph.userId, DATE(ph.date), ph.status, ph.canceledBy,
  p.id, p.pName, p.pPrice 
FROM purchaseshistory AS ph 
  INNER JOIN products AS p ON ph.proId = p.id
WHERE DATE(ph.date) = ?