Jump to content

Only get unique rows


xiao

Recommended Posts

I have this query:

$result = mysql_query("SELECT p.products_id, p.products_model, p.products_price, p.products_image, pd.products_name, pc.categories_id FROM products p, products_description pd, products_to_categories pc WHERE pc.categories_id = 10 AND pd.language_id = 1 AND p.products_price <=50 ORDER BY p.products_price") or die(mysql_error());

 

What it should do:

Get id, model, price, image and name for all products with category 10 that have a price <= 50

 

I'm using it in osCommerce so I had to include the language_id part to only get the english ones.

But now with the above query I get product like 5 or 6 times (might be more), and I don't know how to fix that...

I tried some GROUP BYs, but that seems to give incorrect rows.

Link to comment
https://forums.phpfreaks.com/topic/101265-only-get-unique-rows/
Share on other sites

This seems to work a little better:

$result = mysql_query("SELECT p.products_id, p.products_model, p.products_price, p.products_image, pd.products_name, pc.categories_id FROM products p, products_description pd, products_to_categories pc WHERE pc.categories_id = 10 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_price <=50 GROUP BY pd.products_name ORDER BY p.products_price") or die(mysql_error());

 

But it appears to give products with any category_id

Link to comment
https://forums.phpfreaks.com/topic/101265-only-get-unique-rows/#findComment-517981
Share on other sites

is this an improvement?

$result = mysql_query("SELECT DISTINCT p.products_id, p.products_model, p.products_price, 
        p.products_image, pd.products_name, pc.categories_id 
        FROM products p, products_description pd, products_to_categories pc 
        WHERE pc.categories_id = 10 
            AND pd.language_id = 1 
            AND p.products_price <=50 
        ORDER BY p.products_price") 
        or die(mysql_error());

Link to comment
https://forums.phpfreaks.com/topic/101265-only-get-unique-rows/#findComment-518063
Share on other sites

Whoa!

 

I just noticed you have no join criteria specified

 

$result = mysql_query("SELECT DISTINCT p.products_id, p.products_model, p.products_price, 
        p.products_image, pd.products_name 
        FROM products p
        INNER JOIN products_description pd ON p.products_id = pd.products_id
        WHERE p.categories_id = 10 
            AND pd.language_id = 1 
            AND p.products_price <=50 
        ORDER BY p.products_price") 
        or die(mysql_error());

Link to comment
https://forums.phpfreaks.com/topic/101265-only-get-unique-rows/#findComment-518064
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.