Storing a range of dates in one row in database or in seperate table

Question

I am trying to make a car booking system using a jquery datepicker. I have the date picker, I have dates in a database(manually inserted in WAMP SERVER phpMyAdmin) that highlight those dates as pre-booked dates (Cursor disabled in css on mouse over) so you can't book those ones.

I'm at the point of assembling all my parts but have become stuck on the last bit of how to store the dates. It seems from what I've read, that storing a bunch of dates in one row is a no no (plus I haven't managed to do it).

1. So, should I store a start and end date for each client on the one DB table row and generate the in between dates when the page/datepicker loads?
2. If this approach how do I get and then join each row (many clients) of dates together?
3. Or should I generate the in between dates and then store all the dates in a separate table with the client id when the datepicker selects start & end?

Could someone help with the correct approach and php code to get me there. I already have the mysqli connection etc.

My DB columns at present are start_date & end_date in tabledates_from_to. My datepicker uses this format 2021-04-15 and the array needed looks like this '2021-04-15','2021-04-16','2021-04-17' to highlight the dates.

Answer 1

Seems like you have a 1:n relation between clients/customers and bookings. So yes you should store them in separate tables according to database normalization rules.

Assume you have a table clients (primary key client_id) and bookings, which replaces your dates_from_to table (with foreign key client_id) you can do the following JOIN:

SELECT * 
  FROM clients
  JOIN bookings 
 USING (client_id) 
 WHERE booking_date = '2021-04-05'

For the next part I take the example array. Here is example code to insert one row for every day:

$dates = ['2021-04-15','2021-04-16','2021-04-17'];

// $pdo contains a connected DB instance
$stmt = $pdo->prepare('INSERT INTO bookings (client_id, booking_date) VALUES (?, ?)');

foreach ($dates as $day) {
    $stmt->execute([1, $day]);
}

As an alternative you could use first and last day of the period and store everything in one row by replacing booking_date column by start_date and end_date.

Here is the version with mysqli and start/end date (from docs):

$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->prepare('INSERT INTO bookings (client_id, start_date, end_date) VALUES (?, ?, ?)');

// iss means (i)nteger, (s)tring and (s)tring parameter
$stmt->bind_param('iss', 1, '2021-04-15', '2021-04-17'); 
$stmt->execute();

Hope this keeps you going.

Answer 2

After about a month I have the date-picker working, storing all dates in DB on their own row.

It picks a start date & end date from two datepickers.

Creates/produces all the dates in between then inserts all those separate dates into the database each date in it's own row in PHP.

Then redirects to same page & highlights all DB dates & disables them so no one can select already booked dates.

I have copied different bits of code from this site and manipulated it a little & at present I'm very happy with the hybrid outcome.

This is running on a WAMP server.

Below is my code to help other amateurs like me.

<?php

$servername = "localhost:3308";
$username = "root";
$password = "";
$dbname = "datepicker_test_outputs";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}


$sql = "SELECT date FROM insert_datesarray";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
$for_JS_date = null;

// output data of each row
while($row = $result->fetch_assoc()) {
    
    $php_array_dates = date('Y-m-d',strtotime($row["date"]));
    
        $for_JS_date .= "'".$php_array_dates."'".",";
        
            $correct_date_format = substr($for_JS_date, 0, -1);
            }


if($_SERVER["REQUEST_METHOD"]=="POST"){

$start_date = $_POST["from"];
$end_date = $_POST["to"];

$dateentry = array();

// populate $dateentry array with dates
while (strtotime($start_date) <= strtotime($end_date)) {
    $dateentry[] =  date("Y-m-d", strtotime($start_date));
    $start_date = date ("Y-m-d", strtotime("+1 day", strtotime($start_date)));
} // end while

// loop through $dateentry and insert each date into database
foreach($dateentry as $entry) {

    $my_inserted_dates =("INSERT INTO insert_datesarray 
        (date)  VALUES('{$entry}')")
        or die(mysqli_error());
        $result = mysqli_query($conn,$my_inserted_dates);

if($result == false)
{
    echo "<script>alert('BOOKING IS NOT SUCCESS - PLEASE CONTACT ADMINISTRATOR');      </script>";
}
else
{
   /* Header location to refresh the page & load booked dates */
   header('Location: '.$_SERVER['PHP_SELF']);

}

} die;
// end foreach
}
}

?> 
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>jQuery UI DatePicker</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.9.1.js"></script>
<script src="//code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
<style>
    .ui-highlight .ui-state-default{
        background: red !important;
        border-color: red !important;
        color: white !important;
            cursor:not-allowed !important;

    }
</style>
<script type="text/javascript" language="javascript">
    var dates = [<?php echo $correct_date_format;?>];       
    /*var dates = ['2021-04-05','2021-04-15','2021-04-25','2021-04-30'];*/
    jQuery(function(){
        jQuery('input[id=from]').datepicker({
            dateFormat: 'dd-mm-yy',/*CN  Changes date format. Remove if required  CN*/
            changeMonth : true,
            changeYear : true,
            minDate: 0 ,/*Mindate disables dates before todays date*/
            beforeShowDay : function(date){
                var y = date.getFullYear().toString(); // get full year
                var m = (date.getMonth() + 1).toString(); // get month.
                var d = date.getDate().toString(); // get Day
                if(m.length == 1){ m = '0' + m; } // append zero(0) if single digit
                if(d.length == 1){ d = '0' + d; } // append zero(0) if single digit
                var currDate = y+'-'+m+'-'+d;
                if(dates.indexOf(currDate) >= 0){
                    return [true, "ui-highlight", 'Date Already Booked'];   
                }else{
                    return [true];
                }                   
            }
        });
    })
</script>
<script type="text/javascript" language="javascript">       
    var dates = [<?php echo $correct_date_format;?>];       
    /*var dates = ['2021-04-05','2021-04-15','2021-04-25','2021-04-30'];*/
    jQuery(function(){
        jQuery('input[id=to]').datepicker({
            dateFormat: 'dd-mm-yy',/*CN  Changes date format. Remove if required  CN*/
            changeMonth : true,
            changeYear : true,
            minDate: 0 ,/*Mindate disables dates before todays date*/
            beforeShowDay : function(date){
                var y = date.getFullYear().toString(); // get full year
                var m = (date.getMonth() + 1).toString(); // get month.
                var d = date.getDate().toString(); // get Day
                if(m.length == 1){ m = '0' + m; } // append zero(0) if single digit
                if(d.length == 1){ d = '0' + d; } // append zero(0) if single digit
                var currDate = y+'-'+m+'-'+d;
                if(dates.indexOf(currDate) >= 0){
                    return [true, "ui-highlight", 'Date Already Booked'];   
                }else{
                    return [true];
                }                   
            }
        });
    })
</script>
</head>
<body>
<p id="dateFrom"></p>
<p id="dateTo"></p>
<form action="" name="form1" method="post">
<label for="from">From</label>
<input type="text" id="from" name="from"onchange="getFromDate()">
<label for="to">to</label>
<input type="text" id="to" name="to"onchange="getToDate()">
<input name="book" type="submit" value="Book">
</form> 
</body>
</html>