SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 48
  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
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,016
    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

  10. #10
    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.

  11. #11
    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?

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,016
    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,070
    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
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    It should be
    Code:
    if($giveortake=='3') { $sql .=' AND departureDate <='. $dep . 'INTERVAL 3';}
    
    if($giveortake=='5') { $sql .=' AND departureDate <='. $dep . 'INTERVAL 5';}
    You also forgot the keyword "DAY" at the end of INTERVAL <num>.
    So, to sum up:
    PHP Code:
    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. #18
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. but it doesnt return anything. is the format correct?

  19. #19
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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
    PHP Code:
    $result mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error()); 
    instead of just
    PHP Code:
    $result mysql_query($sql); 
    See if that gives you any output.

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

  21. #21
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    1) You should quote $depx

    Code:
    if($giveortake=='3' || $giveortake=='5') { $sql .=' AND departureDate <= "'. $depx .'" + INTERVAL '.$giveortake.' DAY'; }
    (my bad, sorry)
    in the code you have now you're asking "departureDate < 2010-07-05", which MySQL interprets to mean "is departureDate smaller than, or equal to, 1998", because 2010-7-5 is 1998.

    2) If it all possible, don't use MySQL functions in your WHERE clause, because MySQL has to apply that function to each and every record before it can begin to answer the query, i.e., using a function in the WHERE clause forces a full table scan, which is a bad thing.
    So, you should change
    Code:
    where DATE_FORMAT(departureDate, '&#37;d/%m/%Y') = '$dep'
    to

    Code:
    where departureDate = '$depx'
    (and of course make sure $depx is defined at that point).

    3) The query you're creating doesn't make any sense.
    If I were to input "07/05/2010" as date, and "5" as giveortake, you would have the following in your WHERE clause:

    Code:
    departureDate = '2010-05-07' AND departureDate <= '2010-05-07' + INTERVAL 5 DAY
    Since you're already asking every record where date='2010-05-07', adding departureDate <= '2010-05-07' + INTERVAL 5 DAYS doesn't add anything.
    I think you should look at BETWEEN.
    That is, if a user enters a giverotake, don't use the date=<user-input>, but use a BETWEEN statement instead.
    Oh, and you'll also need CURDATE().

  22. #22
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I see your first point. I did change it in the primary query to state; where departureDate = '$depx'

    in the conditionusing the code below, it is returning results but only that matches the primary query.


    I am not sure how to use the between option as not sure how to implement in the conditions bit and syntax etc, so just trying to get this working.

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


    what do you think? could it be the syntax or format?

    Thanks

  23. #23
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Please re-read my previous post:

    if a user enters a giverotake, then don't use the date=<user-input>, but (else) use a BETWEEN statement.

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

    Unhappy

    ok but I am not sure how to use between in this scenario. Would it be something like this?

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

  25. #25
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hm9 View Post
    ok but I am not sure how to use between in this scenario. Would it be something like this?

    if($giveortake=='3') { $sql .=' AND departureDate between "'$depx'" and "'$depx'" + 3;}
    You still need the INTERVAL 3 DAY, and it needs to be INSIDE the string ...

    'some text in a string' + 3 = 3


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
  •