Intereting Posts
JQuery Datatables: JQuery Popup Box не работает под функцией – php mysql
Ошибка: количество связанных переменных не совпадает с числом токенов
Совместимость браузера jQuery (IE)
html с «другим» выбором, который имеет текстовое поле
Как читать данные из заголовка SOAP на стороне клиента
Ошибка сайта: доступ к php-проекту
jquery progressbar – загружает все сразу
Apple Push Notification Service с PHP Script
Ошибка входа в систему Google Php Oauth Не удалось найти системный пакет CA
Сеансы Safari и php не работают в facebook iframe
условие / правило перезаписи htaccess для поддоменов
Codeigniter – редактирование формы (повторное заполнение) с помощью edit_unique
переменная суммы php в цикле while
Какой хороший сценарий сокращения URL?
method_exists в родительском классе php
Запретить ввод даты и времени в базу данных
Я пытаюсь создать систему резервирования, и я создал резервирование таблиц, которые содержат столбцы (id, officename, имя комнаты, resstart, resend, resuser). Перезапуск и повторная DATATIME – DATATIME тип DATATIME . Я создал форму с выбором даты, и она успешно вставлена в базу данных.
Вот мой PHP-файл:
<?php session_start(); include('includes/config.php'); include('includes/checklogin.php'); check_login(); $username = $_SESSION['username']; //code for add courses if($_POST['submit']) { $officename=$_POST['officename']; $roomname=$_POST['roomname']; $startdate=$_POST['startdate']; $enddate=$_POST['enddate']; $query="insert into reservations (officename,roomname,resstart,resend,resuser) values(?,?,?,?,?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param('sssss',$officename,$roomname,$startdate,$enddate,$username); if($stmt->execute()){ echo"<script>alert('Your Reservation Has Been Added Successfully');</script>"; }else{ echo"<script>alert('Warning! You cannot Reserve this appointment');</script>"; } } ?> <!doctype html> <html lang="en" class="no-js"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1, maximum-scale=1"> <meta name="description" content=""> <meta name="author" content=""> <meta name="theme-color" content="#3e454c"> <title>Make New Reservation</title> <link rel="stylesheet" href="css/awesome-bootstrap-checkbox.css"> <link rel="stylesheet" href="css/style.css"> <script type="text/javascript" src="js/jquery-1.11.3-jquery.min.js"></script> <script type="text/javascript" src="js/validation.min.js"></script> <!--Load Script and Stylesheet --> <script type="text/javascript" src="jquery.simple-dtpicker.js"></script> <link type="text/css" href="jquery.simple-dtpicker.css" rel="stylesheet" /> $(document).ready(function() { $( "#date" ).datepicker({ dateFormat: "yy-md" }); }); </script> </head> <body> <?php include('includes/header.php');?> <div class="ts-main-content"> <?php include('includes/sidebar.php');?> <div class="content-wrapper"> <div class="container-fluid"> <div class="row"> <div class="col-md-12"> <h2 class="page-title">Make New Reservation</h2> <div class="row"> <div class="col-md-12"> <div class="panel panel-default"> <div class="panel-heading">Make New Reservation</div> <?php echo "<h4>You Logged in As: <span>$username</span></h4>"; ?> <div class="panel-body"> <?php if(isset($_POST['submit'])) { ?> <p style="color: red"><?php echo htmlentities($_SESSION['msg']); ?><?php echo htmlentities($_SESSION['msg']=""); ?></p> <?php } ?> <form method="post" class="form-horizontal"> <div class="hr-dashed"></div> <div class="form-group"> <label class="col-sm-2 control-label">Select Office </label> <div class="col-sm-8"> <Select name="officename" class="form-control" required> <option value="Select Office">Select Office</option> <?php $sql="select * from offices"; $stmt2 = $mysqli->prepare($sql); //$stmt2->bind_param('i',$roomno); //$stmt->bind_param('i',$aid); $stmt2->execute() ;//ok $res=$stmt2->get_result(); while ($row=$res->fetch_object()) { echo "<option value=". $row->officename .">" . $row->officename . "</option>"; } ?> </Select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">Select Room </label> <div class="col-sm-8"> <Select name="roomname" class="form-control" required> <option value="Select Room">Select Room</option> <?php $sql="select * from rooms"; $stmt2 = $mysqli->prepare($sql); //$stmt2->bind_param('i',$roomno); //$stmt->bind_param('i',$aid); $stmt2->execute() ;//ok $res=$stmt2->get_result(); while ($row=$res->fetch_object()) { echo "<option value=". $row->roomname .">" . $row->roomname . "</option>"; } ?> </Select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">Start time and date</label> <div class="col-sm-8"> <input type="text" autocomplete="off" name="startdate" value="" required> <script type="text/javascript"> $(function(){ $('*[name=startdate]').appendDtpicker(); }); </script> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">End time and date</label> <div class="col-sm-8"> <input type="text" autocomplete="off" name="enddate" value="" required> <script type="text/javascript"> $(function(){ $('*[name=enddate]').appendDtpicker(); }); </script> </div> </div> <div class="col-sm-8 col-sm-offset-2"> <input class="btn btn-primary" type="submit" name="submit" value="Make New Reservation"> </div> </div> </form> </div> </div> </div> </div> </div> </div> </div> </div> </div> </div> </body> </html>в<?php session_start(); include('includes/config.php'); include('includes/checklogin.php'); check_login(); $username = $_SESSION['username']; //code for add courses if($_POST['submit']) { $officename=$_POST['officename']; $roomname=$_POST['roomname']; $startdate=$_POST['startdate']; $enddate=$_POST['enddate']; $query="insert into reservations (officename,roomname,resstart,resend,resuser) values(?,?,?,?,?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param('sssss',$officename,$roomname,$startdate,$enddate,$username); if($stmt->execute()){ echo"<script>alert('Your Reservation Has Been Added Successfully');</script>"; }else{ echo"<script>alert('Warning! You cannot Reserve this appointment');</script>"; } } ?> <!doctype html> <html lang="en" class="no-js"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1, maximum-scale=1"> <meta name="description" content=""> <meta name="author" content=""> <meta name="theme-color" content="#3e454c"> <title>Make New Reservation</title> <link rel="stylesheet" href="css/awesome-bootstrap-checkbox.css"> <link rel="stylesheet" href="css/style.css"> <script type="text/javascript" src="js/jquery-1.11.3-jquery.min.js"></script> <script type="text/javascript" src="js/validation.min.js"></script> <!--Load Script and Stylesheet --> <script type="text/javascript" src="jquery.simple-dtpicker.js"></script> <link type="text/css" href="jquery.simple-dtpicker.css" rel="stylesheet" /> $(document).ready(function() { $( "#date" ).datepicker({ dateFormat: "yy-md" }); }); </script> </head> <body> <?php include('includes/header.php');?> <div class="ts-main-content"> <?php include('includes/sidebar.php');?> <div class="content-wrapper"> <div class="container-fluid"> <div class="row"> <div class="col-md-12"> <h2 class="page-title">Make New Reservation</h2> <div class="row"> <div class="col-md-12"> <div class="panel panel-default"> <div class="panel-heading">Make New Reservation</div> <?php echo "<h4>You Logged in As: <span>$username</span></h4>"; ?> <div class="panel-body"> <?php if(isset($_POST['submit'])) { ?> <p style="color: red"><?php echo htmlentities($_SESSION['msg']); ?><?php echo htmlentities($_SESSION['msg']=""); ?></p> <?php } ?> <form method="post" class="form-horizontal"> <div class="hr-dashed"></div> <div class="form-group"> <label class="col-sm-2 control-label">Select Office </label> <div class="col-sm-8"> <Select name="officename" class="form-control" required> <option value="Select Office">Select Office</option> <?php $sql="select * from offices"; $stmt2 = $mysqli->prepare($sql); //$stmt2->bind_param('i',$roomno); //$stmt->bind_param('i',$aid); $stmt2->execute() ;//ok $res=$stmt2->get_result(); while ($row=$res->fetch_object()) { echo "<option value=". $row->officename .">" . $row->officename . "</option>"; } ?> </Select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">Select Room </label> <div class="col-sm-8"> <Select name="roomname" class="form-control" required> <option value="Select Room">Select Room</option> <?php $sql="select * from rooms"; $stmt2 = $mysqli->prepare($sql); //$stmt2->bind_param('i',$roomno); //$stmt->bind_param('i',$aid); $stmt2->execute() ;//ok $res=$stmt2->get_result(); while ($row=$res->fetch_object()) { echo "<option value=". $row->roomname .">" . $row->roomname . "</option>"; } ?> </Select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">Start time and date</label> <div class="col-sm-8"> <input type="text" autocomplete="off" name="startdate" value="" required> <script type="text/javascript"> $(function(){ $('*[name=startdate]').appendDtpicker(); }); </script> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">End time and date</label> <div class="col-sm-8"> <input type="text" autocomplete="off" name="enddate" value="" required> <script type="text/javascript"> $(function(){ $('*[name=enddate]').appendDtpicker(); }); </script> </div> </div> <div class="col-sm-8 col-sm-offset-2"> <input class="btn btn-primary" type="submit" name="submit" value="Make New Reservation"> </div> </div> </form> </div> </div> </div> </div> </div> </div> </div> </div> </div> </div> </body> </html>в<?php session_start(); include('includes/config.php'); include('includes/checklogin.php'); check_login(); $username = $_SESSION['username']; //code for add courses if($_POST['submit']) { $officename=$_POST['officename']; $roomname=$_POST['roomname']; $startdate=$_POST['startdate']; $enddate=$_POST['enddate']; $query="insert into reservations (officename,roomname,resstart,resend,resuser) values(?,?,?,?,?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param('sssss',$officename,$roomname,$startdate,$enddate,$username); if($stmt->execute()){ echo"<script>alert('Your Reservation Has Been Added Successfully');</script>"; }else{ echo"<script>alert('Warning! You cannot Reserve this appointment');</script>"; } } ?> <!doctype html> <html lang="en" class="no-js"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1, maximum-scale=1"> <meta name="description" content=""> <meta name="author" content=""> <meta name="theme-color" content="#3e454c"> <title>Make New Reservation</title> <link rel="stylesheet" href="css/awesome-bootstrap-checkbox.css"> <link rel="stylesheet" href="css/style.css"> <script type="text/javascript" src="js/jquery-1.11.3-jquery.min.js"></script> <script type="text/javascript" src="js/validation.min.js"></script> <!--Load Script and Stylesheet --> <script type="text/javascript" src="jquery.simple-dtpicker.js"></script> <link type="text/css" href="jquery.simple-dtpicker.css" rel="stylesheet" /> $(document).ready(function() { $( "#date" ).datepicker({ dateFormat: "yy-md" }); }); </script> </head> <body> <?php include('includes/header.php');?> <div class="ts-main-content"> <?php include('includes/sidebar.php');?> <div class="content-wrapper"> <div class="container-fluid"> <div class="row"> <div class="col-md-12"> <h2 class="page-title">Make New Reservation</h2> <div class="row"> <div class="col-md-12"> <div class="panel panel-default"> <div class="panel-heading">Make New Reservation</div> <?php echo "<h4>You Logged in As: <span>$username</span></h4>"; ?> <div class="panel-body"> <?php if(isset($_POST['submit'])) { ?> <p style="color: red"><?php echo htmlentities($_SESSION['msg']); ?><?php echo htmlentities($_SESSION['msg']=""); ?></p> <?php } ?> <form method="post" class="form-horizontal"> <div class="hr-dashed"></div> <div class="form-group"> <label class="col-sm-2 control-label">Select Office </label> <div class="col-sm-8"> <Select name="officename" class="form-control" required> <option value="Select Office">Select Office</option> <?php $sql="select * from offices"; $stmt2 = $mysqli->prepare($sql); //$stmt2->bind_param('i',$roomno); //$stmt->bind_param('i',$aid); $stmt2->execute() ;//ok $res=$stmt2->get_result(); while ($row=$res->fetch_object()) { echo "<option value=". $row->officename .">" . $row->officename . "</option>"; } ?> </Select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">Select Room </label> <div class="col-sm-8"> <Select name="roomname" class="form-control" required> <option value="Select Room">Select Room</option> <?php $sql="select * from rooms"; $stmt2 = $mysqli->prepare($sql); //$stmt2->bind_param('i',$roomno); //$stmt->bind_param('i',$aid); $stmt2->execute() ;//ok $res=$stmt2->get_result(); while ($row=$res->fetch_object()) { echo "<option value=". $row->roomname .">" . $row->roomname . "</option>"; } ?> </Select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">Start time and date</label> <div class="col-sm-8"> <input type="text" autocomplete="off" name="startdate" value="" required> <script type="text/javascript"> $(function(){ $('*[name=startdate]').appendDtpicker(); }); </script> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">End time and date</label> <div class="col-sm-8"> <input type="text" autocomplete="off" name="enddate" value="" required> <script type="text/javascript"> $(function(){ $('*[name=enddate]').appendDtpicker(); }); </script> </div> </div> <div class="col-sm-8 col-sm-offset-2"> <input class="btn btn-primary" type="submit" name="submit" value="Make New Reservation"> </div> </div> </form> </div> </div> </div> </div> </div> </div> </div> </div> </div> </div> </body> </html>
Я хочу, чтобы форма запретила вставку в таблицу, если пользователь попытался сделать новое резервирование с уже существующим временем для той же комнаты. Например, номер 1 зарезервирован с 1:00 до 2:00, поэтому следующий пользователь не может зарегистрировать номер 1 с 1:10 до 2:00, например, но он может одновременно регистрировать номер 2.
Заранее спасибо.
Возьмите счет из резервирования стола с запрашивающим номером комнаты и новым временем резервирования и посмотрите, существует ли запись в таблице между resstart и временем повторной отправки.
Если я правильно понял ваш вопрос, любой из нижеприведенных двух запросов должен работать.
select count(1) from reservations where roomname = 'XYZ' and '17-SEP-17' between resstart and resend;
или
select count(1) from reservations where roomname = 'XYZ' and to_date('17-SEP-17') between resstart and resend;
лучший способ предотвратить дублирование – создать уникальный ключ в вашей базе данных.
ALTER TABLE reservations ADD CONSTRAINT constr_reservations UNIQUE (roomname,resstart,resend,resuser)
в этом случае код не должен изменяться. потому что вы уже улавливаете отказ вставки
if($stmt->execute()){ echo"<script>alert('Your Reservation Has Been Added Successfully');</script>"; }else{ //ROOM DUPLICATE CASE!!! echo"<script>alert('Warning! You cannot Reserve this appointment');</script>"; }