Related Products

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bizzy
Forum Newbie
Posts: 10
Joined: Mon Apr 16, 2007 1:15 pm

Related Products

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
Bizzy
Forum Newbie
Posts: 10
Joined: Mon Apr 16, 2007 1:15 pm

Post 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]
Post Reply