Search code examples
phpmysqldifference

Difference between now() and one column with datum (date) in MySql

I don't have too much experience with the PHP programming language, so I ask you for help solving a small problem.

I have several users in the tabele with their data and one column that lists the date when they need to report to the interview.

I need a sql query syntax to check the following:

Does the database have a user whose date in the table is the same or larger than it currently is. If a user is found, then my program will send an email to remind him.

I will use CRON JOB after to refresh the index.php, thats okay, but bothers me the most that I don't know the date comparison syntax. Otherwise the date in mysql database for each user is entered in the format 2020-02-15

$datenow = date("Y-m-d");
echo $datenow;
$sql = "SELECT * FROM 'users' WHERE 'report_date' >= '".$datenow."'";
if ($result = mysqli_query ($con, $sql)) {
    if (mysqli_num_rows($result)> 0) {       
        while($rows = mysqli_fetch_assoc ($result)) {                      
            $id = $row['id'];
            blablablabla
        }
Solution

  • bothers me the most that I don't know the date comparison syntax

    You seem to be looking for MySQL date function current_date(), which is the equivalent of php function call date("Y-m-d"):

    SELECT * FROM users WHERE report_date >= current_date;

    Side note: do not surround the column names with single quotes, otherwise it turns it to a literal string, so you end up comparing string 'report_date' with the date, which will not do what you want. Same goes for the table name, that should not be single quoted either.