Forum Moderators: coopster
The problem was that it worked perfect only that if there where more than one booking for an property in that month, there were several rows for that property.
Now I have the oldest mysql version so I can use group_concat, and I am trying to concatenate the rows with group_concat, but it donīt work at all, what am I doing wrong?
When trying the code in phpmyadmin, I get all arrival and departuredates correctly, the problem is in displaying them.
This is the select:
SELECT group_concat(llegada), group_concat(salida), propiedad from bookings where ((llegada >= '2005-09-01' AND salida <= '2005-09-31') or (llegada BETWEEN '2005-09-01' AND '2005-09-31') or (salida BETWEEN '2005-09-01' AND '2005-09-31')) group by propiedad
And to display:
do {
$fecha1 = '2005-09-01';
$fecha2 = '2005-09-02';
and so on
$n = 'N';
$l = '';
if ($fecha1 >=$row["llegada"] && $fecha1<=$row["salida"]){ $fecha1=$n; }
else { $fecha1=$l; }
if ($fecha2 >=$row["llegada"] && $fecha2<=$row["salida"]){ $fecha2=$n; }
else { $fecha2=$l; }
and so on
echo "<th>".$row["propiedad"]."</th> \n";
echo "<th>".$fecha1."</th> \n";
echo "<th>".$fecha2."</th> \n";
and so on
So maybe group_concat is not the correct way or am I doing it wrong?
Thanks in advance from an desperate :)
I have an table mysql with bookings and where I insert arrival and departure dates, type date and as well the properties.
I want to display all properties that are booked for exampel between 2005-09-01 to 2005-09-31
That I want to do like this:
N = not available
1/9 2/9 3/9 4/9 etc
Property 1 N N N
Property 2 N
etc.
As I said before I can do it perfect if only one booking between the dates selected, but the troubble is when there are two between the dates selected.
The problem I have is to display it.
SELECT
propiedad,
MONTH(llegada) AS llegadaMonth,
DAY(llegada) AS llegadaDay,
DAY(salida) AS salidaDay
FROM bookings
WHERE
(llegada BETWEEN '2005-09-01' AND '2005-09-31')
OR
(salida BETWEEN '2005-09-01' AND '2005-09-31')
ORDER BY propiedad, llegada
;
The problem is that if there is more than one booking for the same month and for the same property there is more than one row for the property having 2 or more bookings......
Thats the reason I tried group_concat in mysql.
The problem is in the displaying,
do {
$fecha1 = '1';
$fecha2 = '2';
etc
if ($row["llegadaMonth"] == '9' ){
if ($fecha1 ==$row["llegadaDay"]) { $fecha1=$n; } }
elseif ($fecha1 ==$row["salidaDay "]){ $fecha1=$n; }
else { $fecha1=$l; }
etc
echo "<th>".$row["propiedad"]."</th> \n";
echo "<th>".$fecha1."</th> \n";
echo "<th>".$fecha2."</th> \n";
etc
The problem is that if there is more than one booking for the same month and for the same property there is more than one row for the property having 2 or more bookings......
You mean the same property in the same month on the same day?
I found your related thread a bit back,
[webmasterworld.com...]
I'm assuming you are still going for the layout described in msg#12 where the "N" and "Y" entries are for each day of the given month, so there would be 30 of them across the row representing the booking status for each day in September ...?
You mean the same property in the same month on the same day?I found your related thread a bit back,
[webmasterworld.com...]I'm assuming you are still going for the layout described in msg#12 where the "N" and "Y" entries are for each day of the given month, so there would be 30 of them across the row representing the booking status for each day in September ...?
I mean same month and same property but not same day.
I get 2 rows for property 1 if have booking 1/9 to 8/9 and 15/9 to 20/9, suppose the way I do to get the results is not correct...
Yes it is the same as my old post, by that time I did an workaround, an extra table mysql where I put in the dates as name of the columns, and when I insert the bookings in table bookings as well I insert in the extra table, works but must be an better way though I must update that table every month.
Though my host upgraded to the last mysql version and now I can use group_concat I am on it again, I thougt group_concat would do it.
$year['start']=2005;
$month['start']=9;
$day['start']=1;
$year['end']=2005;
$month['end']=9;
$day['end']=30;
$sel['start']=$year['start']."-".$month['start']."-".$day['start'];
$sel['end']=$year['end']."-".$month['end']."-".$day['end'];
SELECT
propiedad,
MONTH(llegada) AS llegadaMonth,
DAY(llegada) AS llegadaDay,
DAY(salida) AS salidaDay
FROM bookings
WHERE
(llegada BETWEEN '".$sel['start']."' AND '".$sel['end']."')
OR
(salida BETWEEN '".$sel['start']."' AND '".$sel['end']."')
ORDER BY propiedad,llegada ASC
;
while($selectedrow = mysql_fetch_array($yourresults)) {
if($selectedrow['propiedad']!=$sel['start'] && $selectedrow['llegada']!=$sel['start']) {
$sel['start']=$year['start']."-".$month['start']."-".$day['start']++;
}
else {
echo "The stuff you need to print out here."
}
}
Justin
This will not work as written! Just an idea from something I use... don't have time work all the way through the logic - basically, the suggestion is to use a loop and a counter increment on the day in the display - I broke the dates up, so they could easily be implemented to scroll through results based on month or year also:
Iīve been looking at it,
Thanks a lot, but I am afraid I donīt understand what to do with it....think an counter increment is to advanced to me, or maybe not what I want or need?
The way I do it works perfect, maybe not the best way, but the mysql seclect works perfect, only that I get more than one row if more than one booking when I display the select.
And coopsters select is not correct, though coopsters would leave out the bookings starting in august ending in september etc, but the important thing at this moment is how to display it using php, thats where I am stuck.
I want an table displayed like this
property 1/9 2/9 3/9 4/9 etc
name1 A A N N
name2 N N N N
N = not available
A = available
Thanks, seems mission imposible for me.
<?php
$llegada = '2005-09-01';
list ($lyr, $lmo, $lda) = explode('-', $llegada);
// Build the last day of the month:
$salida = date('Y-m-d', mktime(0,0,0,$lmo+1,0,$lyr));
list ($syr, $smo, $sda) = explode('-', $salida);
$stmt = "
SELECT
propiedad,
MONTH(llegada) AS llegadaMonth,
DAY(llegada) AS llegadaDay,
MONTH(salida) AS salidaMonth,
DAY(salida) AS salidaDay
FROM bookings
WHERE
(llegada BETWEEN '$llegada' AND '$salida')
OR
(salida BETWEEN '$llegada' AND '$salida')
ORDER BY propiedad, llegada
";
$rows = mysql_query($stmt);
$propiedad = false;
$calendar = array();
while ($row = mysql_fetch_array($rows)) {
if ($propiedad!== trim($row['propiedad'])) {
$propiedad = trim($row['propiedad']);
$calendar[$propiedad] = array_fill(1, $sda, 'A');
}
$begin = ($row['llegadaMonth'] == $lmo)? $row['llegadaDay'] : 1;
$end = ($row['salidaMonth'] == $lmo)? $row['salidaDay'] : $sda;
foreach (range($begin, $end) as $day) {
$calendar[$propiedad][$day] = 'Y';
}
}
print "<table>\n";
print "<tr>\n";
foreach (range($lda, $sda) as $day) {
print "\t<td>$day</td>\n";
}
print "</tr>\n";
foreach ($calendar as $propiedad => $bookings) {
$bookings = implode("</td>\n\t<td>", $bookings);
print "<tr>\n\t<td>$bookings</td>\n</tr>\n";
}
print '</table>';
?>
At this moment I only copied and past the code to try it.
And I get as an result numbers from 1-30 and rows filled in, in all 30 columns, (except 1 row that has 31 columns?).
A stupid question: On the code you done should the name of the properties show up in the table printed out?
As well I will need to Join another table to get the name of the properties that donīt have any bookings at all.
If the select shows the availability correctly or not I canīt tell yet, though the name of the properties donīt show up.
So I have homework to do...
The query is still much the same, one new column added which will allow us to look at the end month as well, you know for those date ranges that fall outside of any given month.
Prior to the query statement I just modified the way we can break up the start day and build the end day based on the start day for any given month using PHP's date() and mktime() functions.
After we execute the query and get the result set, we initialize a couple of variables before we begin the heart of the processing, the while loop which analyzes and processes data based on what we see for each row returned. If there is a new property, start a new array index for that property and prefill it's values with 'A' to initialize every day of the month with a default value, in this case, "Available". Then we analyze the date range to see which days to switch to 'Y' to show it is not available. Oops, just realized you wanted that to be an 'N', not a 'Y' -- you'll have to make that adjustment. Next we read the next row and keep on going the same way until we have read every row returned.
Last step then is to take our new array of data and format it for display.
One row that has 31 columns? Are you certain you don't have an invalid date in your database? There are only 30 days in September (I noticed that after I copied and used your code from the first message in this thread).
No, I did not add the Property, I left some of it for you to fill in ;)
print "<table>\n";
print "<tr>\n\t<td>Propiedad</td>\n";
foreach (range($lda, $sda) as $day) {
print "\t<td>$day</td>\n";
}
print "</tr>\n";
foreach ($calendar as $propiedad => $bookings) {
$bookings = implode("</td>\n\t<td>", $bookings);
print "<tr>\n\t<td>$propiedad</td>\n\t<td>$bookings</td>\n</tr>\n";
}
print '</table>';
The JOIN shouldn't be too difficult but if you get stuck, we're here ;)
The JOIN shouldn't be too difficult but if you get stuck, we're here ;)
Shouldnīt be, but for somebody like me....
I been trying, but canīt get it.
However the select now seems to be correct (I added more to it), works perfect when only using table bookings.
Below code gives me all the properties from the second table "casa" which one has the name of all properties with their link and category, but the availability shown up is not correct and nor is the $zone or $sleeps.
Actually I ever displayed other way but using $row[whatever] in the while loop ($row = mysql_fetch_array($rows)) but in your example the while brackets are closed so supose I need to put the id.properties, zone etc into the $calendar:
Trying to change propiedad to id_propiedad,
please donīt laugh :)
$llegada = '2005-10-01';
list ($lyr, $lmo, $lda) = explode('-', $llegada);
// Build the last day of the month:
$salida = date('Y-m-d', mktime(0,0,0,$lmo+1,0,$lyr));
list ($syr, $smo, $sda) = explode('-', $salida);
$stmt = " SELECT id_propiedad, link_ingles, zona, duermen, propiedad, MONTH(llegada) AS llegadaMonth, DAY(llegada) AS llegadaDay, MONTH(salida) AS salidaMonth, DAY(salida) AS salidaDay FROM casa, bookings WHERE casa.id_propiedad = bookings.propiedad and casa.tipo = '3' AND (llegada BETWEEN '$llegada' AND '$salida') OR (salida BETWEEN '$llegada' AND '$salida') or (llegada <= '$llegada' AND salida >= '$salida') ";
$rows = mysql_query($stmt);
$id_propiedad = false;
$calendar = array();
while ($row = mysql_fetch_array($rows)) {
$zone = ($row['zona']);
$sleeps = ($row['duermen']);
if ($id_propiedad!== trim($row['propiedad'])) {
$id_propiedad = trim($row['id_propiedad']);
$calendar[$id_propiedad] = array_fill(1, $sda, 'A');
}
$begin = ($row['llegadaMonth'] == $lmo)? $row['llegadaDay'] : 1;
$end = ($row['salidaMonth'] == $lmo)? $row['salidaDay'] : $sda;
foreach (range($begin, $end) as $day) {
$calendar[$id_propiedad][$day] = 'N';
}
}
print "<table>\n";
print "<tr>\n\t<td>Propiedad</td>\n";
print "\n\t<td>*</td>\n";
print "\n\t<td>**</td>\n";
foreach (range($lda, $sda) as $day) {
print "\t<td>$day</td>\n";
}
print "</tr>\n";
foreach ($calendar as $id_propiedad => $bookings) {
$bookings = implode("</td>\n\t<td>", $bookings);
print "<tr>\n\t<td>$id_propiedad</td>\n\t<td>$zone</td>\n\t<td>$sleeps</td>\n\t<td>$bookings</td>\n</tr>\n";
}
print '</table>';
this query gives me in phpmyadmin the results bad:
SELECT id_propiedad, link_ingles, zona, duermen, propiedad, MONTH(llegada) AS llegadaMonth, DAY(llegada) AS llegadaDay, MONTH(salida) AS salidaMonth, DAY(salida) AS salidaDay FROM casa, bookings where casa.id_propiedad = bookings.propiedad and casa.tipo = '3' AND (llegada BETWEEN '2005-12-01' AND '2005-12-31') OR (salida BETWEEN '2005-12-01' AND '2005-12-31') or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
It donīt give me the same id_propiedad as propiedad in phpmyadmin,
I tried left join bookings, right join casa, join, inner join, whats left?
mysql donīt care at all about: casa.id_propiedad = bookings.propiedad and casa.tipo = '3' even gives me properties which tipo is not '3'.
Howerer, the query is identic as my previous expceptuating coopsters way of choose the dates, and works perfect in phpmyadmin:
SELECT id_propiedad, link_ingles, zona, duermen, llegada, salida, propiedad from casa, bookings where casa.id_propiedad = bookings.propiedad and casa.tipo = '3' and ((llegada >= '2005-12-01' AND salida <= '2005-12-31') or (llegada BETWEEN '2005-12-01' AND '2005-12-31') or (salida BETWEEN '2005-12-01' AND '2005-12-31'))
why the difference between both?
This select gives the result ok using left join bookings, and where there are no bookings I get as result null.
SELECT id_propiedad, link_ingles, zona, duermen, llegada, salida, propiedad from casa left join bookings on casa.id_propiedad = bookings.propiedad and casa.tipo = '3' and ((llegada >= '2005-12-01' AND salida <= '2005-12-31') or (llegada BETWEEN '2005-12-01' AND '2005-12-31') or (salida BETWEEN '2005-12-01' AND '2005-12-31'))
But that select is not the one to use,
This select that is the one to use gives me 7 rows using only table bookings:
SELECT propiedad, MONTH(llegada) AS llegadaMonth, DAY(llegada) AS llegadaDay, MONTH(salida) AS salidaMonth, DAY(salida) AS salidaDay FROM bookings where (llegada BETWEEN '2005-12-01' AND '2005-12-31') OR (salida BETWEEN '2005-12-01' AND '2005-12-31') or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
This select that is the one to use gives me 9 rows using only table casa:
SELECT id_propiedad, link_ingles, zona, duermen FROM casa where casa.tipo = '3'
This select that is the one to use gives me 168 rows using both tables:
SELECT id_propiedad, link_ingles, zona, duermen, propiedad, MONTH(llegada) AS llegadaMonth, DAY(llegada) AS llegadaDay, MONTH(salida) AS salidaMonth, DAY(salida) AS salidaDay FROM casa left join bookings on casa.id_propiedad = bookings.propiedad and casa.tipo = '3' AND (llegada BETWEEN '2005-12-01' AND '2005-12-31') OR (salida BETWEEN '2005-12-01' AND '2005-12-31') or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
when clicking on explain mysql I get this result:
1 SIMPLE casa ALL NULL NULL NULL NULL 28 rows
1 SIMPLE bookings ALL NULL NULL NULL NULL 257 rows
The results from the tables are repeated several times and the on rules ignored.
select distinctrow (
case when (llegada BETWEEN '2005-12-01' AND '2005-12-31' ) or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
then MONTH(llegada)
else null end
) AS llegadaMonth ,
(
case when (salida BETWEEN '2005-12-01' AND '2005-12-31' ) or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
then MONTH(salida)
else null end
) AS salidaMonth,
(
case when (llegada BETWEEN '2005-12-01' AND '2005-12-31') or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
then DAY(llegada)
else null end
) AS llegadaDay,
(
case when (salida BETWEEN '2005-12-01' AND '2005-12-31') or (llegada <= '2005-12-01' AND salida >= '2005-12-31')
then DAY(salida)
else null end
) AS salidaDay,
id_propiedad, link_ingles, zona, duermen, propiedad
from bookings right outer
join casa on casa.id_propiedad = bookings.propiedad and casa.tipo = '3'
Any thoughts?
Anyway I am closer now, though I have the correct select, and changing propiedad to id_propiedad the calendar shows the bookings correctly,
the only problem is that the properties from table casa that donīt have any booking at all, gets marked with an N instead of an A....
This is what I got so far:
$llegada = '2005-10-01';
list ($lyr, $lmo, $lda) = explode('-', $llegada);
// Build the last day of the month:
$salida = date('Y-m-d', mktime(0,0,0,$lmo+1,0,$lyr));
list ($syr, $smo, $sda) = explode('-', $salida);
$stmt = " SELECT id_propiedad, MONTH( llegada ) AS llegadaMonth,
DAY (llegada) AS llegadaDay, MONTH( salida ) AS salidaMonth,
DAY (salida) AS salidaDay FROM casa
LEFT JOIN bookings ON bookings.propiedad = casa.id_propiedad
AND (llegada BETWEEN '$llegada' AND '$salida' OR salida
BETWEEN '$llegada' AND '$salida' OR (llegada <= '$llegada' AND salida >= '$salida'))
WHERE casa.tipo =3
ORDER BY id_propiedad";
$rows = mysql_query($stmt);
$id_propiedad = false;
$calendar = array();
while ($row = mysql_fetch_array($rows)) {
if ($id_propiedad!== trim($row['id_propiedad'])) {
$id_propiedad = trim($row['id_propiedad']);
$calendar[$id_propiedad] = array_fill(1, $sda, 'A');
}
$begin = ($row['llegadaMonth'] == $lmo)? $row['llegadaDay'] : 1;
$end = ($row['salidaMonth'] == $lmo)? $row['salidaDay'] : $sda;
foreach (range($begin, $end) as $day) {
$calendar[$id_propiedad][$day] = 'N';
}
}
print "<table>\n";
print "<tr>\n\t<td>Propiedad</td>\n";
foreach (range($lda, $sda) as $day) {
print "\t<td>$day</td>\n";
}
print "</tr>\n";
foreach ($calendar as $id_propiedad => $bookings) {
$bookings = implode("</td>\n\t<td>", $bookings);
print "<tr>\n\t<td>$id_propiedad</td>\n\t<td>$bookings</td>\n</tr>\n";
}
print '</table>';
?>
It is much easier to understand and help if you can give some specific examples of what the data looks like in the table, and then how you want to format and display it in the browser.
Here is the final join, and the display almost working, expect an strange error:
[webmasterworld.com...]
