php - Disable selected value from DataBase
I'm building an appointment system to a barbershop. It works like this:
- Appointment page in PHP where the client will add their name, phone number, what they want, the date and time (like 2PM).
- This will add to the database these informations
- The admin page will fetch this data from the db and display to the barber the appoiments
My problem: Lets say that the barber has 8AM and 9PM work hours. A client wants 8AM appointment, and the next one that will make the appointment should not see that the 8AM "space" is available, only 9AM and posterior hours.
How can I do that? I thought that I would make an code that reads all data from the db to that day and fetch all taken hours.
Reservation form:
<!DOCTYPE html>
<html>
<head>
<title>
Agendar
</title>
<link href="theme.css" rel="stylesheet">
</script>
</head>
<body>
<?php
// (A) PROCESS RESERVATION
if (isset($_POST['date'])) {
require "reserve.php";
if ($_RSV->save(
$_POST['date'], $_POST['slot'], $_POST['name'], $_POST['email'],
$_POST['tel'], $_POST['notes'])) {
echo "<div class='ok'>Reserva feita! Entraremos em contato caso haja necessidade.</div>";
} else { echo "<div class='err'>".$_RSV->error."</div>"; }
}
?>
<!-- (B) RESERVATION FORM -->
<h1>Agendar um hor??rio</h1>
<form id="resForm" method="post" target="_self">
<label for="res_name">Nome</label>
<input type="text" required name="name" placeholder="Seu nome"/>
<label for="res_tel">Telefone</label>
<input type="text" required name="tel" placeholder="(12) 12345-6789"/>
<label for="res_notes">Barba, cabelo ou os dois?</label>
<select required name="notes">
<option disabled selected value> -- Selecione -- </option>
<option value="Barba">Barba</option>
<option value="Cabelo">Cabelo</option>
<option value="Cabelo e Barba">Cabelo e Barba</option>
</select>
<label>Date</label>
<input type="date" required id="res_date" name="date" placeholder="<?=date("Y-m-d")?>">
<label>Hor??rio</label>
<select required name="slot">
<option disabled selected value> -- Selecione -- </option>
<option value="8:00">8:00</option>
<option value="9:00">9:00</option>
</select>
<input type="submit" value="Reservar"/>
</form>
</body>
</html>
Admin page:
<?php
include_once 'database.php';
$result = mysqli_query($conn,"SELECT * FROM reservations");
?>
<!DOCTYPE html>
<html>
<head>
<link href="styles.css" rel="stylesheet">
<title> Agendamentos</title>
</head>
<body>
<?php
if (mysqli_num_rows($result) > 0) {
?>
<table>
<tr>
<td>ID</td>
<td>Data</td>
<td>Hor??rio</td>
<td>Nome</td>
<td>Telefone</td>
<td>Vai fazer</td>
</tr>
<?php
$i=0;
while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row["res_id"]; ?></td>
<td><?php echo $row["res_date"]; ?></td>
<td><?php echo $row["res_slot"]; ?></td>
<td><?php echo $row["res_name"]; ?></td>
<td><?php echo $row["res_tel"]; ?></td>
<td><?php echo $row["res_notes"]; ?></td>
</tr>
<?php
$i++;
}
?>
</table>
<?php
}
else{
echo "No result found";
}
?>
</body>
</html>
Structure:
CREATE TABLE `reservations` (
`res_id` int(11) NOT NULL AUTO_INCREMENT,
`res_date` date DEFAULT NULL,
`res_slot` varchar(32) DEFAULT NULL,
`res_name` varchar(255) NOT NULL,
`res_tel` varchar(60) NOT NULL,
`res_notes` text DEFAULT NULL,
`res_email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`res_id`),
KEY `res_date` (`res_date`),
KEY `res_slot` (`res_slot`),
KEY `res_name` (`res_name`),
KEY `res_tel` (`res_tel`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin
Answer
Solution:
First your reservations table should not contain users' data like name, tel, email instead you just create clients table and store each client as a single row. Next In your reservations table add column client_id of integer type, where you'll keep numeric id of client in the clients table. This way you do not repeat client's data. You can fetch data from many tables in one query with JOIN statement. That's one-to-one relation (one reservation can have only one client).
Rest is rather easy, when you generating list of available
- Select all rows for given day, which has assigned client
select * from reservations where client_id > 0 - In PHP generate appointments only for these hours, which has NOT row in DB
Tip: as a programmer it will be easier to store hours in DB in 24h format, that way you'll use 8:00 or 16:00 instead of 8+AM vs 8+PM. Of course at the end you can display 8:00 PM on the page.
CREATE TABLE `reservations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`res_date` date DEFAULT NULL,
`slot` time DEFAULT NULL,
`client_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO reservations (res_date, slot, client_id) VALUES ('2021-02-25', '08:00:00', 1);
INSERT INTO reservations (res_date, slot, client_id) VALUES ('2021-02-26', '11:30:00', 2);
INSERT INTO reservations (res_date, slot, client_id) VALUES ('2021-02-27', '16:30:00', 3);
CREATE TABLE `clients` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO clients (first_name, last_name, email, phone) VALUES ('John', 'Doe', 'john@doe.tld', '+1234');
INSERT INTO clients (first_name, last_name, email, phone) VALUES ('Jane', 'Doe', 'jane@doe.tld', '+4321(7)');
INSERT INTO clients (first_name, last_name, email, phone) VALUES ('Johan', 'M??ller', 'mueller@foode', '+49123');
JOIN - querying connected tables at once:
SELECT reservations.*, clients.*
FROM reservations
LEFT JOIN clients ON (reservations.client_id = clients.id)
Answer
Solution:
you have to check the available hours.
- before generating select box on reservation page, you may execute a select query to check if barber is available or not!I recommend you to use 24hour format for this part.
1- a functin to create times between 8:00 to 21:00
function dailyTime(){
$time = [];
$start = 8;
$end = 21;
for ($time = $start; $time <= $end; $time++) {
array_push($time,date("H:00", mktime($time+1)));
}
return $time;
}
2- do the filteration for options:
a) generate time between 8 - 21 b) unset reserved time from array c) make select box for the rest of items left in time array
<select required name="slot">
<option disabled selected value> -- Selecione -- </option>
<?php
$availableTimes = dailyTime();
$result = mysqli_query($conn,"SELECT * FROM reservations");
while($row = mysqli_fetch_array($result)) {
if (($key = array_search($row['res_slot'], $availableTimes)) !== false) {
unset($availableTimes[$key]);
}
}
foreach($availableTimes as $free){
echo '<option>'.$free.'<option>';
}
?>
</select>