>我有两个字段的表
competition {stateTime, endTime}
当我插入到该表时,我想确保我要插入的值不在该表中任何行的句点上我输入这个函数(PDO数据库)
function isCompetitionInAnotherCompetition($startTime, $endTime) {
$query = "SELECT * FROM competition";
$sth = $this->db->prepare($query);
$sth->execute(array());
while ($row = $sth->fetch()) {
if ($startTime >= $row['startTime'] && $startTime <= $row['endTime'])
return true;
if ($endTime >= $row['startTime'] && $endTime <= $row['endTime'])
return true;
}
return false;
}
但效果不佳,数据库中的每个日期都是yyyy-mm-dd
,示例2012-01-15
我可以建议不要编写一个函数来测试存在并返回返回记录的布尔值,这样您就可以稍后测试是否没有返回任何记录,但如果有,您可以在需要时使用它们。正如Alvin Wong建议的那样,你可以在sql中使用BETWEEN
,这样你就可以得到这样的东西。
function getCompetitionsBetween($startTime, $endTime) {
$startTime = date("Y-m-d", $startTime);
$endTome = date("Y-m-d", $startTime);
$query = "SELECT * FROM competition
WHERE start_time BETWEEN ? AND ?
OR end_time BETWEEN ? AND ?";
$sth = $this->db->prepare( $query );
$sth->execute( array($startTime, $endTime, $startTime, $endTime) );
return $sth->fetchAll();
}
后来/其他地方
$competitions = getCompetitionsBetween($startTime, $endTime);
if (empty(competitions)) {
$this->save();
} else {
echo ('sorry the following competitions conflict with these dates');
foreach($competitions as $k => $v) {
echo ($k . ':' . $v);
}
}