Hello for the first time!
In my effort to become a better programmer I decided to build an online booking web application. As you can see in the picture below, I have created this database with the following things in mind.
each customer may book an appointment or request an appointment.
i) Booked appointments have a fixed time and day.
ii) Appointment requests do not have a fixed time and day until they are booked manually through the back office. They must have at least two time-ranges between two days, or more which declare when the customer is available, so the appointment must be booked inside one of these time ranges.
each appointment may have more than one customers ( probably max to 3 )
- each customer may have more than one services ( haven't decided the limit here )
The desired outcome for the front-end, I have decided upon is the following (which will have pagination and ajax as well but I am not there yet):
The first successful attempt on this, was not efficient at all as I needed to run 4 queries to the database for each appointment, so I immediately started thinking of something better but I wasted almost two weeks and I have nothing yet.
I can't understand in which part I am missing knowledge MySQL or PHP(forgot to mention that I am writing this with PHP but better late than never)....very likely on both sides but I don't know what to study to solve this one, while so many other stuff I managed to find something around.
Thanks in advance!
UPDATE: Follows the code I use (including the queries):
MODEL
public function getAppointments()
{
$db = \Config\Database::connect();
// get future appointments
$query = $db->query('
SELECT DISTINCT`appointment_id`
FROM `appointments_schedule`
WHERE `datetime_from` > CURRENT_TIMESTAMP
');
$appointments = $query->getResultArray();
return $appointments;
}
public function getAppointmentDateTimes($appointment_id)
{
$db = \Config\Database::connect();
$query = $db->query('
SELECT `datetime_from`, `datetime_to`
FROM `appointments_schedule`
WHERE `appointment_id` = ' . $appointment_id
);
$appointmentDateTimes = $query->getResultArray();
return $appointmentDateTimes;
}
public function getAppointmentCustomers($appointment_id)
{
$db = \Config\Database::connect();
$query = $db->query('
SELECT `id`, `last_name`, `first_name`, customers.status, `is_booking`
FROM customers
INNER JOIN appointment_customer_services ON appointment_customer_services.customer_id = customers.id
WHERE id IN(
SELECT `customer_id`
FROM appointment_customer_services
WHERE appointment_id = ' . $appointment_id . ')
GROUP BY customers.last_name'
);
$appointmentCustomers = $query->getResultArray();
return $appointmentCustomers;
}
public function getCustomerServices($appointment_id, $customer_id)
{
$db = \Config\Database::connect();
$query = $db->query('
SELECT `name` AS `service_name`
FROM services
WHERE id IN(SELECT `service_id`
FROM appointment_customer_services
WHERE `customer_id` =' . $customer_id . '
AND `appointment_id` =' . $appointment_id . ')
');
$CustomerServices = $query->getResultArray();
return $CustomerServices;
}
CONTROLLER
$data['appointments'] = [];
foreach ( $model->getAppointments() as $appointment )
{
foreach ( $appointment as $id )
{
foreach ( $model->getAppointmentDateTimes($id) as $key => $timeDates )
{
$data['appointments'][$id]['timeDates'][] = $timeDates;
}
foreach ( $model->getAppointmentCustomers($id) as $appointmentCustomers )
{
foreach ( $model->getCustomerServices($id, $appointmentCustomers['id']) as $services )
{
foreach ( $services as $service )
{
$appointmentCustomers['services'][] = $service;
}
}
$data['appointments'][$id]['customers'][] = $appointmentCustomers;
}
}
}
PRINT_R OUTPUT
Array
(
[1] => Array
(
[timeDates] => Array
(
[0] => Array
(
[datetime_from] => 2020-04-28 14:00:00
[datetime_to] => 2020-04-28 17:00:00
)
[1] => Array
(
[datetime_from] => 2020-05-06 12:00:00
[datetime_to] => 2020-05-06 17:00:00
)
[2] => Array
(
[datetime_from] => 2020-05-30 17:00:00
[datetime_to] => 2020-05-30 20:00:00
)
)
[customers] => Array
(
[0] => Array
(
[id] => 1
[last_name] => Jolie
[first_name] => Angelina
[status] => 1
[is_booking] => 1
[services] => Array
(
[0] => service1
)
)
)
)
[2] => Array
(
[timeDates] => Array
(
[0] => Array
(
[datetime_from] => 2020-05-29 14:00:00
[datetime_to] => 2020-05-29 16:00:00
)
)
[customers] => Array
(
[0] => Array
(
[id] => 2
[last_name] => Lopez
[first_name] => Jennifer
[status] => 1
[is_booking] => 1
[services] => Array
(
[0] => service1
[1] => service2
[2] => service3
)
)
)
)
[3] => Array
(
[timeDates] => Array
(
[0] => Array
(
[datetime_from] => 2020-05-28 15:00:00
[datetime_to] => 2020-05-27 17:00:00
)
)
[customers] => Array
(
[0] => Array
(
[id] => 3
[last_name] => Charlize
[first_name] => Theron
[status] => 1
[is_booking] => 1
[services] => Array
(
[0] => service1
[1] => service2
[2] => service3
)
)
[1] => Array
(
[id] => 4
[last_name] => Bullock
[first_name] => Sandra
[status] => 1
[is_booking] => 0
[services] => Array
(
[0] => Service1
[1] => Service2
)
)
[2] => Array
(
[id] => 5
[last_name] => Aniston
[first_name] => Joe
[status] => 1
[is_booking] => 0
[services] => Array
(
[0] => service1
)
)
)
)
)
VIEW(the table i fill with the data above)
<table class="table table-hover">
<thead class="text-warning">
<?php foreach ( $table_head as $th) : ?>
<th <?php if ($th == "timeDates"){echo "style="."text-align:center;";} ?>>
<?php echo $th; ?>
</th>
<?php endforeach; ?>
</thead>
<tbody>
<?php
/* the table has 3 cells and does not contain nested tables
each row represents one customer, with his services in a unordered list
the times cell has a rowspan = the number of the appointment customers
*/
?>
<?php foreach ( $appointments as $appointment ) : ?>
<tbody class="appointments-table">
<?php foreach ( $appointment['customers'] as $index => $customer) : ?>
<tr class="appointments-table">
<td <?php echo $customer['is_booking'] ? "style='font-weight: bold;'" : ''; ?>><?php echo $customer['last_name'] . ' ' . $customer['first_name'];?></td>
<td>
<?php foreach ( $customer['services'] as $service ) : ?>
<ul style="list-style-type:none; padding-left: 0; margin-bottom: 0;">
<li><?php echo $service; ?></li>
</ul>
<?php endforeach; ?>
</td>
<?php if ( $index == 0 ) : ?>
<td class="appointments-table" rowspan=<?php echo count($appointment['customers']); ?>>
<ul class="pl-0 mb-0" style="list-style-type:none; text-align: center; margin-bottom: 0;">
<?php foreach ( $appointment['timeDates'] as $timeDate ) : ?>
<li><?php echo $timeDate['datetime_to'] ?
( date_format(date_create($timeDate['datetime_from']), "d/m/Y H:i") . ' until ' .
date_format(date_create($timeDate['datetime_to']), "d/m/Y H:i") ) :
date_format(date_create($timeDate['datetime_from']), "d/m/Y H:i"); ?></li>
<?php endforeach; ?>
</ul>
</td>
<?php endif; ?>
</tr>
<?php endforeach; ?>
</tbody>
<?php endforeach; ?>
</table>
NOTE: i intend to improve the view also to move as much logic as possible to the controller
