Re: how to show 6 randomly selected featured products?

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

 



Hi,

Joins are fine to use, the problem here is you have them in the wrong order.

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


This is doing:

join products to categories_has_products (using prod_id) -- fine
then join categories_has_products to categories (using cat_id) -- fine
then join categories to prices (using prod_id) --- not fine


You could try

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	prices as pr ON pr.prod_id = p.prod_id
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

and it should work quickly because it does:

join products to prices (using prod_id)
then join prices to categories_has_products (using prod_id)
then join categories_has_products to categories (using cat_id)


Afan Pasalic wrote:
?!?!? Yup. It works! YEAH!!! :) :) :)
And pretty fast too!

Ok. I was suggested to use JOIN. But you DON'T use it?

-afan


On Thu, 2006-02-09 at 13:09 +1100, Chris wrote:

Oops, missed this one before I sent that other email.

I think it's the order of your joins playing up.

LEFT JOIN categories as c ON chp.cat_id = c.cat_id
LEFT JOIN	prices as pr ON pr.prod_id = p.prod_id

That means it will join categories -> prices - not what you want.

Try this query:

SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id,
c.cat_name, c.cat_parent, pr.price
FROM
products p,
categories_has_products chp,
categories c,
prices pr
WHERE
chp.prod_id = p.prod_id
AND chp.cat_id = c.cat_id
AND pr.prod_id = p.prod_id
AND prod_featured = '1'
ORDER BY RAND(), pr.price ASC
LIMIT 6


Afan Pasalic wrote:

The result display is bad (at least on at email). Here is a snapshot:
http://afan.net/bhw/snapshot1.png

-afan


On Wed, 2006-02-08 at 19:39 -0500, Afan Pasalic wrote:


Hm. Have no idea what does it means:

table type possible_keys key key_len ref rows Extra p
ALL
NULL
NULL
   NULL
NULL
   1268
Using
where;
Using
temporary; Using filesort
chp
index
NULL
PRIMARY
      8
NULL
   1741
Using
index
c
eq_ref
PRIMARY
PRIMARY
      4
chp.cat_id
      1

pr
ALL
NULL
NULL
   NULL
NULL
  10231



-afan


On Thu, 2006-02-09 at 09:53 +1100, Chris wrote:


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.





--
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