CODEWITHSUNDEEP
phpmysql
Marios Baraz
Marios Baraz

Reputation: 37

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.

Upvotes: 2

Views: 83

Answers (1)

dns_nx
dns_nx

Reputation: 3943

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?

Upvotes: 1

Related Questions