Conditions in PHP

**hm9** · May 9, 2010 12:37

I am not sure if i understand you correctly. do you mean something like this?

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

**ScallioXTX** · May 9, 2010 13:37

No, I don't.
Please read my previous post again.
Also please read post #23 again, since I've got this feeling you still don't know what I said there.

I think you really should get a decent book on how to write PHP and MySQL, because it looks like your just hacking random characters together and hope it works. Programming doesn't work that way. No offence.

**hm9** · May 10, 2010 05:42

I have a PHP book and I looked at onlines tutorials, but cant find anything that explains like this scenario. We managed to sort out the duration bit which I understand, but the bit with INTERVAL is not straightforward.

You mention to use the Between bit but the examples I found in the book are simple and just compare between two dates... In my case, I have to use the Interval as well so Perhaps some clarification with details on your previous comments would help!

**ScallioXTX** · May 10, 2010 07:24

Okay, let me try to break this down for you

PHP Code:


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

PHP Code:


$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:

PHP Code:


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

PHP Code:


$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);

}

**ScallioXTX** · May 10, 2010 09:15

Hm, thats not entirely correct I see.
It should be:

PHP Code:


$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') {
  $useGiveortake = true;
  // 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 .= ($useGiveortake ? 'AND ' : '') .'duration='.$duration;
}

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
  outputProduct($row);
}

Otherwise it won't work if you supply a $duration but no $giveortake.
the ? in $useGiveortake ? 'AND ' : '' is known as the ternary operator, read about it here.

**hm9** · May 11, 2010 01:33

Thanks ScallioXTX for the clarification and explanation on this. I havent been on my pc yet. i will go through it again and pass you any feedback

Thanks again

**hm9** · May 11, 2010 05:18

Thankx Scallio XTX. It works now. but the destination part (
destination like '%$dest%') is missing from the main query . The destination is not a condition but should be excecuted as part of the main query.

I tried adding it to the Where part in the main query and then added AND to each condition but it generated this error like (Cnnot divide by Zero)

any ideas?
thanks

**ScallioXTX** · May 11, 2010 05:54

Sounds like you messed up the string concatenation (again).
Can you post the complete code?

**hm9** · May 11, 2010 07:39

The only 2 things I added were:

1) destination like '%$dest%' at the end of the main query
2) The AND part in the condition bit

$sql="SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price,buyurl, departureDate from holidays WHERE departureDate = '$depx' and destination like '%$dest%' ";

$sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY';} else { // We need to check for exactly one date $sql .= ' AND departureDate = "'.$depx.'"';}

**ScallioXTX** · May 11, 2010 08:34

1)
Why is "departureDate = '$depx'" back in the main query!? The whole point of my last 10 posts was to get it out of there!
It seems like you're copy/pasting random snippets from my posts into your own script. Please use my complete script to make sure you incorporate all changes and don't miss any.

2)
Did you also change

PHP Code:


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

to

PHP Code:


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

(i.e., remove the ($useGiveortake ? 'AND ' : '') part)

and have you also removed the line:

PHP Code:


$useGiveortake = true;

since you don't need it anymore?

**hm9** · May 11, 2010 11:34

Sorry, I already made thoses changes. i forgot and posted the wrong one on here. I didnt include the departureDate in the query and I did remove the $useGiveortake ? 'AND ' : '') part as well

**hm9** · May 11, 2010 14:02

Ok this is the code now:

$sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like '%$dest%' ';

if ($giveortake == '3' || $giveortake == '5') {

$sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
else {

$sql .= 'departureDate = "'.$depx.'"'; }

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

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

while ($row = mysql_fetch_array($result))
{

outputProduct($row)
;
}

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

and this is the error message:

Warning: Division by zero in D:\inetpub\vhosts\holidays.php on line 71
Query error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND departureDate BETWEEN "2010-05-28" AND "2010-05-28" + INTERVAL 3 DAY' at line 1

Note: I dont get this error message when I remove the destination part in the main query

Thanks

**ScallioXTX** · May 11, 2010 14:24

I will show you the first line of your script in color hightlighting, you should be able to figure out from that what's wrong:

PHP Code:


$sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like '%$dest%' ';

See how the % characters turn green and $dest turns blue? They're not supposed to do that, they're supposed to be red, as they need to be a part of the string.

It has to with double quoted strings, single quoted strings, and string concatenation, which I explained to you in post #29

**hm9** · May 12, 2010 05:08

I know your point: I did the double quotes but still getting the same error message:

$sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "'%$dest%'" ';

**hm9** · May 12, 2010 13:36

I can see anything wrong with the code now. I did exactly as you mentioned. I am still getting that error message. Any ideas?

---------------code---------------------------------------------------
$sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "'%$dest%'" ';

if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {

$sql .= 'departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
else {

$sql .= 'departureDate = "'.$depx.'"'; }

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

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

while ($row = mysql_fetch_array($result))
{

outputProduct($row)
;
}

**ScallioXTX** · May 12, 2010 15:51

Originally Posted by hm9

I did exactly as you mentioned.

No, you did not. If you did, it would work

The problem is in the first line. Let's break it down:

$sql =

Ok, that's a variable assinment, nothing wrong there. Moving along

'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "'

Thats a string, it is delimited by the single quotes I colored red. Nothing wroing with that string, let's move on

%$dest%

Ehm, okay, what? Since this is not part of the string PHP will interpret the % sign as the modulo operator. That can't be right, can it? That's not what you meant! You meant to put it in the string, so why didn't you do so?

So, okay, we will move the % in the string, and we get

'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%'

See, now the % is in the string, all pretty in blue.

So, we still have

$dest%'" ';

Okay, that's wrong again. You cannot follow a string directly by a variable, since that's an allowed construct in PHP. What you need to do is concatenate it, like so:

'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest

See the green part at the end? We concatenated the variable to the string.
Now, to move on

%'" ';

Well, what do you know, there is the modulo operator again! This one also has to go in the string, and then you need to concatenate the last string with the variable proceeding it.

$dest . '%" ';

So, all together now:

'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';

Now, make sure you read this post thorougly, and once you did, read it again! And then get a PHP editor with syntax highlighting like NetBeans, Eclipse, Aptana, Coda, Notepad++, or any other of the free editors out there that make it easy to spot these kind of errors as you type.

**hm9** · May 13, 2010 03:11

Ok. I see your point now. The divide by zero problem is now solved and I dont get that error message anymore, but I still get the the following syntax error in the INTERVAL part:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND departureDate BETWEEN "2010-05-27" AND "2010-05-27" + INTERVAL 3 DAY' at line 1.

$sql .= 'AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
else {

$sql .= 'departureDate = "'.$depx.'"'; }

-----------------------------------------------------

**ScallioXTX** · May 13, 2010 04:31

Can you post the complete code in [php], [/php] tags please?

**hm9** · May 13, 2010 04:55

Ok here is the code:

<php>

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

list($day, $month, $year) = split( '[/]', $dep);
$depx = "$year-$month-$day";
echo $depx ;// to test it works

'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';

if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {

$sql .= ' departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
else {

$sql .= ' departureDate = "'.$depx.'"'; }

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

if($type=='All inclusive' || $type=='Bed and Breakfast') { $sql .= ' AND type='.$type; }

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

while ($row = mysql_fetch_array($result))
{

outputProduct($row)
;
}

</php>

Thanks

**ScallioXTX** · May 13, 2010 09:15

Code:

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

list($day, $month, $year) = split( '[/]', $dep);
$depx = "$year-$month-$day";
echo $depx ;// to test it works


'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';

if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {

$sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
else {

$sql .= ' AND departureDate = "'.$depx.'"'; }



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

if($type=='All inclusive' || $type=='Bed and Breakfast') {
  $sql .= ' AND type="'.$type.'"';
}

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


while ($row = mysql_fetch_array($result))
{

outputProduct($row)
;
}

Changes/additions in red.
And you need to use square brackets when posting code:

[php]
$somevar = 'Some string';
[/php]

**hm9** · May 13, 2010 14:25

Thanks but sorry I am still getting the syntax error message. Its complaining about the Line with the INTERVAL part

PHP Code:




if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {



$sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }

else {



$sql .= ' AND departureDate = "'.$depx.'"'; }

Error message:
Query error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND departureDate = "2010-05-28"' at line 1

I am not sure why as when I removed the dest from the main query, there was no syntax error

Any ideas?
Any idea?

**SpacePhoenix** · May 13, 2010 22:41

Just before the query is run, add this line:

PHP Code:


echo $sql;

Paste the query here

**hm9** · May 14, 2010 05:00

Problem solved. The $sql .= bit was missing from the main query:

So it should be:

PHP Code:




$sql .= 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price, buyurl,  DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';

Thanks again for your help and patience

User Tag List

Thread: Conditions in PHP

Thread Tools

Display

Bookmarks

Bookmarks

Posting Permissions