CODEWITHSUNDEEP
phpmysqlduplicates
user5443928
user5443928

Reputation:

How to restrict duplicate record using PHP and Mysql

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

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.

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.

Upvotes: 0

Views: 173

Answers (2)

Layton Everson
Layton Everson

Reputation: 1148

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:

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:

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.

Step 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.  
}

Upvotes: 0

Marek Skiba
Marek Skiba

Reputation: 2184

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.

Upvotes: 4

Related Questions