Hi everybody,
thank you for all the help and thoughts. I have solved it, but I guess
it is not an elegant solution. What I do now, is simply check again for
the second case. There are 2 cases. Either first page OR all pages,
second case: following pages OR all pages.
My booking checking looks now as following:
################################################################################################
# on which page will the tl be placed?
if ($data[page] == 3){ // all pages
$where_page = 'AND (page = 1 OR page = 3)'; // unfortunatelly we
have to test later on page = 2 OR page = 3. No solution inside one
query. We tried also (page=1 XOR page=2) OR page = 3
$where_page_2 = 'AND (page = 2 OR page = 3)';
}
else{ // page one or all following pages
$where_page = 'AND page = '.$data[page];
}
################################################################################################
################################################################################################
# find out first possible booking period
do{
// get all toplistings that are at least with one day inside the
desired booking period
$stmt= "
SELECT
*
FROM
$DB.$T54
WHERE
cat_id = '$data[cat_id]'
AND cat_type = '$data[cat_type]'
$where_page
AND
(
(start_date <= '$new_start' AND expires >=
'$new_start')
OR (start_date <= '$new_end' AND expires >= '$new_end')
OR (start_date >= '$new_start' AND start_date<=
'$new_end')
)
";
#echo $stmt.$br;
$result = execute_stmt($stmt, $link);
while ($row = db_get_row($result)){
$booked[start][] = $row->start_date;
$booked[end][] = $row->expires;
}
$possible_bookings = count($booked[start]);
// would there be more bookings then possible?
if ($possible_bookings >= $places){ // not enough space. Try
nest day
$shift = true; // shift period for one day
$reservation = 1;
$new_start = date("Ymd",strtotime($new_start." + 1 day"));
$new_end = date("Ymd",strtotime($new_end." + 1 day"));
}
else{ // enough space
unset($shift);
}
unset($booked);
} while ($shift); // shift as long as we find free space
################################################################################################
################################################################################################
# if client wants to book all pages, we have to try also the second
constellation
# we could not find a way to do this in one sql query
# find out if booking period has to be shifted even further due to
all pages booking
if ($page == 3){
do{
// get all toplistings that are at least with one day inside
the desired booking period
$stmt= "
SELECT
*
FROM
$DB.$T54
WHERE
cat_id = '$data[cat_id]'
AND cat_type = '$data[cat_type]'
$where_page_2
AND
(
(start_date <= '$new_start' AND expires >=
'$new_start')
OR (start_date <= '$new_end' AND expires >=
'$new_end')
OR (start_date >= '$new_start' AND start_date<=
'$new_end')
)
";
//echo $stmt.$br;
$result = execute_stmt($stmt, $link);
while ($row = db_get_row($result)){
$booked[start][] = $row->start_date;
$booked[end][] = $row->expires;
}
$possible_bookings = count($booked[start]);
// would there be more bookings then possible?
if ($possible_bookings >= $places){ // not enough space. Try
nest day
$shift = true; // shift period for one day
$reservation = 1;
$new_start = date("Ymd",strtotime($new_start." + 1
day"));
$new_end = date("Ymd",strtotime($new_end." + 1 day"));
}
else{ // enough space
unset($shift);
}
unset($booked);
} while ($shift); // shift as long as we find free space
}
################################################################################################
This is rather a dirty solution. Maybe someone has an idea on how to do
it more elegant? I believe there should be one simple line that is
different instead of checking it all over again for a second time.
Any ideas?
Kim Madsen wrote:
Hey Merlin
Merlin Morgenstern wrote on 2009-12-07 11:52:
Hello everybody,
I am having trouble finding a logic for following problem:
Should be true if:
page = 1 OR page = 3, but it should also be true if page = 2 OR page = 3
The result should never contain 1 AND 2 in the same time.
This obviously does not work:
(page = 1 OR page = 3) OR (page = 2 OR page = 3)
This also does not work:
(page = 1 OR page = 3 AND page != 2) OR (page = 2 OR page = 3 AND
page != 1)
Has somebody an idea how to solve this?
I've read the entire thread and can see that this is a MySQL query you
want to make (I was about to tell you about the == comparison and the
$ in a variable in PHP).
What you want is all results containing 1,2 or 3, so make a "WHERE
page IN(1,2,3)" and use PHP logic to figure out if a free slot is
available or not. Or rewrite your booking routine (use data and time
fields instead, maybe by creating a bunch of free slots and then a
booked field with a default of "0", changed to "1" when booked)
