SQL Statement (help needed)

[matbrummitt]

Hi all,

I'm currently building a content management system which has been going swimmingly, but now i'm stuck because I have to do something i've not dealt with before.

I'm building the search functionality of the site. I've got the basic search in place, but the products on my site have "attributes" attached to them, so that a technical spec can be provided for each product. What I am trying to do, is allow the user to search for a keyword along with various filters in the form of the attributes attached to the product. For example, in the case of the computer gaming site, I might attach the following attributes: Genre, PEGI Age Rating, Platform, Multiplayer?

So a user might come along and say "Show me all product with the keyword "Mario" which are rated 3+ and are multiplayer.

I'll show my database table strucutres below, but first i'll explain where I'm stuck.

I've already got the array containing all search form data, where the keys match any pre-set attribute headings, so i know exactly which attributes I should be filtering against.

My problem, is that when I search for the relevant products by keyword, it returns me all the results, one product per row. However, if i'm to search against a list of say, 5 attributes, which exist each in their own row in another table, how can I do this and pair them up? Bear in mind, I don't want to display attribute values on the search results page, i purely need to pick up all results which match the user's input.

I've looked at subqueries, but i'm not sure if that's what I would need to do?

Hopefully i've explained that well enough?

Below are my table structures.

Thanks

Mat

Table: product_attribute_heading
heading_id (key)
site_id
heading_name
heading_path

Table: product_attribute_value
heading_id
value_id (key)
value

[Stimulus]

Well, for the mario thing, just get all results that are "like mario"

then, use if-statements to narrow it down to only those that are mario and 3+

then, also do multiplayer

[chaos]

For doing everything at the database level rather than filtering at the code level, what you're looking for is the join (SELECT on multiple tables). Might look something like:
SELECT `heading_id`
FROM `product_attribute_heading`, `product_attribute_value`
WHERE `product_attribute_heading`.`heading_id` = `product_attribute_value`.`heading_id`
AND `heading_name` LIKE '%mario%'
AND `value_id` = however you identify some subfield
AND `value` LIKE '%something%'

That gets you a heading ID based on a combination of data from the main table and the subtable, without actually returning any data from the subtable.

Depending on how much data you have and how it's structured, though, doing like Stimulus is saying and filtering at the code level can be a better idea than it sounds, though. Other databases may vary, but MySQL can have a lot of trouble doing big hairy joins on large tables, and it not infrequently winds up being a better idea to let the application handle it, issuing additional separate queries to subtables or whatever.