xiao Posted April 15, 2008 Share Posted April 15, 2008 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 More sharing options...
xiao Posted April 15, 2008 Author Share Posted April 15, 2008 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 More sharing options...
Barand Posted April 15, 2008 Share Posted April 15, 2008 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 More sharing options...
Barand Posted April 15, 2008 Share Posted April 15, 2008 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 More sharing options...
xiao Posted April 16, 2008 Author Share Posted April 16, 2008 That looks a lot better indeed, but I need to get the categories_id from products_to_categories. Right now it gives this error: Unknown column 'p.categories_id' in 'where clause' Link to comment https://forums.phpfreaks.com/topic/101265-only-get-unique-rows/#findComment-518414 Share on other sites More sharing options...
fenway Posted April 16, 2008 Share Posted April 16, 2008 Should be pc.categories_id. Link to comment https://forums.phpfreaks.com/topic/101265-only-get-unique-rows/#findComment-518493 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.