how can i do this?? random and joins?

[acidglitter]

where i'm working, we have three tables... one for products, one for categories and one for the groups the categories fall under. if a product is new, in the ifnew field it will have a '1'. there will probably always be at least 10 new products in each category group. what i want to do is randomly show three new products for each category group.

 

 

this is what i have so far...

 

SELECT p.name, p.category_id, c.group_id, g.name AS 'group'
FROM products AS p
INNER JOIN categories AS c ON
	p.category_id = c.category_id
INNER JOIN categorygroups AS g ON
	c.group_id = g.group_id
WHERE p.ifnew='1'
ORDER BY c.group_id ASC

 

its showing EVERY new product in each group.... how can i change it to only randomly show three new products from each group?

[fenway]

You may want to read this... but basically, you need to generate a table of the 3 new products per group first, and then join it back.

[acidglitter]

thanks. i'm still in the middle of reading that, but do you think it would just be better to do something like

 

$query=mysql_query("select name, group_id from category groups order by group_id asc");
while($group=mysql_fetch_array($query)){
$products=mysql_query("select p.name from products as p
	inner join categories as c on p.category_id = c.category_id
	where c.group_id='{$group['group_id']}'");

while($product=mysql_fetch_array($products)){
	// display products
}
}

 

 

that would be 4 queries instead of one though

[acidglitter]

... but basically, you need to generate a table of the 3 new products per group first, and then join it back.

 

one of the examples from that page is

(select * from fruits where type = 'apple' order by price limit 2)

union all

(select * from fruits where type = 'orange' order by price limit 2)

union all

(select * from fruits where type = 'pear' order by price limit 2)

union all

(select * from fruits where type = 'cherry' order by price limit 2)

 

is that what you meant? or did you mean actually making a separate table for the new products?

[fenway]

Well, yes, if you retrieve the list if unique product IDs first, that would work with a RAND() and LIMIT 3.

 

The other way would require sorting the product table, and then only taking 3 of each.