Jump to content


Photo

Select Array Values?


  • Please log in to reply
2 replies to this topic

#1 AdamB

AdamB
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 30 July 2006 - 09:35 AM

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:

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

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

$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");

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!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 July 2006 - 04:38 PM

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

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 AdamB

AdamB
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 30 July 2006 - 05:26 PM

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:

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

Thanks very much again! Its much appreciated  :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users