SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IF statement in a MySQL Select Command

    Hey,

    For one of my projects I need to change what Categories and Sub Categories are selected in a Table depending on whether or not a Sub Category or difficulty is selected basically I need to do something like this:

    $result = mysql_query(SELECT * FROM table WHERE category='$thecategory');

    ok so that works so far but I need to be able to detect whether or not a Sub Category was selected which I do by passing a variable like Sub Category through the GET function.

    so

    $subCategory = $_GET['subcategory']

    then I would like to update my SQL command like this but don't know how to properly:

    $result = mysql_query(SELECT * FROM table WHERE category='$thecategory' if($subCategory){AND subCategory='$subCategory'});

    I hope that makes sense, basically I need to detect if they've chosen a Sub Category and if they have only select things that are in the Main Category and the Sub Category. Thanks for any help.

    Jordan Garn

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,156
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    You could do something like
    PHP Code:
    $query "SELECT * FROM table WHERE category='" $thecategory "'";
    $subCategory $_GET['subcategory'];//sanitize before using
    if($subCategory){
    $query .= " AND subCategory='" $subCategory "'";
    }
    $result mysql_query($query); 

  3. #3
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, good idea, i'll try and implement that, basically use PHP to generate the string I see

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'd recommend verification before assuming the variable exists.

    PHP Code:
    $query "SELECT * FROM table WHERE category='{$thecategory}'"
    if(
    array_key_exists('subCategory'$GET)){ 
        
    $subCategory mysql_real_escape_string($_GET['subCategory']);
        
    $query .= " AND subCategory='{$subCategory}'"

    $result mysql_query($query); 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    SitePoint Evangelist JordashTalon's Avatar
    Join Date
    Dec 2008
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, good point, makes for cleaner PHP code

  6. #6
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this..

    $query2 = empty($_GET['subcat'])? ' ' : 'AND subcat ={$_GET[subcat]} ' //but clean the $_GET first before appending...

    $query = "SELECT * FROM table WHERE category='$thecategory'.$query2."

    I never really checked it though.. but i tried it before.

  7. #7
    SitePoint Enthusiast jagat_21's Avatar
    Join Date
    Jun 2007
    Location
    Ahmedabad,India
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    You can try this also.

    $result = mysql_query("SELECT * FROM table WHERE category='{$thecategory}' AND IF( '{$subCategory}' <> '' , subCategory = '{$subCategory}','')");

  8. #8
    SitePoint Enthusiast jagat_21's Avatar
    Join Date
    Jun 2007
    Location
    Ahmedabad,India
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another way 2 do the same thing ::

    $result = mysql_query("SELECT * FROM table WHERE category='{$thecategory}' AND IF( '{$subCategory}' IS NOT NULL, subCategory = '{$subCategory}','')");


Tags for this Thread

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
  •