Re: how to show 6 randomly selected featured products?
| From: | Chris | Date: | Wed, 08 Feb 2006 22:53:02 +0000 |
| Subject: | Re: how to show 6 randomly selected featured products? | ||
| References: | 1 2 3 4 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-230130@lists.php.net to get a copy of this message | ||
Hi,
Grab that query and run it through mysql with 'explain', so:
explain SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name, c.cat_parent, pr.price
FROM products as p
LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id
LEFT JOIN categories as c ON chp.cat_id = c.cat_id
LEFT JOIN prices as pr ON pr.prod_id = p.prod_id
WHERE prod_featured = '1'
ORDER BY RAND(), pr.price ASC
LIMIT 6
That will show you whether you are using the database indexes or not.
If you can't work it out then post the explain output.
afan@afan.net wrote:
I'm still in trouble with this problem :(
Solution:
$query = my_query("
SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name,
c.cat_parent, pr.price
FROM products as p
LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id
LEFT JOIN categories as c ON chp.cat_id = c.cat_id
LEFT JOIN prices as pr ON pr.prod_id = p.prod_id
WHERE prod_featured = '1'
ORDER BY RAND(), pr.price ASC
LIMIT 6
", 0);
was working fine but KILLLING SLOW!!!
Now, I'm trying this way:
$query = my_query("
SELECT prod_id
FROM products
WHERE prod_featured = '1'
ORDER BY RAND() LIMIT 6
", 0);
while($result = mysql_fetch_array($query))
{
$FEATURED_PRODUCTS_IDS[] = $result['prod_id'];
}
$query = my_query("
SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name,
c.cat_parent, pr.price
FROM products as p
LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id
LEFT JOIN categories as c ON chp.cat_id = c.cat_id
LEFT JOIN prices as pr ON pr.prod_id = p.prod_id
WHERE
p.prod_id = ". $FEATURED_PRODUCTS_IDS[0] ." OR
p.prod_id = ". $FEATURED_PRODUCTS_IDS[1] ." OR
p.prod_id = ". $FEATURED_PRODUCTS_IDS[2] ." OR
p.prod_id = ". $FEATURED_PRODUCTS_IDS[3] ." OR
p.prod_id = ". $FEATURED_PRODUCTS_IDS[4] ." OR
p.prod_id = ". $FEATURED_PRODUCTS_IDS[5] ."
ORDER BY pr.price ASC
", 0);
But, since I have more than one price for product I'm getting 6 products
but with all avaliable prices. And I need ONLY the lowest price.
Any idea?
Thanks for any help.
-afan