Okay, let me try to break this down for you
if($giveortake=='3') { $sql .=' AND departureDate between "'$depx'" and "'$depx'" + 3;}
The problem here is that ’ AND departureDate between “‘$depx’” and “‘$depx’” is a string, and 3 is an integer and you’re using + to try to concatenate them. However, + is not the operator to concatenate string, but . (dot) is.
Effectively what you’re doing here is add ’ AND departureDate between “‘$depx’” and “‘$depx’” and 3 together, and since ’ AND departureDate between “‘$depx’” and “‘$depx’” is not a number it will evaluate to 0, thus you have 0 + 3 = 3.
Also, you’re ending the string and opening it again wrongly.
Key to understand here is that there are strings with single quotes and string with double quotes. These have different meaning. Strings in double quotes are parsed by PHP, while string in single quotes are not.
To give you an example
$name = 'ScallioXTX';
echo 'Hi, my name is $name'; // Hi, my name is $name
echo "Hi, my name is $name"; // Hi, my name is ScallioXTX
Note the difference between the single quotes and the double quotes.
So to combine this, you should have done this:
if($giveortake=='3') {
$sql .= " AND departureDate BETWEEN '$depx' AND '$depx' + INTERVAL 3 DAY";
}
That is, the value of the column departureDate should lie between $depx and $depx + 3 days
Now, for the if-then-else part (as per post #23).
If you select all rows that have a specific departureDate and also have a departureDate between that same date and another date, you end up with all rows that have the specific departureDate, and the interval is ignored.
This is easier to understand if we swap the different clauses in the WHERE query. Suppose we have this query
[SQL]
SELECT
field1,field2,field3
FROM
some_table
WHERE
departureDate BETWEEN ‘2010-05-10’ AND ‘2010-05-13’
AND
departureDate = ‘2010-05-10’
[/SQL]
Now, MySQL evaluates this query from left to right and will first find all rows that are between ‘2010-05-10’ and ‘2010-05-13’, so you might have an intermidiate result that contains the dates ‘2010-05-10’, ‘2010-05-11’ and ‘2010-05-13’. Now, we go over to the next part, WHERE deparureDate = ‘2010-05-10’. Clearly, ‘2010-05-11’ and ‘2010-05-13’ are not equal to ‘2010-05-10’ so they get dropped from the result, leaving you with only ‘2010-05-11’.
So, what you need to do is change the check for the departureDate in the WHERE clause.
So, your complete code thusfar would be
$dep = mysql_real_escape_string($dep);
$dest = mysql_real_escape_string($dest);
// rewrite $dep to YYYY-mm-dd format, and call that $depx
list($day, $month, $year) = split( '[/]', $dep);
$depx = "$year-$month-$day";
// Change MySQL keywords to uppercase, so you can discern them better from the rest of the code
$sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE ';
if ($giveortake == '3' || $giveortake == '5') {
// We now need to search in a range of dates, using BETWEEN and INTERVAL
$sql .= 'departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY';
} else {
// We need to check for exactly one date
$sql .= 'departureDate = "'.$depx.'"';
}
if($duration=='7' || $duration=='3') {
$sql .= ' AND duration='.$duration;
}
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
outputProduct($row);
}