Checking availability for holidays using php

is it complicated?

[lasko]

I am having a great time discovering the world of php and now find myself keen to produce a new program after another.

I need to create a simple script that checks the availability of a holiday rental or hotel.

Has anyone had any experience in this sort of program and is it complicated to construct?

What will be required to get started for a basic setup and then later on expand it easily to produce more user friendly results producing tables of dates available etc.

I need a short term solution that i can expand easily.

Can anyone give me some points to think about that I may have to do.

Thanks for your time

[jamesa]

That's what it should do if all the apartments are available for the given date range. What are your $start and $end values and what do you have in the reservations table?

[lasko]

Hi,

Here is a table of reservations I am testing against

Ref¦ID ¦.......fromdate....¦......todate.......
-----------------------------------------------
001¦501¦2003-01-01 12:00:01¦2003-01-07 12:00:00
008¦505¦2003-06-01 12:00:01¦2003-10-01 12:00:00
003¦504¦2003-02-01 12:00:01¦2003-02-07 12:00:00
004¦501¦2003-01-07 12:00:01¦2003-01-09 12:00:00
007¦504¦2003-06-05 12:00:01¦2003-07-11 12:00:00

Notice the time after the dates, this is so the apartment
appears available the day it becomes free.

Here are the dates strings

$timein = "12:00:01"; // set for all enquiries
$timeout = "12:00:00"; // set for all enquiries
$start = "$byear-$bmon-$bday $timein";
$end = "$eyear-$emon-$eday $timeout";

The $byear-$bmon-$bday are being fed by a simle selection form then I add the $timein and $timeout to enable precise
results of an apartment coming available on a day when somebody leaves.

I have tested dates for checking availability like:

Start: 2003-01-01 12:00:01
End: 2003-01-07 12:00:00

Also tested dates that overlap which have sometimes proved to be very wrong.

Start: 2003-01-01 12:00:01
End: 2003-02-05 12:00:00

I am feeding the dates through a form as its easy to test, because you need to sit down and just feed it a load of dates to see the output.

[lasko]

Going back to basics

The command

SELECT * apartments WHERE id NOT IN ('$booked_list')";

is not processing the commas properly.

Doing it manually

$booked_list = "501";

will list all apartments except 501.

$booked_list = "501,502,504";

will not work, lists all apartments including 501,502,503.

It seems that the comma is not being read.

I still think we are on the right track, we manage to list all the apartments booked just need to knock it off the ones availalbe.

[aspdaddy]

>SELECT * apartments WHERE id NOT IN ('$booked_list')";

Should those single quotes be there?

[jamesa]

I just tested it with the single quotes , and yes, it won't work properly with the single qoutes - it'll just ignore everything after the first comma. Same with double quotes, so don't use any quotes in there at all.

SELECT * FROM apartments WHERE id NOT IN ($booked_list);

[lasko]

Hi,

I had put the single or double qoutes as it would not work without them.

I get the error message

Query failed : Unknown column '501' in 'where clause'

When using

$booked_list = implode(",", $booked_array);

$query = "SELECT * FROM apartments WHERE id NOT IN($booked_list)";

It thinks '501' should be a column and not ID, the principle is correct where t selets all apartments where the <> the id in reservations.

I have tried using the <> as well like

$query = "SELECT * FROM apartments WHERE id <>($booked_list)";

$query = "SELECT * FROM apartments WHERE id <>($id)";

these don't work either.

[jamesa]

Do an

echo $query

after the

$query =

line and lets see what it looks like. Then run that directly in mysql and see what the error is.

[lasko]

ok guys

I did two echo commands

This command

$query = "SELECT * FROM apartments WHERE id NOT IN($booked_list)";
echo "$query = 'SELECT * FROM apartments WHERE id NOT IN($booked_list)'";

output:
-------------------
SELECT * FROM apartments WHERE id NOT IN(501,504) = 'SELECT * FROM apartments WHERE id NOT IN(501,504)'Query failed : Unknown column '501' in 'where clause'
-------------------

And this command

$query = "SELECT * FROM apartments WHERE id NOT IN($booked_list)";
echo "$id";

output:
-------------------
504Query failed : Unknown column '501' in 'where clause'
-------------------

We now know that the implode is putting it into commas correctly put the select command is having problems going through the list.

[jamesa]

Well the script is building the $booked_list OK, so that just leaves the query. I'm just guessing here since I can't duplicate the problem, but try running this against the database (the difference being the quotes):

SELECT * FROM apartments WHERE id NOT IN("501","504");

If that works then change:

$booked_array[] = $line['ID'];

to read:

$booked_array[] = '"' . $line['ID'] . '"';

[lasko]

The test using

Select * from apartments WHERE id NOT IN('501','504')";

worked perfect.

So I changed the commands to

$booked_array[] = ' " ' . $line['ID'] . ' " ';

}
$booked_list = implode(",", $booked_array);

$query = "SELECT * FROM apartments WHERE id NOT IN($booked_list)";

but still creates the full list. :(

-----------

I have just done another test where I place the echo command after the implode to print out the $booked_list

$booked_array[] = ' " ' . $line['ID'] . ' " ';
}
$booked_list = implode(",", $booked_array);
echo "$booked_list";

$query = "SELECT * FROM apartments WHERE id NOT IN($booked_list)";

And the output was

" " , " "

So it won't find it any apartments :(

[jamesa]

what do you get when you echo $link['ID'] in the while loop? Are you sure you have $start and $end dates that will match a booked apartment? Anything else change?

Maybe build the string first and then pop it in the array:

$strg = '"' . $line['ID'] . '"'; 
$booked_array[] = $strg;

[lasko]

Their should be 3 reservations for 2 apartments

2 apartments should be crossed of the list when I run the script I echo the apartments that should be booked and get

" " " " , " "

I now echo the dates used for the check availability being $start and $end and I get the correct dates.

When I echo the $ID I get the 501,504 which is also correct but the implode is not writing the id into the $book_list

If it was writing it then the echo for $book_list would be the same as $id

We just need to list all the values into one string and find apartments that do not have the id in the string.

Doing 1 apartment is not really a problem, multiple apartments it goes wrong.

[jamesa]

Weird becaue it works on my end. Don't know what the difference could be. Anyway, see if this works:

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
$booked_array[] = $line['ID']; 
}

$count = 0; 
while (list ($key, $val) = each ($booked_array)) { 
if ($count > 0) { $delim = ','; } 
$booked_list .= $delim . '"' . $val . '"'; 
$count++; 
} 
echo $booked_list;

And delete the implode line.

[hakre]

hi lasko,

to figure the last error out first:

The test using
Select * from apartments WHERE id NOT IN('501','504')";

worked perfect.

So I changed the commands to

$booked_array[] = ' " ' . $line['ID'] . ' " ';

replace the line

$booked_array[] = ' " ' . $line['ID'] . ' " ';

with

$booked_array[] = "\'" . $line['ID'] . "\'";

or in words: if single quotes work in the sql query, use single quotes while creating the IN(###) part.

this should work then. btw, if the field id is numeric, quotes shouldn't be needed with mysql. checkout the documentation about this [mysql.com].

- hakre

[lasko]

Yipee

Its working!

Thank you jamesa

I tried your new version and it worked,

I received an error message after a few tests, and then I realised it was because I had tested all the apartments
where available. So I created a an if and else just to please the script incase all apartments will ever be available. Hope Not :)

I am going to have this tested now by several people, and then we will test it again.

Looks like we have it

Thank you very much for all your help!

:)