Best Way to Mix 2 Different Query Results

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
User avatar
snowrhythm
Forum Commoner
Posts: 75
Joined: Thu May 04, 2006 1:14 pm
Location: North Bay Area, CA

Best Way to Mix 2 Different Query Results

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
Post Reply