Page 1 of 1

PHP Conditions Problem

Posted: Sun May 09, 2010 4:34 pm
by hm9
I have a webpage in PHP that queries the database in mysql. There are many conditions that I need to check for when the query is submitted. for instance, users will have to select from many options like give or take 3 days etc.

I have used a base query and then set conditions. Every works apart from the condition part of the give or take 3 days. When the condition is executed, it just get ignored and only the base query is excecuted. I am not sure why its not working. Can you please advise me or check the code?.Note the condition part of the duration works as expected, its only the condition part for give or take that doesnt work.


----------------- Code--------------------------------------------------------





$dest = $_GET["destination"]; // Variable for destination is retrieved from form in previous page
$dep= $_GET["departuredate"]; // Variable for departure date is retrieved from form in previous page
$giveortake = $_GET["giveortake"]; // Variable for givr or take is retrieved from form previous page




$dep = mysql_real_escape_string($dep); $dest = mysql_real_escape_string($dest);

//$dep is in this format: dd/mm/yyyy so i will change it to mysql format yyyy-mm-dd
list($day, $month, $year) = split( '[/]', $dep);
$depx = "$year-$month-$day";
echo $depx;// to test it works and it works

//begin output exact match
$sql="SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price,buyurl, departureDate from holidays where departureDate = '$depx' and destination like '%$dest%' ";




if($duration=='7' || $duration=='3'){ $sql .= ' AND duration='.$duration; }

// up to here the code works fine as expected

if($giveortake=='3') { $sql .=' AND departureDate <='. "'$depx'" .' + INTERVAL '.$giveortake.' DAY';}



$result = mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error());
while ($row = mysql_fetch_array($result))
{

outputProduct($row)
;
}

------------------------end----------------------------------------

Thanks in advance :( :(

Re: PHP Conditions Problem

Posted: Sun May 09, 2010 5:56 pm
by mecha_godzilla
Have you tried 'echoing' out the $sql variable before it's being used in the query to see whether the additional values are being appended to it?

If the 'giveortake' if() statement isn't testing true then you need to check what value is actually being stored in the $giveortake variable. For testing purposes, I'd hard-code this rather than relying on the $_GET value for the moment (remember also to check that this value is spelled correctly/generated correctly in your form.) I can't see anything wrong with the code you've posted so I suspect the $giveortake variable might be the culprit.

HTH,

Mecha Godzilla

Re: PHP Conditions Problem

Posted: Mon May 10, 2010 3:33 am
by hm9
Thanks for the reply. I can echo the variable $giveortake correctly and it is spelt correctly.
I suspect it may have something to do with the format of the INTERVAL part. The previous if condition for duration works as expected but this one because I m using the INTERVAL, it may be the format or syntax?

if($giveortake=='3') { $sql .=' AND departureDate <='. "'$depx'" .' + INTERVAL '.$giveortake.' DAY';}


how would I do it if I just use a number instaed of using the variable $giveortake in the INTERVAL part of the condition. . any ideas?

Re: PHP Conditions Problem

Posted: Mon May 10, 2010 4:18 pm
by mecha_godzilla
Based on your code, this is what your SQL query looks like if all the if() tests are true:

Code: Select all

SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price,buyurl, departureDate
FROM holidays
WHERE departureDate = '$depx'
AND destination like '%$dest%'
AND duration = '$duration'
AND departureDate <= '$depx' + INTERVAL '$giveortake' DAY
The problem you might be having is that (as you say) the INTERVAL part of your query isn't working. I'm not sure whether this code is relevant but it demonstrates use of INTERVAL, and is used to retrieve all the rows in a table where a date in the 'date_col' is within the last 30 days:

Code: Select all

SELECT something
FROM tbl_name
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
This code was found here:

http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html

For your application, you would use DATE_ADD rather than DATE_SUB I think.

Let me know if that's any help or not anyway :)

M_G