Conversion of dates in PHP and SQL

Question

I am trying to get past fixtures for a football website. A user will add a fixture into the database and then I want the PHP script to return all fixtures that have a date older than or equal to today. This will then populate a drop-down to select the match and enter a score.

    require 'connect-mysql.php';

    $sql = $conn->query("SELECT 
                            * 
                          FROM 
                             fixtures 
                          WHERE 
                             fixture_date <= " .date("Y-m-d"). " 
                          ORDER BY 
                             fixture_date DESC");

    $rows = array(); 

    while ($row = $sql->fetch_assoc()) {
        $rows[] = $row;
    }

    echo json_encode($rows); // Parse to JSON and print.

The current output is nothing when I have database entries with a date of yesterday.

For example, I'd expect it to output a result if there was a fixture in there for today because I have put equal to also.

@LukeVarty . . . Use parameters! If you did, you would never have had this problem. — Gordon Linoff
– Gordon Linoff, Commented Aug 10, 2019 at 11:20

Gordon Linoff · Accepted Answer · 2019-08-10 11:22:17Z

2

I want the PHP script to return all fixtures that have a date older than or equal to today.

Under most circumstances, you can probably trust that "today" for the user is the same as "today" on the database. There is no need to pass in the value. Just use curdate() or a similar built-in value:

SELECT f.* 
FROM fixtures f
WHERE f.fixture_date <= curdate()
ORDER BY fixture_date DESC;

answered Aug 10, 2019 at 11:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KIKO Software Over a year ago

I upvoted your answer because this is a useful suggestion. Just keep in mind that you need to make sure that the time that PHP reports matches the time that MySQL reports. They could be out by hours, due to timezone differences, or simply by a few seconds because they run on different servers.

KIKO Software · Accepted Answer · 2019-08-10 08:45:31Z

1

There's an error in your query, the quotes around the PHP date are missing. Try this:

 $sql = $conn->query("SELECT 
                        * 
                      FROM 
                         fixtures 
                      WHERE 
                         fixture_date <= '" .date("Y-m-d"). "' 
                      ORDER BY 
                         fixture_date DESC");

Better still: Check for MySQL errors.

answered Aug 10, 2019 at 8:45

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

3 Comments

Luke Varty Over a year ago

Ah perfect, thank you. Something so simple! Appreciate it.

Gordon Linoff Over a year ago

A solution that munges query strings with values rather than using parameters should not be upvoted.

KIKO Software Over a year ago

@GordonLinoff Somebody stubbornly upvoted my answer after you commented. Probably because you haven't given any reason for your comment? I do agree that your alternative, using curdate(), looks nicer, but it can also cause subtle bugs when MySQL time is not equal to PHP time. These bugs can, of course, be prevented, but why bother? There's something to say for always using parameters in queries (reason: SQL-injection), but in this case the date can never be user input, so it is not really necessary. We are allowed to use common sense, aren't we?

Collectives™ on Stack Overflow

Conversion of dates in PHP and SQL

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related