Help please....[PHP]

[watthehell]

Hi all,

 

I have two fields in MYSQL db

 

user_reserve_time_start

user_reserve_time_end

 

Here what i want is while saving data from PHP i need to check if  the time is reserved or not.

 

a) Suppose user selected time start is 12:00 and time end is 1:00

 

b) and if another user selects the time 12:30 to 1:30

 

He must not be allowed to do this because from (a) the time is already reserved from 12 to 1.

 

Any help on this , i am having headache thinking how to do it ...

 

Will strcmp ( string str1, string str2 ) work on this..

 

ANy help will be most welcome...

 

thanks

 

[Psycho]

What type of field are you using? Is this simply a time field, a datetime or a timestamp? I would suggest using a timestamp or datetime field.

 

Then, when you have a new start time and end time period to enter you could do something like this:

 

Mock code:

$start_time = newStartTimeStamp;
$end_time = newEndTimeStamp;

$query = "
    SELECT *
    FROM table
    WHERE ($start_time<user_reserve_time_end AND $end_time>user_reserve_time_start)";

$result = mysql_query($query);

if (mysql_num_rows($result)!=0) {
    echo "This record overlaps existing time slots. Do NOT insert!";
} else {
    echo "This record does not overlaps existing time slots. OK to insert!";
}

[vijayfreaks]

Hi...

        first one start time: 12:00 then 1200 end time: 1:00 -> 1300

        store data like this.. (or it would be time stamp)

 

     

        then whoever is going for reservation for that check condition like this

        fetch rec. frm qry: ( suppose 1230 is new entered time )

 

   

        select count(*) as reserve from table where 1230 not between start_time and end_time

        if reserve > 0 then its reserved otherwise not..

 

Regards,

Vijay

         

 

 

[watthehell]

Thnks for the reply ...

 

I think you solution is a good one, but i am having this kind of design in front end... (IS THIS CORRECT OR NOT)

 

DB

user_reserve_id  int(9) auto_increment --> First field

user_reserve_time_start varchar(20) --> Second field

user_reserve_time_end varchar(20) --> Third field

==============================================

 

I am making this kind of form

 

FROM::

LISTBOX1 for hour [with value from 12 to 24]    // this is for hour

LISTBOX2 for Minute [with value 00, 10, 20, 30, 40, 50] // this is for minute

 

TO::

LISTBOX1 for hour [with value from 12 to 24]    // this is for hour

LISTBOX2 for Minute [with value 00, 10, 20, 30, 40, 50] // this is for minute

 

=========================================================

 

SO the user will select hour (from 1st listbox) and minute (from second list box)

e.g. 12:00 ----------For FROM

 

Similarly for TO

e.g. 1:00

 

I am saving those values in the second and third field of the DB...

 

Your reply looks nice but is this design ok for saving as you guys replied. (OR IS THIS BAD DESIGN)

 

Thanks in advance..

 

 

[Psycho]

There are specific fields types designed just for date and time fields. Do not use varchar!

 

Are the time spans specific to a specific date? So, could one user reserve the time span of 10:00am to 11:00am on the 6th and another reserver 10:00am - 11:00am on the 7th? If so, then you will want to use the datetime or timestamp field type. If the time is not tied to a date, then you can use the time field type.

 

In any case, the logic I provided will work.

 

If the new start time is before the end time for an existing record AND the new end time is after the start time for the same existing record then it overlaps that time span in that existing record.

WHERE ($start_time<user_reserve_time_end AND $end_time>user_reserve_time_start)

 

But you must use a date or time field. You cannot do that with a varchar.

[xyn]

ti match dates properly in SQL. they should be DATETIME format.