检查此日期是否已在DB中

Question

I need to make it so people can Rent a car but when a car is rented it doesnt need to be able to book it again. so I need to check if the car is already rented those days.

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>';
}

}

I think I almost got it but this code will give me this error:

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 * ...') #1 {main} thrown in C:\xampp\htdocs\Rent-a-Car\pages\auto.php on line 260

This error will tell me their is prob on this line: WHERE gebruiker.Klantcode =:code AND auto.kenteken =:groen"; but I have no clue what.

Answer 1

Your error is caused by foreach ($db->query($test) as $invoice) {. The query( function doesn't work with bindings and it would duplicate the execute functionality if it did. You should replace that with a while and fetch or use a fetchall.

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

then change:

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

to:

foreach ($invoices as $invoice) {

Alternatively, without the fetchall you could replace the foreach with:

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