Conditions in PHP

PHP
1

I have a webpage in PHP that queries the database in mysql. There many conditions that I need to check for when the query is submitted. for instance, users will have to select from many options like: if number of days they want to stay is 3 then show deals for 3 days and 7 show deals for 7 days etc in addition, If they slecect give or take 3 or 5 days I have to show which deals are within that period and also which star hotels they want etc. So there are so many options that users can select. At the moment my page just shows the deals within the exact period of time as I havent implemented the other options yet… which is the best solution to query the database with all these conditions.

$result = mysql_query("SELECT *
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from deals where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ and destination like ‘%$dest%’ ");

if (!mysql_num_rows($result)) { echo “Sorry, we didnt find the exact match but We found alternative options for you”;

Thanks in advance

2

The usual approach is to build a suitable query based on the conditions provided from a base query, like so…


<?php
$sql = 'SELECT foo, bar FROM table WHERE 1 = 1';

if(FOO_CONDITION){
    $sql .= ' AND WHERE foo = 4';
}

if(BAR_CONDITION){
    $sql .= ' AND WHERE bar = 5';
}

$result = mysql_query($sql);
?>

:slight_smile:

3

Thanks Anthony.

I am working on it. the base query works, but the conditions are not rendered. It just displays what ever is in the base query.

May be the order is not right or sth else?

$result = mysql_query("SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from holidays where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ and destination like ‘%$dest%’ ");
//end of output exact match

if ($result)
{
while ($row = mysql_fetch_array($result))
{
if($duration=‘7’){ $result .= ’ AND WHERE duration= 7’;

outputProduct($row);

}

}
}

4

You’re filtering your results after executing your query, you should be letting your database do this filtering.

Collect Conditions -> Build SQL -> Execute -> Display

Currently, you’re attempting to…

Execute -> Apply Conditions -> Display

Compare it to this. :slight_smile:


<?php
$conditions = array(
    'duration'      => 7,
    'onboard meals' => false,
    'location'      => 'tel aviv'
    'room only'     => true
);

$sql = 'SELECT foo, bar FROM table WHERE 1 = 1';

if(true === isset($conditions['duration'])){
    $sql .= sprintf(
        " AND duration = &#37;d",
        $conditions['duration']
    );
}

if(true === isset($conditions['onboard meals'])){
    $sql .= sprintf(
        " AND onboard_meals = %s",
        (bool)$conditions['onboard meals'] ? 'true' : 'false'
    );
}

if(true === isset($conditions['location'])){
    $sql .= sprintf(
        " AND location = '%s'",
        mysql_real_escape_string(
            $conditions['location']
        )
    );
}

if(true === isset($conditions['room only'])){
    $sql .= sprintf(
        " AND room_only = %s",
        (bool)$conditions['room only'] ? 'true' : 'false'
    );
}

$result = mysql_query($sql);

while($record = mysql_fetch_assoc($result)){
    #display record
}
?>
5

Ok thanks for the example. I applied the example to my code , but i got this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

if($duration=‘7’){ $sql .= ’ AND WHERE duration= 7’;}

$result = mysql_query($sql);

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

outputProduct($row)
;
}

6

any idea why i am getting the error message?
is the structure of the query code? It works without the condition

thanks

7

You’re doing a few things wrong…

In your IF condition, you’re not actually comparing ( == OR === ) 7 to $duration, you’re [URL=“http://www.php.net/manual/en/language.operators.assignment.php”]assigning.

Secondly, I suspect your SQL now has multiple WHERE conditions which isn’t as intended.

‘echo out’ your $sql variable to see what it contains prior to executing it, if you can’t see anything out of the norm execute it in a non-php environment.

Say, mysql cli or phpmyadmin.

After you’ve executed the query within php, you can check why it failed by looking at

mysql_error[/fphp]().
8

Thanks for the advice. Ok, the first query excecutes without a problem and the code works ok but its only when I add the conditions that i get the problem. I amended the code as advised:
-------------------------CODE-----------------------------------------------
$result = mysql_query("SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price,buyurl,
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from holidays where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ and destination like ‘%$dest%’ ");
//end of output exact match

if($duration === 7)
{
$SQL .= " AND duration = ‘7’“;
}
elseif($duration === 3)
{
$SQL .= " AND duration = ‘3’”;
}

$result = mysql_query($SQL);

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

outputProduct($row)
;
}

-------------------------END----------------------------------------------------------------------------

There is an error which states: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in.

This refers to this line of code:
while ($row = mysql_fetch_array($result))

So not sure why it executes fine without the condition bit but when I add the condition bit, it complains about the line of code.

Any idea?

Thanks

9

Add this line right after the mysql_query line.

echo mysql_errno($link) . ": " . mysql_error($link) . "\
";

What error is return by mysql?

10

Yup, read my last post. :rolleyes:

11

I put the error code check and when i excecute it the following message is returned:

Query was empty

So is the syntax not correct or something?

12

Just before the second query is run add this line so it reads:


echo "<p>The SQL query being run is: $sql</p>";
$result = mysql_query($SQL);

What (if anything) is echoed as the query?

13

I am making progress with this. Its better now as I dont have the error message anymore and I can see the results but there is one thing here:

If the user select 3 days for instance, they will still see the other records for 7 days as well

I only want the users to see the option they selected. for instance, I have three identical entries. 2 has seven days and one with 3 days in their duration variable so when a user selects 3, I dont want them to view the 7 days option.

Is there something else that needs modifying or adding in the code?

----------------this is the modified code----------------------------

$sql = "SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from holidays where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ and destination like ‘%$dest%’ ";
//end of output exact match

if($duration==‘7’){ $sql .= ’ AND WHERE duration= 7’;}

if($duration==‘3’){ $sql .= ’ AND WHERE duration= 3’;}

$result = mysql_query($sql);

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

outputProduct($row)
;
}
----------------------end-------------------------------------

cheers

14 

// Protect $dep and $dest from SQL Injection
// See http://en.wikipedia.org/wiki/SQL_Injection
$dep = mysql_real_escape_string($dep);
$dest = mysql_real_escape_string($dest);

// 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 DATE_FORMAT(departureDate, '%d/%m/%Y') = '$dep' AND destination LIKE '%$dest%' ";
//end of output exact match

// "AND WHERE" is incorrect, just make it "AND"
// And replace the two lines with one
if($duration=='7' || $duration=='3') {
  $sql .= ' AND duration='.$duration;
}

/*
* OR:
* $validDurations = array('3','7');
* if (in_array($duration, $validDurations)) {
*  $sql .= ' AND duration='.$duration;
* }
*/
$result = mysql_query($sql);

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

:slight_smile:

15

Thanks scallioXtX. Problem solved :slight_smile:

Cheers for the reponse everyone

16

Just one thing i forgot to mention. I am trying to implement the same conditions to intervals so that if user select a 3 interval, the query should return results within extra 3 days and so on. I got the logic to work but keep getting syntax error. can you spot if there is anything wrong with the syntax or something else

if($giveortake==‘3’) { $sql .=’ AND departureDate <='. $dep + INTERVAL ‘3’;}

if($giveortake==‘5’) { $sql .=’ AND departureDate <='. $dep + INTERVAL ‘5’;}

The error message i get is:

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in

Thanks again

17

It should be


if($giveortake=='3') { $sql .=' AND departureDate <='. $dep [COLOR="Red"]. '[/COLOR]INTERVAL 3';}

if($giveortake=='5') { $sql .=' AND departureDate <='. $dep [COLOR="Red"]. '[/COLOR]INTERVAL 5';}

You also forgot the keyword “DAY” at the end of INTERVAL <num>.
So, to sum up:


if($giveortake=='3' || $giveortake=='5') {
  $sql .=' AND departureDate <='. $dep .' + INTERVAL '.$giveortake.' DAY';
}

This kind of error suggests to me that you’re not using an editor with PHP syntax highlighting. If you’re not, I highly suggest you get one, as it makes it easy to spot these kind of errors.

18

Thanks. but it doesnt return anything. is the format correct?

19

Yes the format is correct, are you sure you made it DAY in your query, and not DAYS.
Weird as it may sound, it really is DAY.

Also, try


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

instead of just


$result = mysql_query($sql);

See if that gives you any output.

20

Yes its definitely DAY. I think I know why it may not be showing anything: The departureDate in mysql table is in this format 2010-05-07 and the variable $dep has this format 07/05/2010 so I thought I would convert the dep$ to the same format as mysql 07/05/2010 and I come up with something like this:

SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price,buyurl,
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from holidays where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ and destination like ‘%$dest%’ ";
//end of output exact match

list($day, $month, $year) = split( ‘[/]’, $dep);
$depx = “$year-$month-$day”;
echo $depx ;// to test it works

if($giveortake==‘3’ || $giveortake==‘5’) { $sql .=’ AND departureDate <=‘. $depx .’ + INTERVAL ‘.$giveortake.’ DAY’; }

But it still doesnt display anything. I can echo the variable $depx after it has been converted to mysql format correctly but the condition doesnt display anyhting.

Not sure why its not doing it?

Cheers