MySQL通过日期时间完整性检查将行插入数据库 [英] MySQL INSERTing rows into a database with datetime sanity checks

问题描述

我正在制作一个会议室预订系统，其中在开始日期和结束日期之内应该没有时间，因此从理论上讲，验证应该在一个开始日期和结束日期的时间范围内不检查日期/时间.

I am making a meeting room booking system in which there should be no times within the start and end dates so in theory the validation should check for no dates/times within one start and end date time frame.

我有两个表，可以在其中插入开始日期和结束日期，因此我目前唯一感兴趣的列是这些

I have two tables, I can insert into it fine with both start and end dates so the only columns i am interested in at the moment are these

会议室
| ------------------------------------ |
|-预订时间-|- bookingend- |

meetingrooms
|------------------------------------|
|- bookingtime -|-bookingend-|

我了解健全性检查以及我可以用伪代码执行的检查的原理.这是到目前为止我得到的代码- >

I understand the principle behind the sanity check and the check i can do in psudocode. Here is the code i have got so far - >

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));

我不想直接将数据插入到语句中，但是直到我了解如何执行此操作时，我还是被困住了，所以问题就变成了

I don't want to insert data directly into the statement but until i understand how to do this i am stuck, so the question,

如何在插入数据之前之前执行健全性检查，这样我就无法在预订时间内获得日期.

How do i perform a sanity check before the data is inserted so that i don't get dates within booked times.

任何帮助将不胜感激.

any help would be greatly appreciated.

推荐答案

经过长时间的深入研究，我现在得到了该方法的一个有效示例，以及一种防止sql注入的方法，下面是代码；

after a long and intensive search, I have now got a working example of this method, along with a method of protecting against sql injection, here's the code;

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语句，从而允许进行验证以过滤不同日期之间的结果.

There isn't much to explain about this code, but in term of differences, (excluding the change in column names with the table) the query is now prepared before the value is set, then it is possible to use it in an if statement, thus allowing the validation to take place to filter results between different dates.

与此同时，我还添加了验证功能，可以通过AND语句停止将其他会议室的日期包含在该语句中，在该语句中，会议室ID被限制为单个值.

along with this i have added validation to stop dates from other meeting rooms being included within the statement via the AND statement where the meeting room id is limeted to a single value.

尽管现在，这将导致另一个问题是此语句引发的另一个抛出的错误，但我知道插入操作是正确的，但此准备好的语句中的某些内容会导致错误:

Although now, which will lead on to a separate question is another thrown error that comes from this statement, i know the insert is sound but something from this prepared statement causes the error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens File: Pdo.php, Line: 234

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens File: Pdo.php, Line: 234

尽管现在我正在研究从准备好的语句引发的错误，如果有修复，它将更新此答案，感谢您的帮助.

Although now i am looking into a error that is thrown from the prepared statement and will update this answer when there is a fix, thanks for the help.

这篇关于MySQL通过日期时间完整性检查将行插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！