Page 1 of 1

Related Products

Posted: Tue May 01, 2007 5:24 am
by Bizzy
Hi,

please imagine the situation. On product page I have 3 related products, that should be most relevant to shown product.

Code: Select all

SELECT * FROM products WHERE category = '$same_category' AND product_id <> '$same_product_id' AND (price BETWEEN $price-10 AND $price+10 OR color LIKE '$color') ORDER BY RAND() DESC LIMIT 3
I would rather show products that have common price and same color. If there is less than three results then show at least same color. And if it's necessary then show only products with common price.

I know possibilities, but no one uses only one mysql_query.

Is there any way to solve it?

Posted: Wed May 02, 2007 2:51 pm
by califdon
Since you need to use conditional logic to obtain the results you want, I don't think there is any way to do that in a single SELECT query. But using several queries, first to determine how many results the each query returns, should be fairly simple.

Posted: Fri May 04, 2007 11:50 am
by Bizzy
califdon wrote:...
Thanks for reply. Main problem is overload of database, if I use this solution. Fortunately I have solution now:

Code: Select all

SELECT DISTINCT * FROM products WHERE category = '$same_category' AND product_id <> '$same_product_id' AND ( (price BETWEEN $price-150 AND $price+150 AND color LIKE '%$color%') OR (color LIKE '%$color%') OR (price BETWEEN $price-150 AND $price+150) ) LIMIT 3
[/syntax]