Help needed with php/mysql script for pulling dates outof a table

[PHP Learner]

Hello coder buddies,

I have made this table with all the dates of the years going upto 2063, to keep it simple, it contains 3 columns which have been pre-popluated, example as follows...


Actual table

 

AutoIncNo | BookingDate | Status

 

The calendar is in 2 parts.

1) Calendar to select a date

2) A list menu to select the amount of nights they wish to stay.

So I take the original date (the one they select) and rearrange the format to suit the table...

 

PHP Code:

$CalendarDate = str_replace("/", "-", "$CalendarDate");

    $QueryDate = date("Y-m-d", strtotime($CalendarDate)); 

Connect to the database...
 

PHP Code:

include_once('../connect/connectdatabase.php'); 

Run the first query to check if the dates they require are available.

$QueryDate is the date they select

$NightsForQuery is the amount of nights they want to stay

 

PHP Code:

$CalendarQuery = mysql_query("SELECT * FROM BookingsCalendar WHERE BookingDate='$QueryDate' LIMIT 1");

    while($row = mysql_fetch_array($CalendarQuery)) {$AutoInc = $row["AutoIncNo"];}


    $AutoInc2 = $AutoInc + $NightsForQuery - 2;


    $SelectDates = mysql_query("SELECT * FROM BookingsCalendar WHERE AutoIncNo BETWEEN $AutoInc AND $AutoInc2");

    while($row = mysql_fetch_array($SelectDates)) {

        $AutoIncNo = $row["AutoIncNo"];
        $BookingDate = $row["BookingDate"];
        $Status = $row["Status"];

        if ($Status == 'booked') {

            $LastBookedDate = $BookingDate; 
            $LastAutoIncNo = $AutoIncNo; 
            $Handle = 1;

        }

    } // End - while($row = mysql_fetch_array($SelectDates)) {



if ($Handle !== 1) {echo 'DATES AVAILABLE >> WRITE BOOKING CODE';} 

So if the handle is not equal to 1 its fine and they can book, but, if the dates arn't available (i.e, $Handle == 1) I need to check the closest available dates either side (before and after) the date they wanted where the Status is 'available' for the amount of nights they wish to stay...

So I set out to establish the first available date in either direction and thts where I got stuck. Looking at it I'm sure you could run a while loop to find the next available block inside the code above, but not sure how.
 

PHP Code:

$FirstDateQuery = mysql_query("SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo < $LastAutoIncNo ORDER BY AutoIncNo DESC LIMIT $NightsForQuery");
        
    while($row = mysql_fetch_array($FirstDateQuery)) {
        
            $AutoIncNo = $row["AutoIncNo"];
            $BookingDate = $row["BookingDate"];
            $Status = $row["Status"];
            
            echo $BookingDate . '   ';

           } 

Which works, but, it selects the previous 4 rows individually.

So for example, if someone tries to book from 2013.06.01 but cant because its 'booked' for the next 4 days, the above script runs and brings up 2013.05.31 - 2013.05.30 - 2013.05.29 - 2013.05.28 as a result.

But if one of those dates are booked it will skip it and give me the next one (selecting as it is the next 4 that meet the condition 'available')

So if say 2013.05.29 was booked it would show 2013.05.31 - 2013.05.30 - 2013.05.28 - 2013.05.27 missing out the day which is booked.

Now the thing is that we need the next 4 rows together (undivided/continuous/without breaks in the dates) which are 'available'.

If you have a better more efficient way or can adapt what is already here, that would be grand... My brain hurts lol.

Thank you, L-Plate

 

[Barand]

try something like this

$db = new mysqli(HOST, USERNAME, PASSWORD, 'test');

$prefDate = '2013-06-24';    // preferred date
$reqDays = 3;                // days required

$sql = "SELECT bookingdate
        FROM bookingscalendar
        WHERE status = 'available'
            AND bookingdate >= CURDATE()
        ORDER BY bookingdate";
$res = $db->query($sql);

$dates = $earlier = $later = array();
$count = 0;

$preferred = new DateTime($prefDate);
$prevDay = new DateTime();
$prevDay->modify('-1 days');

/********************************
*  search for N cosecutive dates
*********************************/
while (list($bd) = $res->fetch_row()) {
    $dt = new DateTime($bd);
    if ($dt->diff($prevDay)->days==1 ) {
        $dates[] = $dt->format('Y-m-d');
    }
    else {
        $dates = array($dt->format('Y-m-d'));
    }    
    if (count($dates)>=$reqDays) {
        if ($dt->diff($preferred,0)->format('%R%a') > 1-$reqDays) {
            $earlier = array_slice($dates, -$reqDays); // last available N days prior
        } else {
            $later = array_slice($dates, -$reqDays);   // next available N days
            break;
        }
    }
    $prevDay = clone $dt;
}
// output search results
if ($later[0] != $prefDate) {
    echo "Prior dates: " . join(' | ', $earlier) . '<br>';
    echo "Later dates: " . join(' | ', $later);
} else {
    echo join(' | ', $later) . " is available";
}

[PHP Learner]

Well your certainly the person I need to speak to!

 

Thanks for your input, the script returns a parse error on the same line as the connection...

$db = new mysqli(HOST, USERNAME, PASSWORD, 'test');

I'm not familiar with the mysqli() command yet, so its quite possible that I've F'd it up...

Parse error: syntax error, unexpected T_DNUMBER

(on the same line)

 

 

Oh joy,

 

This is probably very simple, if your not brain fried.

Edited May 15, 2013 by PHP Learner

[PHP Learner]

Would this work in it's place?

$prefDate = $_SESSION['CalendarDate'];    // preferred date
$reqDays = $NightsForQuery;                // days required

$res = mysql_query("SELECT BookingDate FROM BookingsCalendar WHERE Status='available' AND BookingDate >= CURDATE() ORDER BY BookingDate");

//$db = new mysqli(ip, usrname, psswrd, 'databasename');

//$res = $db->query($sql);

Edited May 15, 2013 by PHP Learner

[Barand]

Yes, you can use

 

mysql_connect()

mysql_select_db()

mysql_query()

mysql_fetch_row()

 

instead of the mysqli functions that I have used. BUT you are going to need to change sometime as the mysql library is deprecated as of PHP5.5

[PHP Learner]

Alright Barand,

 

I would love to try out your script but I wonder if you can help me with populating the table first? See, the following scripts work with the older versions of phpmyadmin but yesterday I got a newer version and it's stopped working with a very generic error(500). Reckon you could cast your professional eye accross this code quickly and see if it can be made more efficient or see what needs doing to it?

CREATE TABLE `table_setup` (
    `number` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`number`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

INSERT INTO table_setup VALUES(NULL);
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
DELETE FROM table_setup WHERE NUMBER > 18264;

CREATE TABLE `BookingsCalendar` (
    `AutoIncNo` SMALLINT(2) NOT NULL AUTO_INCREMENT,
    `BookingDate` DATE NOT NULL,
    `Status` varchar(10) NOT NULL DEFAULT 'available',
    `InvoiceRefference` varchar(40) NOT NULL,
    `CustomerName` varchar(40) NOT NULL,
     `CustomerEmail` varchar(40) NOT NULL,
      `CustomerPhone` varchar(30) NOT NULL,
      `Address` varchar(100) NOT NULL,
      `County` varchar(40) NOT NULL,
      `Country` varchar(30) NOT NULL,
      `PostCode` varchar(10) NOT NULL,
    PRIMARY KEY (`AutoIncNo`)

)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

INSERT INTO BookingsCalendar (BookingDate)
    SELECT DATE_ADD('2013-05-05', INTERVAL number-1 DAY)
    FROM table_setup
    WHERE NUMBER < 18264;

Gotta say, appreciate this very much.

 

L-Plate

Edited May 16, 2013 by PHP Learner

[PHP Learner]

Hmmm, looks like it might be a server error and not the script.

[Barand]

Before you go too far, consider a couple of changes

 

A ) Normalize your data so you don't have the burden of all that data in the bookings calendar

+------------------+           +--------------+        +-------------+
| bookingsCalendar |           |  invoice     |        | customer    |
+------------------+           +--------------+        +-------------+
| autoIncId        |    +----- |  invoiceId   |   +--- | custId      |
| bookingDate      |    |      |  invoiceRef  |   |    | custName    |
| status           |    |      |  invoiceDate |   |    | email       |
| invoiceId        | >--+      |  custId      | >-+    | address     |
+------------------+           |    etc       |        |  etc        |
                               +--------------+        +-------------+

B ) Only store bookings in the calendar.

 

My script looks for N or more contiguous "available" status dates. If you only store bookings it can be adapted to search for gaps of N or more days. That way you don't need to store recs for all dates way into the future.

 

I'll dig out my test data and post it for you.

[Barand]

test data attached

test_bookingscalendar.txt

[PHP Learner]

My word! lol,

 

Guru is certainly apt, many thanks for your efforts Barand.

 

So if a fresh new approach is what the doctor ordered thats what we will do.

 

In this case, by the look of it, I'll make 3 tables to spread out or 'normalize' the data, and your right making entries only when is necessary will save an awful lot of clutter.

CREATE TABLE `bookingsCalendar` (
                  `autoIncId` int(10) NOT NULL AUTO_INCREMENT,
                  `bookingDate` date NOT NULL,
                  `status` enum('available','booked') NOT NULL,
                  `invoiceId` varchar(30) NOT NULL,
                  PRIMARY KEY (`autoIncId`)
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `invoice` (
                  `invoiceId` varchar(30) NOT NULL,
                  `invoiceRef` varchar(30) NOT NULL,
                  `invoiceDate` date NOT NULL,
                  `custId` varchar(30) NOT NULL
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `customer` (
                  `custId` varchar(30) NOT NULL,
                  `firstName` varchar(20) NOT NULL,
                  `lastName` varchar(20) NOT NULL,
                  `email` varchar(45) NOT NULL,
                  `phoneNo` tinyint(4) NOT NULL,
                  `address` varchar(255) NOT NULL,
                  `county` varchar(50) NOT NULL,
                  `country` varchar(30) NOT NULL,
                  `postcode` varchar(10) NOT NULL
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Would you say these values are appropriate?

 

So the next step is to take a date from the calendar widget and amount of nights they want to stay (for testing sake best to keep it to 7 nights I think).

I'll have to go away and write some sort of script for this now as best I can and I'll come back to post it in a bit, it will probably be clunky but I am a learner after all.

 

[PHP Learner]

<?php session_start();


		if (!isset($_SESSION['CalendarDate'])) {$_SESSION['CalendarDate'] = date("d/m/Y");}


		$NumberOfNights = $_SESSION['NumberOfNights'];


		if ($NumberOfNights == '7 Nights') {$Selected_1 = 'selected="selected"';}

		if ($NumberOfNights == '8 Nights') {$Selected_2 = 'selected="selected"';}

		if ($NumberOfNights == '9 Nights') {$Selected_3 = 'selected="selected"';}

		if ($NumberOfNights == '10 Nights') {$Selected_4 = 'selected="selected"';}

		if ($NumberOfNights == '11 Nights') {$Selected_5 = 'selected="selected"';}

		if ($NumberOfNights == '12 Nights') {$Selected_6 = 'selected="selected"';}

		if ($NumberOfNights == '13 Nights') {$Selected_7 = 'selected="selected"';}

		if ($NumberOfNights == '14 Nights') {$Selected_8 = 'selected="selected"';}

		if ($NumberOfNights == '15 Nights') {$Selected_9 = 'selected="selected"';}

		if ($NumberOfNights == '16 Nights') {$Selected_10 = 'selected="selected"';}

		if ($NumberOfNights == '17 Nights') {$Selected_11 = 'selected="selected"';}

		if ($NumberOfNights == '18 Nights') {$Selected_12 = 'selected="selected"';}

		if ($NumberOfNights == '19 Nights') {$Selected_13 = 'selected="selected"';}

		if ($NumberOfNights == '20 Nights') {$Selected_14 = 'selected="selected"';}

		if ($NumberOfNights == '21 Nights') {$Selected_15 = 'selected="selected"';}





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



		$CalendarDate = $_POST['CalendarField'];

		$_SESSION['CalendarDate'] = $_POST['CalendarField'];


		$NumberOfNights = $_POST['NumberOfNights'];

		$_SESSION['NumberOfNights'] = $NumberOfNights;




		if ($NumberOfNights == '7 Nights') {$Selected_1 = 'selected="selected"'; $NightsForQuery = 7;}

		if ($NumberOfNights == '8 Nights') {$Selected_2 = 'selected="selected"'; $NightsForQuery = 8;}

		if ($NumberOfNights == '9 Nights') {$Selected_3 = 'selected="selected"'; $NightsForQuery = 9;}

		if ($NumberOfNights == '10 Nights') {$Selected_4 = 'selected="selected"'; $NightsForQuery = 10;}

		if ($NumberOfNights == '11 Nights') {$Selected_5 = 'selected="selected"'; $NightsForQuery = 11;}

		if ($NumberOfNights == '12 Nights') {$Selected_6 = 'selected="selected"'; $NightsForQuery = 12;}

		if ($NumberOfNights == '13 Nights') {$Selected_7 = 'selected="selected"'; $NightsForQuery = 13;}

		if ($NumberOfNights == '14 Nights') {$Selected_8 = 'selected="selected"'; $NightsForQuery = 14;}

		if ($NumberOfNights == '15 Nights') {$Selected_9 = 'selected="selected"'; $NightsForQuery = 15;}

		if ($NumberOfNights == '16 Nights') {$Selected_10 = 'selected="selected"'; $NightsForQuery = 16;}

		if ($NumberOfNights == '17 Nights') {$Selected_11 = 'selected="selected"'; $NightsForQuery = 17;}

		if ($NumberOfNights == '18 Nights') {$Selected_12 = 'selected="selected"'; $NightsForQuery = 18;}

		if ($NumberOfNights == '19 Nights') {$Selected_13 = 'selected="selected"'; $NightsForQuery = 19;}

		if ($NumberOfNights == '20 Nights') {$Selected_14 = 'selected="selected"'; $NightsForQuery = 20;}

		if ($NumberOfNights == '21 Nights') {$Selected_15 = 'selected="selected"'; $NightsForQuery = 21;}



		$CalendarDate = str_replace("/", "-", "$CalendarDate");


		$QueryDate = date("Y-m-d", strtotime($CalendarDate));



		include_once('../connect/connect.php');


//$bookingsCalendar = mysql_query("INSERT INTO bookingsCalendar (bookingDate, status, invoiceId) VALUES ('now()', 'booked', 'invoiceId')"); 




} // End - if (isset($_POST['SubmitCalendar'])) {

[PHP Learner]

Sorry screwed that up.

 

Ok so above, I grab the dates from the sessions created when a calendar is used (date and nights submitted) and posted back to the bookings page for processing.

 

So this is pretty much where my expertise runs dry lol.

 

Question!

 

1) How do you tell mysql to search for dates that don't already exist? So you want to tell it to...

SELECT * FROM bookingsCalendar WHERE bookingDate='$QueryDate'

But how do you say and include the next 7 days in mySQL talk?

 

 

Question!

 

Next step I suppose if we assume that the dates have been booked on say 01/06/2013 for 7 days,

 

2) How do we search for the next available block of 7 days despite the possibility of other blocks of dates existing in the table. The search has to find previous dates as well as future dates on the table. Really its a bid to aid the customer from loosing the will to live lol, but in all seriousness is highly practical and could make the difference between a conversion and nothing at all.

 

 

What do you think B?

Edited May 17, 2013 by PHP Learner

[Barand]

I made a few tweaks

CREATE TABLE `bookingscalendar` (
  `autoIncId` int(10) NOT NULL AUTO_INCREMENT,
  `bookingDate` date NOT NULL,
  `numDays` tinyint NOT NULL,
  `invoiceId` int(11) NOT NULL,
  PRIMARY KEY (`autoIncId`),
  KEY `inv` (`invoiceId`),
  KEY `date` (`bookingDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `invoice` (
  `invoiceId` int(10) NOT NULL AUTO_INCREMENT,
  `invoiceRef` varchar(30) NOT NULL,
  `invoiceDate` date NOT NULL,
  `custId` int(11) NOT NULL,
  PRIMARY KEY (`invoiceId`),
  KEY `cust` (`custId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `customer` (
  `custId` int(10) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(20) DEFAULT NULL,
  `lastName` varchar(20) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phoneNo` varchar(15) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `county` varchar(50) DEFAULT NULL,
  `country` varchar(30) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`custId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

so instead of storing this for a seven-day booking

+-----------+-------------+-----------+
| autoincno | bookingdate | invoiceId |
+-----------+-------------+-----------+
|        19 | 2013-06-01  |      1234 |
|        20 | 2013-06-02  |      1234 |
|        21 | 2013-06-03  |      1234 |
|        22 | 2013-06-04  |      1234 |
|        23 | 2013-06-05  |      1234 |
|        24 | 2013-06-06  |      1234 |
|        25 | 2013-06-07  |      1234 |
+-----------+-------------+-----------+

you only store

+-----------+-------------+---------+-----------+
| autoincno | bookingdate | numDays | invoiceId |
+-----------+-------------+---------+-----------+
|        19 | 2013-06-01  |   7     |    1234   |
+-----------+-------------+---------+-----------+

More efficient but processing is a little more complex.

 

I don't know how your invoice/payments system works so I can't say specifically what your invoice table requires.

 

Is it the case that only a single item is available for booking? eg a single venue, Or do you need to track bookings for multiple items eg hotel rooms?

[PHP Learner]

Nice!

 

That's got to be the most efficient calendar table there is. Well the invoice table is probably something I'll come back to refine a bit later on, for now if I'm right here it will serve it's purpose. It will be necessary to have the ability to check for a number of rooms at a time, I was going to use a different table for each room to achieve that. Now that you mention it tho, do you reckon we should add a room number/name column after autoInc or something?

 

I'm really quite out of my depth as far as processing will go here, what would you suggest?

Edited May 17, 2013 by PHP Learner

[Barand]

Yes, add roomNo column.

 

You will also want a "room" table containing room attributes eg

 

roomNo

standard/deluxe

single/double/twin/family

en-suite

 

Prices will prob need a separate table too as they tend to vary by season, b&b/half-board/full-board etc

[PHP Learner]

Hello mate,

 

I think the room table isn't necessary at this stage, all the information for the types of rooms and facilities will be in each page.

 

A prices table is a good suggestion I will be making the client a small CMS to change their requirements depending on fluctuations in their tariffs etc as you outlined above so thanks for that.

 

The brass tacks of it all now really is the way we work with the bookingsCalendar table you tweaked this morning, care to print some logic Barand?

[PHP Learner]

Oh sorry,

 

It's this code here isn't it?

$db = new mysqli(HOST, USERNAME, PASSWORD, 'test');

$prefDate = '2013-06-24';    // preferred date
$reqDays = 3;                // days required

$sql = "SELECT bookingdate
        FROM bookingscalendar
        WHERE status = 'available'
            AND bookingdate >= CURDATE()
        ORDER BY bookingdate";
$res = $db->query($sql);

$dates = $earlier = $later = array();
$count = 0;

$preferred = new DateTime($prefDate);
$prevDay = new DateTime();
$prevDay->modify('-1 days');

/********************************
*  search for N cosecutive dates
*********************************/
while (list($bd) = $res->fetch_row()) {
    $dt = new DateTime($bd);
    if ($dt->diff($prevDay)->days==1 ) {
        $dates[] = $dt->format('Y-m-d');
    }
    else {
        $dates = array($dt->format('Y-m-d'));
    }    
    if (count($dates)>=$reqDays) {
        if ($dt->diff($preferred,0)->format('%R%a') > 1-$reqDays) {
            $earlier = array_slice($dates, -$reqDays); // last available N days prior
        } else {
            $later = array_slice($dates, -$reqDays);   // next available N days
            break;
        }
    }
    $prevDay = clone $dt;
}
// output search results
if ($later[0] != $prefDate) {
    echo "Prior dates: " . join(' | ', $earlier) . '<br>';
    echo "Later dates: " . join(' | ', $later);
} else {
    echo join(' | ', $later) . " is available";
}

[Barand]

 

I think the room table isn't necessary at this stage, all the information for the types of rooms and facilities will be in each page.

 

Where will that information come from - perhaps a room table?

How does your program know what rooms there are without a room table?

 

That code needs adapting to the revised version of the calendar table so it looks for gaps between the bookings that are sufficient for the number of days required

[PHP Learner]

I want to thank you very much for your patients Barand,

 

Im sorry if this is difficult to understand but the fact of the matter is that the description of the rooms will not need a table because it will be hard coded if you like into its own page. There simply is no need for a rooms table because these details are not the kind that change very often unless they are altering the premises in some way and if or when that is so, I'll cross that bridge.

 

So it's just the room name/number we need which we will add to the table like so.

ALTER TABLE `bookingscalendar` ADD `roomId` VARCHAR( 30 ) NOT NULL ;

That said, the main focus is the search function for the bookingscalendar which after optimising the original table (with much gratitude!) is where we began.

 

Cheers mate,

 

php LEARNER