Page 1 of 1

Best Way to Mix 2 Different Query Results

Posted: Wed Aug 22, 2007 5:14 pm
by snowrhythm
i'm making a webpage that's going to have dynamic lists. let's say in my database there are two tables, one called "products" and one called "product_categories".

products has these fields:

product_id
product_price
product_title
product_description
product_category_id

and product_categories has the following fields:

product_category_id
product_category_name

i need to use the product_category_id in the products table to reference the product_category_name in the product_categories table so the category names can be used as headers for the products they've been assigned to (via the category id).
so a simplified version of my list would look like this:

<h2>{product_category_name}<h2>
<h5>product_title</h5>
<h5>product_title</h5>
<h5>product_title</h5>

<h2>{another product_category_name}</h2>
<h5>product_title</h5>
<h5>product_title</h5>
<h5>product_title</h5>
<h5>product_title</h5>
<h5>product_title</h5>
<h5>product_title</h5>

and so on.

i can't figure out what to do with the code. i obviously need some kind of complicated loop (or so i think) or some brilliant SQL that i don't know about.

sorry if this is hard to understand, i tried to make it as clear as possible. thanks in advance for any help, it's much appreciated.

Posted: Wed Aug 22, 2007 5:22 pm
by Christopher

Posted: Thu Aug 23, 2007 7:46 pm
by califdon
The SQL is pretty standard. It would look something like this:

Code: Select all

SELECT * FROM products AS a, products_categories AS b WHERE a.product_category_id = b.product_category ORDER BY product_category, product_title
When you receive the query results, you do indeed need to process it in a loop that checks each row to see if the product_category_id is the same as the previous value (held in a variable). When it is not, you need to insert a header line.