ORDER_BY日期LIMIT 1 [英] ORDER_BY date LIMIT 1

问题描述

我有一个名为notify的表，其中包含(搜索者，捐助者，日期)列

类型(datetime)的日期列，它存储以下格式YYYY-MM-DD HH:MM:SS

我试图从通知表中选择具有最新日期的1条记录，然后进行比较日期和当前日期，并计算两个日期之间的天数.

I have table named notify with (seeker, donor, date) columns

the date column of type (datetime) and it stores the following format YYYY-MM-DD HH:MM:SS

I'm trying to SELECT 1 record with the latest date from notify table and then compare the date with the current date and calculate the number of days between tow dates..

<?php

session_start();
$email = $_GET['email'];
date_default_timezone_set('Asia/Riyadh');
$time = date("Y-m-d H:i:s");

$note = "SELECT * FROM notify WHERE seeker='".$_SESSION['email']."'AND donor='".$email."' ORDER_BY `date` DESC LIMIT 1";
$st = $conn->prepare($note);
$st->execute();

if($found = $st->fetch(PDO::FETCH_ASSOC)){
    $now = $time;
    $old_date = strtotime($found['date']);
    $dateif = $now - $old_date;

    if(floor($dateif/(60*60*24)) >= 7){
    echo "the difference between tow dates is 7 days or more";
    } else { echo "difference between tow dates is less than 7 days";}
}
?>

代码不起作用！
我的通知表中只有一条记录，该记录的日期为2013-04-22 09:15:47

the code is not working !
i have only one record in my notify table with this value in date 2013-04-22 09:15:47

推荐答案

首先，您应该使用如下准备好的语句:

First of all, you should use prepared statements like this:

$note = "SELECT * 
    FROM notify 
    WHERE seeker=:seeker AND donor=:donor 
    ORDER BY `date` DESC
    LIMIT 1";

$st = $conn->prepare($note);
$st->execute(array(
    ':seeker' => $_SESSION['email'],
    ':donor' => $email,
);

没有占位符，您仍然可以使用SQL注入.

第二，您不能通过这种方式将字符串与整数进行比较:

Second, you can't compare a string with an integer in this way:

$now = $time; // string
$old_date = strtotime($found['date']); // integer
$dateif = $now - $old_date; // dunno?

您应该将苹果与苹果进行比较:

You should compare apples with apples:

$seven_days_ago = strtotime('-7 days');
$old_date = strtotime($found['date']);

if ($old_date > $seven_days_ago) {
    echo "difference between tow dates is less than 7 days";
} else {
    echo "the difference between tow dates is 7 days or more";
}

这篇关于ORDER_BY日期LIMIT 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！