Jump to content

Archived

This topic is now archived and is closed to further replies.

AdamB

Select Array Values?

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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  :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.