availabilitychart

[helenp]

Hello,
I am very happy reaching this,
I am very new to php, and donīt know where to read about it,
finally Iīve been able to check the availability for bookings with mysql, and display with an N if not available on an calendar,

this is the code, (not perfect, testcode, but works)

but how to put the days in an array not to have 31 diferents if.s and 31 variables?:

$result = mysql_query ("SELECT llegada, salida, propiedad from bookings Where ((llegada >= '2004-12-01' AND salida <= '2004-12-31') or (llegada <= '2004-12-01' AND salida >= '2004-12-31') or (llegada BETWEEN '2004-12-01' AND '2004-12-31') or (salida BETWEEN '2004-12-01' AND '2004-12-31'))", $dbh);
if ($row = mysql_fetch_array($result)){

echo "<table border = '1'> \n";
echo "<tr> \n";
echo "<td><b>propiedad</b></td> \n";
echo "<td><b>1</b></td> \n";
echo "</tr> \n";

do {
$fecha1 = '2004-12-10';
$n = 'N';
if ($fecha1 >=$row["llegada"] && $fecha1<=$row["salida"]){ $fecha1=$n; }
echo "<tr> \n";
echo "<td>".$row["propiedad"]."</td> \n";
echo "<td>".$fecha1."</td> \n";
echo "</tr> \n";

} while ($row = mysql_fetch_array($result));
echo "</table> \n";
?>

[ergophobe]

Where do the 31 ifs and 31 vars come in? Do you mena to say that in the actual script you have

$fecha1, $fecha2, $fecha3 etc etc?

[helenp]

<<<<Where do the 31 ifs and 31 vars come in? Do you mena to say that in the actual script you have
$fecha1, $fecha2, $fecha3 etc etc?
>>>>>>>>>

Yes, I will have one var for every day of the month,
thanks.
Maybe there is a better way.

[helenp]

I just posted though I thought I fixed it using below code, but when inserting an testbooking from 1/12 to 3/12 id didnīt show up....
so I edited this post
so I still on it

$fecha2 = Array ("2004-12-01", "2004-12-02", "2004-12-03", "2004-12-04", "2004-12-05");
foreach ($fecha2 as $fecha1)
$n = 'N';
if ($fecha1 >=$row["llegada"] && $fecha1<=$row["salida"]){ $fecha1=$n; }
else { $fecha1=$a;}
echo "<tr> \n";
echo "<td>".$row["propiedad"]."</td> \n";
echo "<td>".$fecha1."</td> \n";
echo "<td>".$fecha1."</td> \n";
echo "<td>".$fecha1."</td> \n";
echo "<td>".$fecha1."</td> \n";
echo "<td>".$fecha1."</td> \n";
echo "</tr> \n";
} while ($row = mysql_fetch_array($result));
echo "</table> \n";

[jatar_k]

I would change a couple things

I wouldn't use
if ($row = mysql_fetch_array($result)){

it would seem all you are checking for is the existence of a result. I would just check the number of rows is greater than 0 like so

if (mysql_num_rows($result) > 0) {

rmember that you then need to reset your result pointer after getting the number of results before you start your mysql_fetch_array

mysql_data_seek($result,0);

I very seldom use do..while loops as well, I always just use while, personal preference, no big deal.

Now are you saying that the 5 cells you have here as an example will actually be 1 for everyday of the month? I want to understand that before I offer any suggestions regarding the date bit. I also don't really get what $fecha2 is all about, is it just for testing? If so, what would the reall data be?

[willybfriendly]

ergophobe, you're typing like an Italian emmigrant - you mena to say...$fecha1, $fecha2, $fecha3 etc etc?

You needa more cappacino thisa morning :)

WBF

[helenp]

thanks,
regarding preferences at the moment I am to new to have any, sometimes use that sometimes only while....

>>>Now are you saying that the 5 cells you have here as an example will actually be 1 for everyday of the month? I want to understand that before I offer any suggestions regarding the date bit. I also don't really get what $fecha2 is all about, is it just for testing? If so, what would the reall data be? >>>

Yes there will be one cell for every day of the month, and what I was trying is to put all the vars in one if statement: if ($fecha1 >=$row["llegada"] && $fecha1<=$row["salida"]){ $fecha1=$n; }

And this is what I want:
$fecha1 = '2004-12-01';
$fecha2 = '2004-12-02';
etc
$n = 'N';
if ($fecha1 >=$row["llegada"] && $fecha1<=$row["salida"]){ $fecha1=$n; }
if ($fecha2 >=$row["llegada"] && $fecha2<=$row["salida"]){ $fecha2=$n; }
etc
This way I would have 31 vars and 31 if statements, is there an better way?

About foreaching as $fecha2, trying and viewing code examples, I tested that way and I thought it worked, but only seemed like it did....so

Thanks to all

[ergophobe]

On the specific question, yes there is a better way.

Instead of
$fecha1 = '2004-12-01';
$fecha2 = '2004-12-02';

You could use an array [php.net] like so

$fecha[0] = '2004-12-01';
$fecha[1] = '2004-12-02';

Note that arrays usually start with a 0 element, not a 1. So your first element is $fecha[0]

Now you just loop through the array

for($i=0; $i<31; $i++) 
{ 
 if ($fecha[$i] >=$row["llegada"] && $fecha[$i] <=$row["salida"]) 
 {  
  $fecha[$i] = $n;  
 } 
} 

Now that covers the specific problem of not repeating all of those if statements.

I suspect, however, there may be some more general reworking to do with the general approach to the problem. Can you summarize in simple language (not code) what it is you are trying to do. In particular, how do you know which days to assign to $fecha? In other words, what happens next month and the month after that? Won't you need far more than 31 dates in the end?

[helenp]

Hi all,
1st jatar_k, I change to what you said, but I dont see why should it be better using if (mysql_num_rows($result) > 0) {
mysql_data_seek($result,0);
instead...

2nd, isnīt ergophobes array code similar as what I did in my post 4?
I am confused though now I am using ergophobes array and I still have problem but now I know why, so maybe I did it ok in my post 4 or maybe not?

ergophobes array loop works just fine thank you,
but now I discovered that the problem is in the select,

if i use this select it works perfect, but repeats the property if it is more than one booking for the month of december:
$result = mysql_query ("SELECT propiedad.id_propiedad, bookings.propiedad, llegada, salida from propiedad LEFT JOIN bookings ON propiedad.id_propiedad = bookings.propiedad AND ((llegada >= '2004-12-01' AND salida <= '2004-12-31') or (llegada <= '2004-12-01' AND salida >= '2004-12-31') or (llegada BETWEEN '2004-12-01' AND '2004-12-31') or (salida BETWEEN '2004-12-01' AND '2004-12-31'))", $dbh);

and if I add distint and/or group by id_propiedad, only one occupance is marked with an $n if there are more than one booking in december.....canīt figure out how to do the select...

I need to select all properties from table propiedad and then check if these properties have bookings for december in table bookings.

About your question: In particular, how do you know which days to assign to $fecha? In other words, what happens next month and the month after that? Won't you need far more than 31 dates in the end?

Actually I do the dates manually, I know one can do that dynamically, dynamic calendars, but I think that is still to advanced to me, so I just start with the easiest or better said less dificult, and later if I know how to, I will improve the code.

These availabilitycharts pages, until now I did manually in an normal html table, (and in 3 languages) so only to have the occupancy automatic done while inserting the bookings in mysql but not the dynamic dates will be an big improvent, so bit by bit.

That will be my next thread :)

[willybfriendly]

I need to select all properties from table propiedad and then check if these properties have bookings for december in table bookings.

I am still not completely clear on what you are trying to do. But, it might be that it would work better to retrieve your booking dates and then check to see what properties have been booked. That way you would not be needlessly fetching non-booked properties out of the DB.

Will you be listing all properties even if there are no dates booked? Or, are you only listing poperties with booked dates? Or, are you listing properties with open dates? Each of these would take a slightly different solution.

I tend to write my routines out as comments, and then write the code to accomplish what I have already commented. Something like:

//first we initialize an array of dates

//connect to db

//now we fetch dates and properties

//and populate the date array

//format it all into a nice table.

That way I have nicely commented code when I go back to make changes. I don't have to sit there scratching my head and thinking, "What the h**l was I trying to do here?" It also helps me to keep organized and stay away from the spagetti code as much.

WBF

[helenp]

>>>>That way you would not be needlessly fetching non-booked properties out of the DB.

Will you be listing all properties even if there are no dates booked? Or, are you only listing poperties with booked dates? Or, are you listing properties with open dates? Each of these would take a slightly different solution.
>>>>>>>>>>
Yes I will list as well properties without any bookings,.....there is the troubble

[willybfriendly]

So, I picture in my mind a page that looks like this

December 2004

Property Ķ Availability

property1 Ķ N Ķ N Ķ N Ķ Y Ķ Y Ķ Y Ķ Y Ķ Y Ķ.....etc.
property2 Ķ Y Ķ Y Ķ Y Ķ N Ķ N Ķ Y Ķ Y Ķ Y Ķ.....etc.
property3.......etc.
...etc.

Is that what you are trying for?

WBF

[helenp]

willybfriendly
You got it...........that is exactly what I have right now on the web, only that it is not dynamic...

And everything works, the problem is in the select, the query, or displayes property more than once if more than one booking for the month, or only mark one booking for property if use group by.

Suppose I could alway put the properties in manually,......as before, but that would make me do the table, and suppose can be done dynamically.

[willybfriendly]

I am not an sql guru.

So, I would tend to do a simple select for booking dates by property:

SELECT llegada, salida FROM bookings WHERE id_propiedad = '$id_propiedad' ORDER BY llegada

which will return one array of dates for each property. Easier to manipulate, but obviously a heavier load on the DB server.

How are your dates stored? Timestamps are easier to do calculations on for me.

Anyway, now we can work those dates into an array representing the month for that property pretty easily. I would start with an array of 31 "Y"s and then selectively change them to "N"'s based on the dates coming back from the db call.

I am not sitting where I can test code, so I am just sharing the thought process.

Someone on these boards can probably show a way to pull all of this out of the db in one step.

WBF

[helenp]

<<<<<<<SELECT llegada, salida FROM bookings WHERE id_propiedad = '$id_propiedad' ORDER BY llegada <<<<<<

thanks but I donīt think that would work, the reason to use 2 tables is that if I use only the bookingstable, new properties that ever had any booking wonīt apear, that could be arranged, adding an booking without dates, the second problem is that old properties, no longer managed will appear as well, that would oblige me to deleate these old bookings.

Or I didnīt understand what you said...
the way you put it, I would have to put manually the properties and give them an value as an var.

this should be the select arranging it with distinct or group by if posible....?
$result = mysql_query ("SELECT propiedad.id_propiedad, bookings.propiedad, llegada, salida from propiedad LEFT JOIN bookings ON propiedad.id_propiedad = bookings.propiedad AND ((llegada >= '2004-12-01' AND salida <= '2004-12-31') or (llegada <= '2004-12-01' AND salida >= '2004-12-31') or (llegada BETWEEN '2004-12-01' AND '2004-12-31') or (salida BETWEEN '2004-12-01' AND '2004-12-31')) group by id_propiedad, llegada, salida", $dbh);

The dates are stored as date columns.

[willybfriendly]

How is your current query screening inactive properties? It seems to me that without flagging properties as inactive, then you will only be able to fetch properties that have a booking, or all the properties in the db.

But, it seems you want all active properties, and then to highlight periods that are booked for specific properties, correct?

WBF

[helenp]

I catch inactive properties from talbe propiedad, then I join it with the bookingstable, works perfect if only one booking.

[helenp]

wow,
this is complicated, donīt work even in an less complex query, only works if are one booking for dates choosen.

with this query I get 2 rows, same as before.

As you can see that is for only one property, and if I take away the where statements for dates it returns even more rows...
So this way I canīt even do it for a single property,

SELECT llegada, salida from bookings WHERE propiedad = 'Banana_Beach_3' and ((llegada >= '2004-12-01' AND salida <= '2004-12-31') or (llegada <= '2004-12-01' AND salida >= '2004-12-31') or (llegada BETWEEN '2004-12-01' AND '2004-12-31') or (salida BETWEEN '2004-12-01' AND '2004-12-31'))

[helenp]

I figuered out that I could do it with group_concat if I had mysql 4.1,
and otherwise can be done in php to concat them,
but how?
but I have 4.0.....sniff,