CONCAT日期问题

Question

My database has y m d fields with no leading zeros in the month and day. I am trying to compare a records y-m-d against today's date. Greater than or equal show a greenlight image else red light. It's not working! HELP!

    $q = "SELECT * FROM pec_mbbtmssgs WHERE activity='bike' AND CONCAT(`y`,'-','m','-','d')  >= date('Y-n-j')";
$r = mysql_query($q);
$row = mysql_fetch_assoc($r);
$activity = $row['activity'];

if($activity == "bike") {
    $image = "greenlight25";
}
else {
    $image = "redlight25";
}

echo '<img src=http://img/' . $image . '.gif />';
?>

Answer 1

Your usage of CONCAT() is a bit off, as database-columns should either be wrapped in backticks or nothing at all, and strings/separators in singlequotes '.

You can also just do DATE(NOW()) to get the current date, and compare against this. Then you use the DATE() with the result of your CONCAT(), so it becomes like this

SELECT * 
FROM pec_mbbtmssgs 
WHERE activity='bike' 
  AND DATE(CONCAT(y, '-', m, '-', d)) >= DATE(NOW()) 

The simpler approach would be to just store your date in a DATE type column, instead of 3 separate ones. You can always extract the specific information you want (e.g. you can extract the month, even if your value is 2016-11-30).