如何通过比较格式错误的日期字符串和今天的日期来过滤结果集行？

Question

I have a VARCHAR column in my mysql database that stores dates with commas rather than hyphens.

I want to fetch some data by comparing these dates against the current date. To get the current date, I am using the php5 method date("Y,m,d"); and declaring it as $date;

If the current date is less than a given row's date, then I want to retrieve it otherwise omit it from the resultset.

Here is my code

$date=date("Y,m,d"); //current date to check if expiry date is valid or not
try {
    $conn = new PDO("mysql:host=******;dbname=******", $username, "******");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $query = "select id, discount, expirydate, description, logouploader from table WHERE expirydate>= ? LIMIT 10";
    $statement = $conn->prepare($query);
    $statement->execute(array($date));
    foreach($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
        echo $row['expirydate'];
    }  
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}
$conn = null;

My code does not compare dates; it fetches all rows even though it was supposed to leave those records which are less than $date; // current date

How can I filter my resultset to only return rows which are not yet expired?

Answer 1

Here is the bad news: Your code is suffering from the ramifications of bad database design. If you are storing date values, never store them in anything other than a date-type column.

Here is the good news: Even if you don't repair your database structure (and you really, really should), you don't need any php to get your query working. In fact, you don't even need to employ a prepared statement because there are no variables to include.

Code: (SQLFiddle Demo)

SELECT discount,
       description,
       logouploader,
       DATE_FORMAT(STR_TO_DATE(expirydate, '%Y,%m,%d'), '%Y-%m-%d') AS `reformatted`
FROM `table`
WHERE STR_TO_DATE(expirydate, '%Y,%m,%d') >= CURDATE()
ORDER BY expirydate

This will yield all rows of discounts that have not yet expired (ordered by date).

You may notice that:

• You don't need to reformat expirydate in the ORDER BY clause because it is accurately sorted as a string.
• I could have just as easily called upon REPLACE() instead of STR_TO_DATE() to format your comma-separated date to hyphen-separated.
• DATE_FORMAT() will offer you great flexibility in formatting the date in your resultset, so that your php can do less processing.