SitePoint Sponsor

User Tag List

Results 1 to 25 of 48

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditions in PHP

    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. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The usual approach is to build a suitable query based on the conditions provided from a base query, like so...

    PHP Code:
    <?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);
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    <?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 = %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
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any idea why i am getting the error message?
    is the structure of the query code? It works without the condition

    thanks

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You're doing a few things wrong...

    In your IF condition, you're not actually comparing ( == OR === ) 7 to $duration, you're 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 [php]mysql_error[/fphp]().
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry

    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. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hm9 View Post
    Any idea?
    Yup, *read* my last post.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  11. #11
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Add this line right after the mysql_query line.
    PHP Code:
    echo mysql_errno($link) . ": " mysql_error($link) . "\n"
    What error is return by mysql?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Just before the second query is run add this line so it reads:

    PHP Code:
    echo "<p>The SQL query being run is: $sql</p>";
    $result mysql_query($SQL); 
    What (if anything) is echoed as the query?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #14
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    PHP Code:
    // 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);


  15. #15
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks scallioXtX. Problem solved :-)

    Cheers for the reponse everyone

  16. #16
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #17
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Just before the query is run, add this line:

    PHP Code:
    echo $sql
    Paste the query here
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  18. #18
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •