MYSQL - 如何按时间查询数据库

I have say 12 results in a database with columns called

我在一个名为数据库的数据库中说了12个结果

thread

message

time_posted

there are 12 results and they all have the same thread witch is 1002.

有12个结果,他们都有相同的线程女巫是1002。

I'm trying to get all results coming after time_posted "2013-03-19 17:36:08". There are at least 4 entries either side so I should be getting 4 entries that come after this time.

我试图在time_posted“2013-03-19 17:36:08”之后得到所有结果。任何一方至少有4个条目,所以我应该在这之后得到4个条目。

So far out of everything I have tried, I have either received an empty array or I got everything except from the row that has 2013-03-19 17:36:08 as a timestamp.

到目前为止,我已经尝试了一切,我要么收到一个空数组,要么除了从2013-03-19 17:36:08作为时间戳的行之外我得到了所有内容。

Here is what I have tried:

这是我尝试过的:

    public function get_messages($thread){

        $time = '2013-03-19 17:36:08';

        $statement = $this->database->prepare("SELECT * FROM chat WHERE thread = ? AND time_posted > '$time'");

        $statement->bindParam(1, $thread);

        $statement->execute();

        $this->result = $statement->fetchAll(PDO::FETCH_ASSOC);

        return $this->result;

    }

I have tried it with both < AND >

我用 试了一下

Thanks in advance for any help.

在此先感谢您的帮助。

All the best.

祝一切顺利。

Connor

1 个解决方案

#1

Try this:

    $time = '2013-03-19 17:36:08';

    $statement = $this->database->prepare("SELECT * FROM chat WHERE thread = ? AND time_posted >= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')");

    $statement->bindParam(1, $thread);
    $statement->bindParam(2, $time );

    $statement->execute();