时间戳

首页 > 解决方案 > 在一个 process.php 文件中提交第二个查询之前，如何查询 SQL 数据库？

php - 在一个 process.php 文件中提交第二个查询之前，如何查询 SQL 数据库？

问题描述

我有一个流程预订脚本，它从 HTML 预订表单中获取信息并将其发送到 mySQL 关系数据库。

它工作得很好，但是我修改了代码，以便第一个查询检查他们选择的房间是否在他们指定的日期范围内的任何时间被预订。这可以在第一个查询 $q 中看到

如果它已经在用户提交该房间的任何一天预订，则脚本应回显错误，否则应触发 mysqli_multiquery 并提交数据。

这两个查询都直接在 sql 中工作，但是我无法弄清楚为什么初始 if 语句不起作用 - 'if(mysqli_num_rows($result) == 0)'。

这是PHP文件：

******** 编辑 **********

当我为已在请求的日期范围内预订的房间提交预订时，它会被提交，而不是在 else 语句中回显内容。

以下是错误消息：

显示的错误信息

********** 编辑两个 **********

固定的！问题出在错误的查询上。

<html>



<body>

    <?php

        $page_title = 'Process Booking Form';

        require 'login-db.php';   //  require once means that it well generate a fatal error if the file is not found and means that the file will only be read in when it has not previously been inclued which prevents wastefule duplicate disk accesses.

         $dbc = new mysqli($hn, $un, $pw, $db);



        if($dbc->connect_error) {

           die ($dbc->connect_error);

        }



        if(isset($_POST['firstname']) &&

           isset($_POST['lastname']) &&

           isset($_POST['datefrom']) &&

           isset($_POST['dateto']) &&

           isset($_POST['specialevent']) &&

           isset($_POST['roomnumber']))

        {

            $firstname     = secure_post($dbc, 'firstname');

            $lastname      = secure_post($dbc, 'lastname');   //security

            $datefrom      = secure_post($dbc, 'datefrom');

            $dateto        = secure_post($dbc, 'dateto');

            $specialevent  = clean_string($dbc, 'specialevent');

            $roomno        = secure_post($dbc, 'roomnumber');



                //validation



                $error_message = "";



                if(strlen($firstname) < 2 || strlen($firstname) > 20) {

                    $error_message .= 'Your first name does not appear to be valid.<br>';

                }



                if(strlen($lastname) < 2 || strlen($lastname) > 30) {

                    $error_message .= 'Your last name does not appear to be valid.<br>';

                }



                if(strlen($error_message) > 0) {

                    die($error_message);

                }


                //Query to find out if user selected dates are already booked for their chosen room
                $q = "SELECT customerinfo.firstname, customerinfo.lastname, roominfo.roomnumber, roominfo.roomsize, roominfo.roomprice, bookings.datefrom, bookings.dateto, bookings.daterange

                FROM customerinfo

                LEFT JOIN bookings ON bookings.customer_id = customerinfo.customer_id

                LEFT JOIN transactions ON customerinfo.customer_id = transactions.customer_id

                LEFT JOIN transactionsdetails ON transactionsdetails.transactions_id = transactions.transactions_id

                LEFT JOIN roominfo ON transactionsdetails.room_id = roominfo.room_id

                WHERE roomnumber = $roomno

                AND (bookings.datefrom BETWEEN $datefrom AND $dateto)

                OR (roomnumber = $roomno

                AND (bookings.dateto BETWEEN $datefrom AND $dateto))";

                $result = mysqli_query($dbc, $q);

                // $checkdates = mysqli_query($dbc, $checkdatessql);

                if(mysqli_num_rows($result) == 0) {

                        //echo confirmation message to user
                        echo "Hello $firstname, your booking has been requested for room: $roomno. Your check-in date and time is $datefrom at 11:00am and your scheduled checkout date and time is $dateto at 15:00pm. Thank you for choosing to stay at Westworld.";

                        //submit customer information to the database
                        mysqli_multi_query($dbc,

                         "INSERT INTO customerinfo(firstname, lastname, specialevent) VALUES ('$firstname','$lastname','$specialevent');

                        SET @cusID := LAST_INSERT_ID();

                        INSERT INTO bookings(dateto, datefrom, daterange, customer_id) VALUES ('$dateto','$datefrom',DATEDIFF(dateto,datefrom), @cusID);

                        INSERT INTO transactions (customer_id, datebooked) VALUES (@cusID, CURDATE());

                         SET @roomID := (SELECT room_id FROM roominfo WHERE roomnumber = '$roomno');

                        INSERT INTO transactionsdetails (transactions_id, room_id) VALUES (LAST_INSERT_ID(), @roomID);");
                } else {
                  //echo room error message
                  echo "The room you have selected is unavailable in that date range.";
                }


            $r->close();

            $dbc->close();


        };



        function secure_post($dbc, $string) {

            return htmlentities($dbc->real_escape_string($_POST[$string]));

        } 



        function clean_string($dbc, $string) {

            if (get_magic_quotes_gpc())  $string = stripslashes($string);

            return htmlentities($dbc->real_escape_string($_POST[$string]));

        }


    ?>



</body>

标签： phpmysql

解决方案

好吧，添加一个“print_r(mysqli_num_rows($result));” 看看结果如何。如文档中所述，它可能会返回一个字符串。我不知道您在 $datefrom 和其他变量中使用了什么，您在查询中使用它们而没有任何转义（好主意）但是当它是有效日期（'YYYY-MM-DD'）时，那么你需要逃避它。

无论是什么，您都应该真正切换到 PDO。在那里您可以使用准备好的语句并让 PDO 为您映射变量（您仍然编写相同的 SQL 命令，但您编写“：test”而不是“$test”，然后说“：test 应该替换为 $test 并且它是一个字符串”）。更安全，公司不会当场解雇你；）

下次尽量保持代码简单，比如一个简单的选择查询，然后是 mysqli_num_rows，然后是一个返回，没有别的。不要发布超出您需要的内容，以便人们了解您的问题。

推荐阅读