Jump to content

Select Array Values?


AdamB

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.