hoopplaya4 Posted March 24, 2009 Share Posted March 24, 2009 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! Quote Link to comment Share on other sites More sharing options...
Ayon Posted March 24, 2009 Share Posted March 24, 2009 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"; } Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted March 25, 2009 Author Share Posted March 25, 2009 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! Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted March 25, 2009 Author Share Posted March 25, 2009 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. Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted March 25, 2009 Author Share Posted March 25, 2009 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? ??? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.