php - 检查该日期是否已存在于数据库中

Question

我需要做到这一点，以便人们可以租车，但租车后不需要再次预订。所以我需要检查一下这几天车是否已经租了。

if (isset($_POST['Huur'])) {
global $db;

$kenteken = htmlspecialchars($_POST["Kenteken"]);
$klantcode = $_SESSION['USERID'];
$factuurdatum = date("Y-m-d");


    $test = "SELECT * 
        FROM factuurregel 
        LEFT JOIN auto 
        ON factuurregel.Kenteken = auto.Kenteken

        LEFT JOIN factuur 
        ON factuurregel.Factuurnummer = factuur.Factuurnummer

        LEFT JOIN gebruiker 
        ON factuur.Klantcode = gebruiker.Klantcode

        WHERE gebruiker.Klantcode =:code AND auto.kenteken =:groen";

        $stmt10 = $db->prepare($test);
        $data10 = array("code" => $klantcode, "groen" => $kenteken);

        try {
            $stmt10->execute($data10);

        } 
        catch (PDOException $e) {
          echo $e->getMessage();
        }

$check = true;
foreach ($db->query($test) as $invoice) {
    if ($invoice['Begindatum'] <= $_POST['Begindatum'] && $invoice['Einddatum'] >= $_POST['Einddatum']) {
        $check = false;
    }
}
if ($check) {
    $sqlstatement = "INSERT INTO factuur (Factuurdatum, Kenteken, Klantcode)
        VALUES (:Factuurdatum, :Kenteken, :Klantcode)";
        $stmt = $db->prepare($sqlstatement);
        $data = array("Factuurdatum" => $factuurdatum, "Kenteken" => $kenteken, "Klantcode" => $klantcode);

        try {
            $stmt->execute($data);
            $factuurnummer = $db->lastInsertId(); 

        } 
        catch (PDOException $e) {
          echo $e->getMessage();
        }

        $begindatum = htmlspecialchars($_POST["Begindatum"]);
        $einddatum = htmlspecialchars($_POST["Einddatum"]);

        $sqlstatement1 = "INSERT INTO factuurregel (Factuurnummer, Kenteken, Begindatum, Einddatum)
        VALUES (:Factuurnummer, :Kenteken, :Begindatum, :Einddatum)";
        $stmt1 = $db->prepare($sqlstatement1);
        $data1 = array("Factuurnummer" => $factuurnummer, "Kenteken" => $kenteken, "Begindatum" => $begindatum, "Einddatum" => $einddatum);

        try {
            $stmt1->execute($data1);

        } 
        catch (PDOException $e) {
          echo $e->getMessage();
        }

    $password_err = "Uw auto is succesvol gereserveerd";
    echo '<script type="text/javascript">alert("'.$password_err.'");</script>';
}

}

我想我几乎明白了，但是这段代码会给我这个错误:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':code AND auto.kenteken =:groen' at line 12 in C:\xampp\htdocs\Rent-a-Car\pages\auto.php:260 Stack trace: #0 C:\xampp\htdocs\Rent-a-Car\pages\auto.php(260): PDO->query('SELECT * \r\n ...') #1 {main} thrown in C:\xampp\htdocs\Rent-a-Car\pages\auto.php on line 260

这个错误会告诉我他们在这一行上的问题:WHERE gebruiker.Klantcode =:code AND auto.kenteken =:groen";但我不知道是什么。

Answer 1

您的错误是由 foreach ($db->query($test) as $invoice) { 引起的。 query( 函数不适用于绑定(bind)，如果适用，它会重复 execute 功能。您应该将其替换为 while 并fetch 或使用 fetchall。

try {
   $stmt10->execute($data10);
   $invoices = $stmt10->fetchAll();
} 

然后更改:

foreach ($db->query($test) as $invoice) {

至:

foreach ($invoices as $invoice) {

或者，如果没有 fetchall，您可以将 foreach 替换为:

while($invoice = $stmt10->fetch(PDO::FETCH_ASSOC)){