Jump to content

Finding Available Time


hoopplaya4

Recommended Posts

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!

Link to comment
https://forums.phpfreaks.com/topic/150974-finding-available-time/
Share on other sites

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";
}

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.

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?  ??? 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.