Select Array Values?

[AdamB]

Hello,
Im trying to figure out if this is possible, and ive come to my wits end in trying different methods.

Im trying to select a group of products from a database, the problem being that products can be split across more than one subcategory, but still reside in the same main category.

Im using this code to select the categories:

[code]$categories = mysql_query("SELECT categories_id FROM categories WHERE parent_id = $_GET[cPath]");[/code]

and Ive used this code so far to select the products:

[code]$products = mysql_query("SELECT products_id, products_quantity, products_image, products_price FROM products WHERE products_status = 1 AND master_categories_id = $echo_categories[categories_id] ORDER BY rand() LIMIT 10");[/code]

I would like to be able to display all the products across the categories in a random order. (EG. A product from subcategory 1 would be followed by 1 from subcategory 4, then maybe 2 from subcategory 3 etc). So far I have tried using a while() loop to output the records, but obviously because the while loop is only working on one subcategory at a time it will only select the products in that subcategory.

Really what I need to do is be able to select all the products which have a 'master_categories_id' value which exists in the categories array selected earlier. If that makes sense :|

Any help would be grately appreciated thank you!

[fenway]

Well, you could do this with a join or a subquery, though I recommend the former.  Try the following (UNTESTED):

[code]SELECT p.products_id, p.products_quantity, p.products_image, p.products_price FROM products AS p LEFT JOIN categories AS c ON ( c.categories_id =  p.master_categories_id ) WHERE p.products_status = 1 AND c.parent_id = $_GET[cPath] ORDER BY RAND() LIMIT 10[/code]

[AdamB]

Thank you very much for your help. With the starter you gave me (which did work, thanks), a tutorial on three-way left joins, and phpMyAdmin Ive managed to construct the SQL statement I needed:

[code]SELECT p.products_id, p.products_quantity, p.products_image, p.products_price, name.products_name FROM (products AS p LEFT JOIN categories AS c ON c.categories_id = p.master_categories_id) LEFT JOIN products_description AS name ON (name.products_id = p.products_id) WHERE p.products_status = 1 AND c.parent_id = $_GET[cPath] ORDER BY RAND() LIMIT 10[/code]

Thanks very much again! Its much appreciated  :)