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!
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;
}
You can't select a whole day with UNIX timestamps because of their more accurate nature (i.e. seconds), you would need the textual version:
$data = array(date('Y-m-d', 1367971200));
$stmt = $dbh->prepare("SELECT COUNT(*)
FROM `report_coa_bal_hist`
WHERE `date` = ?
");
$stmt->execute($data);
$count = current($stmt->fetchAll(PDO::FETCH_COLUMN, 0));
return $count > 0;
Take note of any timezone differences between the server that runs your script and the database server itself.
there are many flaws with your code, not one:
So, the code have to be
$data = array('2013-05-07');
$sql = "SELECT 1 FROM report_coa_bal_hist WHERE `date` = ? LIMIT 1";
$stm = $dbh->prepare($sql);
$stm->execute($data);
return $stm->fetchColumn();