Marios Baraz Marios Baraz - 8 months ago 27
MySQL Question

PHP get the available values that they don't exists on the database

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:

Let's 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 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.

Answer

Not sure how to handle in database query directly, because you have multiple entries. But you can collect all entries in an array with a loop and then check all entries in another loop, like this way, can't you? Maybe not the fastet way, but a solution.

$sql = "SELECT * FROM `appo` WHERE date = '2016-06-17'";
$result_set = mysql_query($sql);

        $working = null;
        $blockedEntries = array();

while($row = mysql_fetch_array($result_set)) {
                            $working = $row['working'];
                            array_push($blockedEntries, array('start'=>$row['start_time'],'end'=>$row['end_time']));
}

        for($echodate = 11; $echodate.':00:00' < $working; $echodate++) {
            $entryFound = false;
            foreach($blockedEntries as $entry) {
                if(!($entry['start'] > $echodate.':00:00' && $entry['end'] > $echodate.':30:00' || $entry['start'] < $echodate.':00:00' && $entry['end'] < $echodate.':30:00')) {
                            $entryFound = true;
                            break;
                }
            }
            if($entryFound == false) {
                echo $echodate.':00<br>';
                echo $echodate.':15<br>';
                echo $echodate.':30<br>';
                echo $echodate.':45<br>';
            }
        }

This code is not tested, but I think, it becomes clear, what I want to achieve. Can working differ in the entries?