0

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.

Improve this question
3
  • 1
    Why you have not used two columns like start time and end time which include date too? It would have been easy with queries to check duplicate and crossing slots. Commented Aug 19, 2016 at 7:28
  • True, but either way, this isn't so hard Commented Aug 19, 2016 at 7:30
  • You can add unique constraint on these 3 column Commented Aug 19, 2016 at 7:38

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.  
}
Improve this answer

5 Comments

But i have two field whose datatype is string.Are they work in my case ?
You will need to convert them, but they will work. Php will convert them for you. $inTime = new \DateTime($inTimeString); If you're using PDO you can submit that object as the parameter.
@ Layton Everson : I am using jquery timepicker for selecting the time in front end, Will it be good to save datetime type column.
Yes. If you think about it, no matter what you're using on the front end, you are ultimately submitting a string, It needs to be converted on the backend. I could write a book about why you shouldn't store the data in the database a string.
@subhra, this will be a little extra work now and a bit of a learning curve for you but it will be worth it to follow through. You will learn a lot of essentials by completing this task properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.