select distinct - use in categories.php search query

[Supertex]

I've noticed that when I put a space in the search box and hit enter, while on categories.php, it returns a list of ALL categories and products in my store, which I find very useful.  However, in cases where I have linked a product to another category, I get duplicate entries.  I would like to limit the number of times a product ID will be returned to 1.

 

I've seen DISTINCT used in queries, but I'm not sure how to structure the query so that only products_id is affected.  For instance, it would be a short list if DISTINCT affected product status in addition to products_id.  

 

Also, I'm not sure how it might affect categories, since they don't have a product id...

 

Haven't found anything that looks like it applies to this on Stack Overflow, but perhaps I can't see the forest for the trees?

 

Mind steering me in the right direction?

[AjitKhodke]

Can you detailed your table structure?

 

 

[♥bruyndoncx]

distinct only returns unique records, so as long as you do not reference categories data or product_to_categories in the selection of fields you won't get duplicates

 

but it is returning categories data in order to populate the cPath to create the edit link I think

[Supertex]

I've come to realize this is less trivial than I first thought.  Perhaps I should leave well enough alone.  Nonetheless, here is my search query:

    if (isset($HTTP_GET_VARS['search'])) {
      $products_query = tep_db_query("select p.products_model, p.products_weight, p.price_in_cart, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order, pg.customers_group_price, s.specials_new_products_price, s.customers_group_id, pi.image, pi.sort_order, p2c.categories_id, pd.products_url,  pas2p.products_attributes_sets_id
from " . TABLE_PRODUCTS . " p join " . TABLE_PRODUCTS_DESCRIPTION . " pd on (p.products_id=pd.products_id) join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on (p.products_id=p2c.products_id) left join " . TABLE_SPECIALS ." s on (s.products_id=p.products_id) left join " . TABLE_PRODUCTS_IMAGES . " pi on (pi.products_id=p.products_id) left join " . TABLE_PRODUCTS_GROUPS . " pg on (pg.products_id=p.products_id) left join " . TABLE_PRODUCTS_ATTRIBUTES_SETS_TO_PRODUCTS . " pas2p on (p.products_id=pas2p.products_id)
  where (pd.products_name like '%" . tep_db_input($search) . "%') 
  or (p.products_model like '%" . tep_db_input($search) . "%') 
  and (pd.language_id = '" . (int)$languages_id . "') 
  and (p.products_id = p2c.products_id)
  and (p.products_id = pd.products_id) 
  and (s.customers_group_id is null or s.customers_group_id=0)
  and (pg.customers_group_id=2 or pg.customers_group_id is null)
  and (pi.sort_order is null or pi.sort_order <2)
  and (pas2p.products_attributes_sets_id is null or pas2p.products_attributes_sets_id is not null) 
  order by $order");
		}

Of course, I've modified the output to include sort-able columns for model, quantity, weight, MAP status, retail and SPPC pricing, retail specials price, attribute sets, a check for product thumbnail, large image, and that the image name matches the product name...and various other things that are handy to have available "at a glance".  

 

As far as table structure... 

products

products_description

products_groups

products_images

products_to_categories

products_attribute_sets_to_products

specials

[MrPhil]

Using GROUP BY products_id might work for you. Look at http://www.mysqltutorial.org/mysql-distinct.aspx.