Re: how to show 6 randomly selected featured products?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux