Finding Available Time

[hoopplaya4]

Hello Everyone,

 

I need some help here on how to determine the logic/syntax/math to SELECT a value from my MySQL database based a on a few parameters.

 

My MySQL DB consists of the following 4 fields: usrID, Start, End, and DayofWeek.  Essentially, these are appointments for specific users at any given time.

 

For example, the DB might have the following values:

 

usrID        Start                      End                    DayofWeek

 

22          08:30:00                08:45:00                Monday

25          11:35:00                12:22:00                Wednesday

22          14:30:00                15:30:00                Saturday

21          21:10:00                23:45:00                Sunday

 

Now, what I'm trying to do, is, let's say I have a form that I can submit, and I want to see what user is available on Monday between 8:15am and 8:55am on Monday. 

 

I would like the data to display back that user 25 and 21 are available.  (22 is not because he's booked from 8:30-8:45 on Monday).

 

Does this make sense?  Any tips on getting off the correct way?  Thanks!

[Ayon]

you could try doing something like this

$start = "08:15:00";
$end = "08:55:00";
$dow = "Monday";
$sql = "SELECT * FROM `table` WHERE `start` > '".$start."' AND `end` < '".$end."' AND `dayofweek`='".$dow."'";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)) {
echo $row['userID']." is available on $dow between $start and $end";
}

[hoopplaya4]

Hi Ayon:

 

Thanks for the reply.  I'll play around with that notion and see if I can get it to work.  I'll post back if I can find anything or have some issues.  Thanks for the idea!

[hoopplaya4]

Okay, so what I've been finding is that the method that was provided, in a sense, does the opposite of what I'm trying to do.  All of my database entries are "busy" times, so thus, the returned entries from the array are people that already have appointments.  I'm trying to find times that they're open. 

 

Would I have to do a SQL query within the query to return the users that have openings?  I'm a bit lost, and trying to think how I could achieve this.  Thanks.

[hoopplaya4]

What I've tried is this:

 

<?php
$start= "08:00:00";
$end= "10:00:00";
$dow = "Monday";
$sql = "SELECT * FROM table WHERE start>= '$start' AND end<= '$end' AND dayofweek= '$dow'";

 

However, this does not work, because someone who has the following schedule:

 

22          07:30:00                 11:00:00                Monday 

 

Would not be SELECT'd from the db.  Thus, the results would be somewhat skewed. 

 

Any ideas?  ???