Page 1 of 1

SQL Search Query help! Logic Needed

Posted: Fri Aug 13, 2010 4:31 pm
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

Re: SQL Search Query help! Logic Needed

Posted: Fri Aug 13, 2010 4:45 pm
by bla5e

Code: Select all

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