如何检查MySQL'日期'列中是否存在特定日期？

Question

Using the code below, I'm having trouble checking whether a specified date exists in a MySQL 'date' column.

$data = array(1367971200);

$s=$dbh->prepare("
    SELECT DISTINCT
        `date`
    FROM
        `report_coa_bal_hist`
    WHERE
        UNIX_TIMESTAMP(`date`) = ?
");

if ($s->execute($data)) {

    if ($s['date'] == null) {

        return false;
    }

    return true;

}

It's returning false, despite the fact that I can see the date '2013-05-08' displayed in phpMyAdmin.

The table itself contains 70+ entries for that date. It always will do, if it contains any at all, but I just want to know whether it exists or not at this stage.

The date field is a MySQL 'date' type. I'm suspecting that the bug is in my structuring of the PDO calling of the query.

UPDATE

Updated $r['date'] to `$s['date']. I suspect that I still have an issue with the structure of that, but probably need to fix the query so that it gives us results before focusing on this.

Also tried running the query against the database directly and got an empty resultset, despite being able to see that the target date exists. Still baffled!

Answer 1

Try this

$data = array(1367971200);

$s=$dbh->prepare("
SELECT COUNT(`date`) as c_date
FROM
    `report_coa_bal_hist`
WHERE
    UNIX_TIMESTAMP(`date`) = ?");

if ($s->execute($data)) {
    $result = $s->fetch(PDO::FETCH_ASSOC);
    if ($result['c_date'] > 0) {
        return false;
    }
    return true;
}