本文介绍了防止日期和时间插入数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试建立预订系统,并且我创建了包含列(ID,办公室名称,房间名称,重新启动,重新发送,重新用户)的表预订.重新启动和重新发送是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-m-d" });
    });
</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:00到2:00保留房间1一样,因此下一个用户不能在例如1:10到2:00注册房间1,但是他可以同时注册房间2.

谢谢.

解决方案

从表预订中获取带有请求房间号和新预订时间的计数,并查看表中是否存在重新启动和重新发送时间之间的记录./p>

如果我正确理解了您的问题,则以下两个查询中的任何一个都可以正常工作.

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;

I am trying to make reservations system, and I have created table reservations which contain columns (id, officename, roomname, resstart, resend, resuser). Restart and resend are DATATIME type. I created a form with date picker and it's inserted successfully to the database.

Here is my PHP file:

<?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-m-d" });
    });
</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>

I want the form to prevent insert to the table, if there is a user tried to make new reservation with already exists time for the same room. Like Room 1 reserved at 1:00 to 2:00, so next user can't register Room 1 at 1:10 to 2:00 for example, but he can register Room 2 at the same time.

Thanks in advance.

解决方案

Take a count from the table reservations with the requesting roomnumber and new reservation time, and see if a record exists in the table between the resstart and resend time.

If I understood your question correctly, any of the below two query should work.

select count(1) from reservations where roomname = 'XYZ' and '17-SEP-17' between resstart and resend;

or

select count(1) from reservations where roomname = 'XYZ' and to_date('17-SEP-17') between resstart and resend;

这篇关于防止日期和时间插入数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-02 19:36