php - 如何确保在日期更改时从 shoutcast 历史记录中插入歌曲历史数据库而不重复？

Question

我正在从 shoutcast 服务器获取最近播放的 10 首歌曲，并想将它们插入我的数据库，以便跟踪在特定日期和时间播放的歌曲。

表结构如下:

`id_played`     int(11)        AUTO_INCREMENT
`date_played`   date
`time_played`   time
`artist`        varchar(255)
`title`         varchar(255)

这是我将歌曲插入表格的代码:

$date = date("Y-m-d");

for($i = 0; $i < $songsCount; $i++) {
    $stmtGetExisting = $mysqli->prepare("SELECT id_played FROM played WHERE date_played = ? AND time_played = ? AND artist = ? AND title = ?");
    $stmtGetExisting->bind_param("ssss", $date, $times[$i], $artists[$i], $titles[$i]);
    $stmtGetExisting->execute();
    $stmtGetExisting->store_result();
    $rows = $stmtGetExisting->num_rows;

    if($rows == 0) {
        $stmtInsertSong = $mysqli->prepare("INSERT INTO played SET date_played = ?, time_played = ?, artist = ?, title = ?");
        $stmtInsertSong->bind_param("ssss", $date, $times[$i], $artists[$i], $titles[$i]);
        $stmtInsertSong->execute();
    }
}

我想使用 cronjob 运行此脚本以自动保留所有播放歌曲的历史记录，因此此脚本将非常频繁地执行。

大多数时候它运行良好，但如果日期从 2015-02-23 更改为 2015-02-24，则在日期更改之前播放的歌曲将再次插入，但使用新日期。

确保不会再次插入在日期更改之前播放的歌曲的最佳方法是什么？

Answer 1

这听起来像是您正在从服务器获取歌曲播放时间，但使用当前日期作为播放日期。如果是这种情况，您可以按如下方式修改脚本:

$date = date("Y-m-d");
$time = date("H-i-s");

for($i = 0; $i < $songsCount; $i++) {
    //since the time is was played would be later then the current time (only time, not
    //datetime) you can check if the pulled time is greater then the current time

    if($time < $times[$i]) continue;

    $stmtGetExisting = $mysqli->prepare("SELECT id_played FROM played WHERE date_played = ? AND time_played = ? AND artist = ? AND title = ?");
    $stmtGetExisting->bind_param("ssss", $date, $times[$i], $artists[$i], $titles[$i]);
    $stmtGetExisting->execute();
    $stmtGetExisting->store_result();
    $rows = $stmtGetExisting->num_rows;

    if($rows == 0) {
        $stmtInsertSong = $mysqli->prepare("INSERT INTO played SET date_played = ?, time_played = ?, artist = ?, title = ?");
        $stmtInsertSong->bind_param("ssss", $date, $times[$i], $artists[$i], $titles[$i]);
        $stmtInsertSong->execute();
    }
}