Query help while with filters

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

Query help while with filters

Post by bla5e »

I am creating a search tool to filter out templates by Product, and/or by category.
I have an empty table(s) so im having a hard time testing my query but am i doing it the efficient way?

Code: Select all

if (isset($_SESSION['template_pub']) || isset($_SESSION['template_cat'])){
$template_query = "SELECT * FROM templates WHERE id=(SELECT templateid FROM templates_products_associated WHERE productid={$_SESSION['template_pub']}) AND id=(SELECT templateid FROM templates_categories_associated WHERE categoryid={$_SESSION['template_cat']})";
}
when i echo out the query with the post data

Code: Select all

SELECT * FROM templates WHERE id=(SELECT templateid FROM templates_products_associated WHERE productid=5) AND id=(SELECT templatedid FROM templates_categories_associated WHERE categoryid=1)
The tables are empty so i can not tell if its working or not and will not be able to test it for couple weeks.

Table Structures
templates_products_associated

Code: Select all

 templateid | integer | not null
 productid  | integer | not null
"templates_products_associated_productid_fkey" FOREIGN KEY (productid) REFERENCES pubs(productid)
"templates_products_associated_templateid_fkey" FOREIGN KEY (templateid) REFERENCES templates(id)
templates_categories_associated

Code: Select all

templateid | integer | not null
 categoryid | integer | not null
"templates_categories_associated_categoryid_fkey" FOREIGN KEY (categoryid) REFERENCES templates_categories(id)
"templates_categories_associated_templateid_fkey" FOREIGN KEY (templateid) REFERENCES templates(id)
thanks
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: Query help while with filters

Post by bla5e »

Query seems to work i just threw it into my db returned no errors.

Next Question..
i need to limit the query with these 2 rules:
- If $_SESSION['template_pub'] == 'na', not to filter by pub
- if $_SESSION['template_cat'] == 'all', not to filter by category

so i thought it would look something like this, but not sure how to connect the dots.

Code: Select all

if (isset($_SESSION['template_pub']) || isset($_SESSION['template_cat'])){
  $template_query = "SELECT * FROM templates WHERE";
  if ($_SESSION['template_pub'] != 'na'){
    $template_query .= " id=(SELECT templateid FROM templates_products_associated WHERE productid={$_SESSION['template_pub']})";
  }
  if ($_SESSION['template_cat'] != 'all' && $_SESSION['template_pub'] != 'na'){
    $template_query .= " AND";
  }
  if ($_SESSION['template_cat'] != 'all'){
    $template_query .= " id=(SELECT templatedid FROM templates_categories_associated WHERE categoryid={$_SESSION['template_cat']})";
  }
}
am i right?
is there a better/faster/efficient/safer way?
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: Query help while with filters

Post by bla5e »

Code: Select all

if (isset($_SESSION['template_pub']) || isset($_SESSION['template_cat'])){
  //Show list of templates by filter                                                                                                                         
  $template_query = "SELECT * FROM templates WHERE";
  if ($_SESSION['template_pub'] != 'na' && $_SESSION['template_pub'] != ''){
    $template_query .= " id=(SELECT templateid FROM templates_products_associated WHERE productid={$_SESSION['template_pub']})";
  }

  if ( ($_SESSION['template_pub'] != 'na' && $_SESSION['template_pub'] != '') && $_SESSION['template_cat'] !=	'all'){
    $template_query .= ' AND';
  }

  if ($_SESSION['template_cat'] != 'all'){
    $template_query .= " id=(SELECT templateid FROM templates_categories_associated WHERE categoryid={$_SESSION['template_cat']})";
  }
}
is what i am using right now and seems to be working except for 1 problem.
If the user selected NA for the product, and All for the category, there shouldnt be a WHERE clause, but it still is shown (because its in the $template_query string) how can i adjust that to logically know when to use WHERE.
Theres got to be a 'cleaner' way to do this.
Post Reply