I want to get the available hours for an appointment website. My MySql table is looking like this(with examples): id(AI,PK), date(2016-06-21), start_time(14:00:00), end_time(14:30:00), working(20:00:00), provider(aaa).
I want to echo the available hours between start_time and end_time. I achieve that with:
$sql = "SELECT * FROM `appo` WHERE date = '2016-06-17'";
$result_set = mysql_query($sql);
while($row = mysql_fetch_array($result_set)){
for($echodate = 11;$echodate.':00:00' < $row['working'];$echodate++)
{
if($row['start_time'] > $echodate.':00:00' && $row['end_time'] > $echodate.':30:00' || $row['start_time'] < $echodate.':00:00' && $row['end_time'] < $echodate.':30:00'){
echo $echodate.':00<br>';
echo $echodate.':15<br>';
echo $echodate.':30<br>';
echo $echodate.':45<br>';
}
}
echo '<br>';
}
}
this is working great! but my problem is: if i have two or more registries on the database it echoes back the times on different rows. Like this:
lets say we have two appointments for that day one at 14:00, ending 14:30 and one at 16:00 ending at 16:30 my script will echo this:
11:00 11:15 11:30 11:45 12:00 12:15 12:30 12:45 13:00 13:15 13:30 13:45 15:00 15:15 15:30 15:45 16:00 16:15 16:30 16:45 17:00 17:15 17:30 17:45 18:00 18:15 18:30 18:45 19:00 19:15 19:30 19:45
11:00 11:15 11:30 11:45 12:00 12:15 12:30 12:45 13:00 13:15 13:30 13:45 14:00 14:15 14:30 14:45 15:00 15:15 15:30 15:45 17:00 17:15 17:30 17:45 18:00 18:15 18:30 18:45 19:00 19:15 19:30 19:45
Now, here is the problem. I want to sum them and display only one column. I'm not really great with sql queries. If there is a way(php or sql) to do that i'd be happy to hear it!
Thank you so much for your time.
Aucun commentaire:
Enregistrer un commentaire