我正在其中应该有开始和结束日期内没有时间所以理论上验证应该检查一个开始和结束日期的时间框架内没有日期/时间会议室预约系统。
我有两个表，我可以开始和结束日期，以便只列我感兴趣的，此刻是这些罚款插入它
会议室
| ------------------------------------ |
| - bookingtime - | -bookingend- |
据我所知，完整性检查和校验我可以做psudocode背后的原理。 这是到目前为止，我已经得到了代码 - >
p4a_db::singleton()->query("INSERT INTO meetingrooms(location_id, bookingtime, bookingend, merono_id)
WHERE bookingtime < " . $date . " AND bookingend > " . $date . "
OR
bookingdate < " . $date . " AND bookingend > " . $dateend . "
VALUES(?,?,?,?)",
array($location, $date, $dateend, $merono));
我不想将数据插入直接插入语句，但直到我知道如何做到这一点我坚持，所以这个问题，
如何进行完整性检查的数据被插入，这样我不明白预订的时间内日之前 。
任何帮助将不胜感激。
Edit:
I've been overthinking my answer and I realized that the old solution will not work in your case since you need the time span, comparing the start and end date is useless.
My way of processing this would be:
- Save the dates as int, use 24h system (7:40am is 740, 9:50pm is 2150)
- Check for stored dates where: (
Start<NewStart<End)
- Check for stored dates where: (
Start<NewEnd<End)
- When processing several rooms, just store room number + time as int. That way you can still use the method from 2 and 3.
2 and 3 can be done in a sql query, check out this link.
Old answer (checking for duplicates)
This is an example of how to check for duplicates (in this case email) before inserting the text:
$emailexist = $mysqli->prepare("select email from users where email = ?");
$emailexist->bind_param('s', $email);
$emailexist->execute();
$emailexist->store_result();
if ($emailexist->num_rows > 0) {
$emailexist->close();
$mysqli->close();
return true;
}
else {
$emailexist->close();
$mysqli->close();
return false;
}
It checks if there are rows which contain the string. If so (if number of rows higher than 0) it returns true (which means, the date already exists).
You can just adapt this to you code.
However, you could also just set the columns to UNIQUE. Then you get an error when trying to insert it. It is easier and you won't have problems with concurrent connections.
一个长期且深入的搜索后，现在我已经得到了这种方法的工作示例，以防范SQL注入的方法以来，这里的代码;
if ($this->BookingValue == 1)
{
$sql = "SELECT COUNT(*) as num FROM meeting_room_bookings
WHERE
(
(? < start_at AND ? > start_at)
OR
(? > start_at AND ? < end_at)
)
AND
meeting_room_id = ?";
$result = p4a_db::singleton()->fetchRow($sql, array($date, $date, $date, $dateend, $merono));
if ( 0 == $result["num"] )
{
p4a_db::singleton()->query("INSERT INTO meeting_room_bookings (start_at, end_at, meeting_room_id)
VALUES
(?,?,?)", array($date, $dateend, $merono));
return true;
}
else
{
return false;
没有太多关于这个代码来解释，但在差异来看，（不包括与表中列名的变化）设定值之前，现在准备的查询，则有可能在使用它if语句，因此允许验证，以发生过滤不同日期之间的结果。
伴随着这一点，我已经添加了验证，以阻止其他会议室日期被列入通过的声明中AND声明，其中会议室ID被limeted为单个值。
尽管现在，这将导致到一个单独的问题是，来自这种说法另一抛出错误，我知道插入声音但这个准备好的声明的东西导致错误：
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens File: Pdo.php, Line: 234
虽然现在我期待到从准备好的语句抛出时，有一个解决方法，感谢您的帮助会更新这个答案一个错误。
