Home>
Requirements

We have created a mechanism to switch database records by specifying the date and time.

For example, if there are 3 records with id1, id2 and id3
id1 8/20 released
id2 8/24 release
id3 8/27 release (reservation only here)

Currently 8/25, so only the record for id2 is displayed now,
I want to switch the display to the record of "id3" when 8/27 comes

Problems

Issue 1. If SQL LIMIT is set to 1, the record of "id3" has not reached the timer time.
Nothing is displayed.

Issue issue 2 and if LIMIT is set to 2, now only the record with id2 is displayed
When the date comes, both "id2" and "id3" will be displayed.

Problem 3. Naturally, if LIMIT is not specified, the current time is "id1" and "id2".
Eventually everything will be displayed

How can I switch the display of records successfully?

<? php
    $dsn = 'mysql: dbname = test2;host = localhost;charset = utf8';
    $user = 'root';
    $password = '';
    $dbh = new PDO ($dsn, $user, $password);
    $dbh->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
    $latest_sql = 'SELECT * FROM sample ORDER BY ID DESC LIMIT 1';
    $latest_stmt = $dbh->prepare ($latest_sql);
    $latest_stmt->execute ();
    while ($latest_rec = $latest_stmt->fetch (PDO :: FETCH_ASSOC)) {
        $latest_release_start = $latest_rec ['release_start'];
        $todaytime = new DateTime ();
        $startTime = new DateTime ($latest_release_start);
        if ($todaytime>= $startTime) {
            echo $latest_release_start. '<br>';
        }
    }
    $dbh = null;
  • Answer # 1

    Set the timer time as the current date and time of the DB server, and get the maximum publication date before that date and time.

    SELECT * FROM sample
    where release_start = (
            select max (release_start) form sample where release_start<= CURRENT_TIMESTAMP
          )

  • Answer # 2

    Wouldn't it be better to narrow down the data before the current date?

    WHERE current date>= published date


    It looks like this