SQL Search Query help! Logic Needed

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

SQL Search Query help! Logic Needed

Post by bla5e »

Code: Select all

if ($_SESSION['articles_pub'] != ''){
    $query = "SELECT id,title,date,category,author FROM {$_SESSION['articles_pub']}.articles";
    if ($_SESSION['articles_cat'] != '' || $_SESSION['articles_date'] != '' || $_SESSION['articles_auth'] != ''){
      $query .= " WHERE";
    }
    if ($_SESSION['articles_cat'] != ''){
      $query .= " category='{$_SESSION['articles_cat']}'";
    }
    if ($_SESSION['articles_date'] != '' && $_SESSION['articles_cat'] != ''){
      $query .= " AND";
    }
    if ($_SESSION['articles_date'] != ''){
      $query .= " date='{$_SESSION['articles_date']}'";
    } 
    //JUST ADDED, NEEDS LOGIC BEFORE IT
    if ($_SESSION['articles_auth'] != ''){
      $query .=	" author='{$_SESSION['articles_auth']}'";
    }
    $query .= " ORDER BY date ASC";

    //Populate Table with Search Results                                                                                                                     
    $sql = pg_query($db, $query);
    while ($row = pg_fetch_assoc($sql)){
      $rows[] = $row;
    }
    $smarty->assign('rows', $rows);
  }
This script works when there isnt an author passed, but I was asked to add the option to filter by author as well. Just having a problem figuring out logic so it properly places the AND's


IF ALL FEILDS ARE FILLED OUT: (WRONG)
SELECT id,title,date,category,author FROM oi.articles WHERE category='xx' AND date='####-##-##' author='xx xx' ORDER BY date ASC

IF JUST AUTHOR IS FILLED OUT: (RIGHT)
SELECT id,title,date,category,author FROM oi.articles WHERE author='xx xx' ORDER BY date ASC

IF AUTHOR AND CATEGORY ARE FILLED OUT: (WRONG)
SELECT id,title,date,category,author FROM oi.articles WHERE category='xxx' author='xx xx' ORDER BY date ASC

IF AUTHOR AND DATE ARE FILLED OUT: (WRONG)
SELECT id,title,date,category,author FROM oi.articles WHERE date='####-##-##' author='xx xxx' ORDER BY date ASC
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: SQL Search Query help! Logic Needed

Post by bla5e »

Code: Select all

if ( (($_SESSION['articles_date'] != '') || ($_SESSION['articles_cat'] != '')) && ($_SESSION['articles_auth'] != '') ){
      $query .=	" AND";
    }
just looks sloppy :-\
Post Reply