Check variable values with values in a database

[elfynity1]

Hey everyone, I have been trying to work this out for 2 days now without success. I am trying to check form input values against existing values in a column called ‘date’ in the ‘bookings’ table in the database. If any matches are found, then the whole result needs to be false so that the query wont run.

My code is such a mess of attempts, but I’m now showing the only idea that half worked.
A user chooses a date or a date range. These dates are checked against what exists in the ‘date’ column in the database. If any of the dates exist, the whole thing needs to be false, if none of the dates are found, it must be true and then those values can be saved to the database.
Here is what I have:

				$sql = "
SELECT date
FROM bookings
;";

$result = $conn->query($sql);
$alreadyBooked = array();

if ($result->num_rows > 0) {
	// output data of each row
	while($row = $result->fetch_assoc()) {
		$alreadyBooked[] = $row['date'];
	} // while
} //  num rows


if(isset($_POST['submit'])){

$name = $_POST['name'];
$email = $_POST['email'];
$tel = $_POST['tel'];
$arrive = strtotime($_POST['arrive']);
$depart = (strtotime($_POST['depart'])) - 86400;
?>

<p>You have chosen the following dates: </p>
 <a name="booking-form"></a> 
 
<?php

	
	
for ( $i = $arrive; $i <= $depart; $i = $i + 86400 ) {
		echo $bookedDates = date('Y-m-d', $i );


		if (in_array($bookedDates, $alreadyBooked)){
			$bookdedDatesNice = date('D, j M Y', strtotime($bookedDates));
			echo 'This date is not available : '.$bookdedDatesNice.'<br />';
		
		} else {
			
			echo 'Your dates are available';
			
			$sqlinsert = "INSERT INTO bookings (name, email, tel, roomid, date) values ";
			// Loop between timestamps, 24 hours at a time
				for ( $i = $arrive; $i <= $depart; $i = $i + 86400 ) {
					$bookedDates =date('Y-m-d', $i ); 
					// Constructing the insert query
				 $sqlinsert .= " ('$name', '$email', '$tel', '$roomId', '$bookedDates'),";
			}

			$bulk_insert_query = rtrim($sqlinsert, ","); // to remove last comma
			$resultinsert = $conn->query($bulk_insert_query); 
			?>
			

			<p>Your booking has been received for: <br />
			Please wait for a confirmation email on: <?php echo $email; ?>
			
			</p>
		
		
		
		<?php
		// header('refresh:3; url=book.php?id='.$id.'#booking-form');
			
		}// if	
}	// for
			
}

It is not working because in_array stops the duplicate values from being inserted into the database, but allows the others that aren’t duplicate to go through. What I need is that nothing is inserted into the database if any duplicates are found. And all are sent if no duplicates are found. I hope that makes sense and that this is possible and that someone can assist.

[mabismad]

For the most immediate problem, the validation logic should be separate from the logic inserting the data. To do this, use an array to hold validation error messages. After the end of the validation logic, if the array holding the error messages is empty, you can insert the submitted data. To display the validation errors, at the appropriate point in the html document, just test if the array is not empty, then either loop over or just implode the array and echo the error message(s).

[elfynity1]

Hi Mabismad, Thank you for your response. I do understand the jist of it, but don’t know php well enough to implement everything you have suggested. I think I would need to look at some code examples.

[droopsnoot]

A better way to do it might be to constrain the table so that the room-id + date combination is unique, then use a transaction to insert all the required dates. That way, you don’t have a problem with two people accessing the code at the same time, where your separate select / inserts could give you issues - in your method, it’s easy for a second user to jump in between your first select and book a date that won’t be in your “booked” array.

I think it’s something like

CREATE UNIQUE INDEX room_date on bookings(roomid, date)

You can then start a transaction, run the insert query for each date, check whether or not it executed, and as soon as one fails because of the unique constraint, roll the transaction back. If none fail, just confirm the transaction. I don’t know the proper syntax for that, but it’s out there.

Also I notice in your code above you magically have a room-id variable that doesn’t appear anywhere else - presumably that should be gathered from the form.