如何使用PHP和Mysql限制重复记录

I need to check the duplicate value from database using PHP and MySQL.I am explaining my table below.

我需要使用PHP和MySQL检查数据库中的重复值。我正在解释我下面的桌子。

time_id        member_id     day_id      time

  1                2            1        12.30am-3.00am

Here I need to check duplicate for time column. Suppose user entered time like 2.00am-4.00am for member_id=2 and day_id=1 it should be checked because 12.00am-3.00am slot has already booked for that member_id and day_id.

在这里我需要检查时间栏的副本。假设用户为member_id=2和day_id=1输入时间如2.00am-4.00am，则应该检查它，因为12.00am-3.00am槽已经为该member_id和day_id预订了。

Here I need query for user can not insert the time in between 12.00am-3.00am again for the same member_id and day_id.

在这里，我需要查询用户不能将时间插入到12:00 am-3.00am之间，以获得相同的member_id和day_id。

2 个解决方案

#1

It will be much more easier (and better for data consistency) if you change time column to 2 columns datetime (ex. date_start and date_end), and then you can try built-in mechanisms in SQL to check that user try add some row between this two dates.

如果您将time列更改为2列datetime(例如date_start和date_end)，那么将会更容易(并且更有利于数据一致性)，然后您可以尝试SQL中的内置机制来检查用户是否尝试在这两个日期之间添加一些行。

#2

Here are the logic steps you need to take to complete this task:

以下是完成这项任务所需的逻辑步骤:

Step 1 Separate the start time and end time into two columns. So your table should look like this:

步骤1将开始时间和结束时间分成两列。所以你的桌子应该是这样的:

time_id  member_id  entry_day    time_in                 time_out
1        2          2016-01-19   2016-08-19 12:30:00     2016-08-18 03:00:00

Your columns types should be:

你的专栏类型应该是:

`time_id`: `int`
`member_id`: `int`
`time_in`: `DATETIME`
`entry_day`: `DATE`
`time_out`: `DATETIME`

Step 2 Write some PHP that executes a query on the database that looks something like this:

步骤2编写一些PHP，在数据库上执行查询如下:

SELECT * FROM TimeEntryTable t
WHERE t.member_id = :memberId 
AND :new_entry_time_in BETWEEN t.time_in AND t.time_out
OR :new_entry_time_out BETWEEN t.time_in AND t.time_out

You will need to pass the new record's time_in, time_out, member_id to the query. I assume you're using PDO... if not then you should probably start.

您需要将新记录的time_in、time_out、member_id传递给查询。我猜你在用PDO…如果没有，你应该开始了。

Step 3

步骤3

Check the result returned by that query.

检查查询返回的结果。

if (count($resultsArray) === 0) {
    //Create the new record, 
} else {
    //If you're here than there was a record within that time range and you should not create it.  
}

#1

#2