SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    syntax for variations on a query

    I'm looking for the syntax to use when I want to run a query with various WHERE clauses depending on the setting of a the variable $show (F means Future, P means Past, A means All).

    In the following query,
    when $show =="F" I want to run the query with:
    PHP Code:
    AND startdate >= now() 
    when $show =="P" I want to run the query with:
    PHP Code:
    AND startdate <= now() 
    when $show =="A" I want to run the query without startdate in the WHERE clause. What is the most efficient way of coding this? I have seen code with variations somehow added in segments. However, I can't seem to find an example now. Here's my existing query:
    PHP Code:
    $sql_list "
                    SELECT
                        program_id, 
                        title,
                        descrip_prog,
                        startdate, 
                        enddate
                    FROM 
                        programs 
                    WHERE
                        site_id = ?  
                        AND cat_id = 
    $cat_id
                        AND visible ='Y' 
                        AND startdate >= now()
                    ORDER BY
                        order_prog,
                        startdate"

    Paul C.
    ClickBasics
    http://www.clickbasics.com

  2. #2
    SitePoint Addict wibble wobble's Avatar
    Join Date
    Dec 2008
    Posts
    242
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $sql_list = "
                    SELECT
                        program_id, 
                        title,
                        descrip_prog,
                        startdate, 
                        enddate
                    FROM 
                        programs 
                    WHERE
                        site_id = ?  
                        AND cat_id = $cat_id
                        AND visible ='Y'"
                     . ($show == 'F' ? 'AND startdate >= now()' :
                         ($show == 'P' ? 'AND startdate <= now()' : ''))
                     . "ORDER BY
                        order_prog,
                        startdate";
    Not tested for typos.
    Find freelance jobs from all the major sites in one place:
    on twitter / on the web / twitter rss feed

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by paulcj2 View Post
    when $show =="A" I want to run the query without startdate in the WHERE clause. What is the most efficient way of coding this?
    that's easy -- just omit it from the WHERE clause

    like this --
    Code:
     
                    WHERE
                        site_id = ?  
                        AND cat_id = $cat_id
                        AND visible ='Y'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    $sql_list "
                    SELECT
                        program_id, 
                        title,
                        descrip_prog,
                        startdate, 
                        enddate
                    FROM 
                        programs 
                    WHERE
                        site_id = ?  
                        AND cat_id = 
    $cat_id
                        AND visible ='Y' 
                        %s
                    ORDER BY
                        order_prog,
                        startdate"

    $where '';
    if (
    $show == "F"$where 'AND startdate >= now()';
    if (
    $show == "P"$where 'AND startdate <= now()';
    $query sprintf($query$where); 

  5. #5
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I appreciate the suggestons:

    wibble_wobble: code produces the message:
    Undefined variable: show
    r937:
    just omit it from the WHERE clause
    Yes. And I need to handle the other cases.

    guido2004: code produces the messages:
    Undefined variable: show
    and
    Unknown column 's' in 'where clause'
    SELECT program_id, title, descrip_prog, startdate, enddate FROM programs WHERE site_id = '170' AND cat_id = 31 AND visible ='Y' %s ORDER BY order_prog, startdate
    I think we're getting close. Clues? Suggestions?
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to define $show. You cannot expect to use a variable that doesn't exist.

  7. #7
    SitePoint Enthusiast VideoWhisper's Avatar
    Join Date
    Dec 2008
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code PHP:
    switch ($show)
    {
    case "F":
    $condition="AND startdate >= now()";
    break;
    case "P":
    $condition="AND startdate < now()";
    break;
    }
     
    $sql_list = "
                    SELECT
                        program_id, 
                        title,
                        descrip_prog,
                        startdate, 
                        enddate
                    FROM 
                        programs 
                    WHERE
                        site_id = ?  
                        AND cat_id = $cat_id
                        AND visible ='Y' 
                    $condition
                    ORDER BY
                        order_prog,
                        startdate";

  8. #8
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to get the value of $show out of the query, like $visible.
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql_list "
                    SELECT
                        program_id, 
                        title,
                        descrip_prog,
                        startdate, 
                        enddate
                    FROM 
                        programs 
                    WHERE
                        site_id = ?  
                        AND cat_id = 
    $cat_id
                        AND visible ='Y' 
                        AND (
                                  (show = 'F' AND startdate >= now())
                                  OR
                                  (show = 'P' AND startdate <= now())
                                  OR
                                  (show = 'A')
                             )
                    ORDER BY
                        order_prog,
                        startdate"


  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in general, the problem with embedding conditional logic into the SQL is that it could detrimentally affect performance

    the cleaner the SQL, the better your chances that it will fly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Re: crmalibu solution.
    Turns out "show" is a MySQL reserved word. So I tried your code using "fpa" (future, past, all) in place of "show", changing it both in the script and the db table. That revision produced the error message:
    Unknown column 'fpa' in 'where clause'
    Then I tried "show_fpa," which produced the same error message, which in full is:
    Unknown column 'show_fpa' in 'where clause'
    SELECT program_id, title, descrip_prog, startdate, enddate FROM programs WHERE site_id = '170' AND cat_id = 31 AND visible ='Y' AND ( (show_fpa = 'F' AND startdate >= now()) OR (show_fpa = 'P' AND startdate <= now()) OR (show_fpa = 'A') ) ORDER BY order_prog, startdate
    So perhaps I'm running up against what r937 suggested:
    embedding conditional logic into the SQL ... could detrimentally affect performance
    Suggestions? Thoughts? Alternatives?
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  12. #12
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wait! I was querying the wrong table! Stay tuned.
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  13. #13
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got the query to work with conditions show_fpa= 'F', 'P' & 'A' using wibble_wobble's code:
    PHP Code:
                    //wibble-wobble
                    
    $sql_list "
                    SELECT
                        program_id, 
                        title,
                        descrip_prog,
                        startdate, 
                        enddate
                    FROM 
                        programs 
                    WHERE
                        site_id = ?  
                        AND cat_id = 
    $cat_id
                        AND visible ='Y'"
                     
    . ($show_fpa == 'F' 'AND startdate >= now()' :
                         (
    $show_fpa == 'P' 'AND startdate <= now()' ''))
                     . 
    "ORDER BY
                        order_prog,
                        startdate"

    However, when I set visible to 'N', the script throws errors:
    http://clickbasicsdemo.com/cat_prog/32

    So now it looks like I need to develop the query to check if visible is set to 'N' as well as 'Y' in addition to $show_fpa set to 'F' as well as 'P'.
    Yikes! I guess I need to set show_fpa as sub-conditions of visible set to N and Y. Is this possible?
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  14. #14
    SitePoint Addict wibble wobble's Avatar
    Join Date
    Dec 2008
    Posts
    242
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What exactly should visible be, and what do you want to happen with it?

    Also, this might be a bit easier to edit:
    Code:
    $where_clasues = array(
      'F' => 'AND startdate >= now()',
      'P' => 'AND startdate <= now()',
      'A' => '',
    );
    
    $where_clause = (isset($where_clasues[$show_fpa]) ? $where_clasues[$show_fpa] : '');
    
    $query = "SELECT    program_id,
                        title,
                        descrip_prog,
                        startdate,
                        enddate
              FROM      programs
              WHERE     site_id = ?
              AND       cat_id = {$cat_id}
              AND       visible ='Y'
              {$where_clause}
              ORDER BY  order_prog,
                        startdate";
    Find freelance jobs from all the major sites in one place:
    on twitter / on the web / twitter rss feed

  15. #15
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Re:
    What _exactly_ should visible be, and what do you want to happen with it?
    WHERE visible can be either 'Y' or 'N'. It is used to either display the program category as part of the navigation, or not.
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  16. #16
    SitePoint Addict wibble wobble's Avatar
    Join Date
    Dec 2008
    Posts
    242
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So can you just change "AND visible ='Y'" to "AND visible = '$visible' and have $visible as a variable 'Y'/'N'?
    Find freelance jobs from all the major sites in one place:
    on twitter / on the web / twitter rss feed

  17. #17
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So can you just change "AND visible ='Y'" to "AND visible = '$visible' and have $visible as a variable 'Y'/'N'
    Thanks. That works great!
    Paul C.
    ClickBasics
    http://www.clickbasics.com


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
  •