So I am working on a project for a company doing an online e-commerce site with a full product catalog.

I had things going just fine until they put some interesting requirements on me. This required restructuring of the MySQL table.

So, I now have an issue. I am working on the "browse products" page. I have no issue displaying categories, then subcategories, then listing all items under them. The issue is there are several item #'s for each set of item.

Items have different sizes, and unfortunately each size has its own item number.

I.E.
ONG76515-XL, ONG76515-L, ONG76515-S, ONG76515-M

What the client now wants (contrary to original request and I do not have time to redo the MySQL tables) is to only display ONE item number on the browse page per set of similar item #'s (basically, show size S and hide sizes M, L, XL until you get to the product detail page).

So, right now my query is thus:

SELECT * FROM ProductList WHERE cat = '$cat' AND subcat = '$subcat' ORDER BY brand, prodName, details ASC

Is there a somewhat efficient way to go about doing this within the SQL query? I would like it to be optimized to the query so that I am not creating tons of overhead by loading 100's of item #s and then hiding all but one per item # set using PHP.

Any help is more than appreciated. I am just stumped and in need of sleep. Project is due Monday AM.

if the structure of the product code is always, refno-size then using this should work

 select fieldlist from table group by left(prod_code,instr(prod_code,'-')) ;

It will use the first one found as the group so the small size "-S" will have to appear first in the row order of the table

or if the -s can be out of sequence

select fieldlist from table where right(prod_code,length(prod_code) - instr(prod_code,'-')) = 's' ;

Might be better.

Yeah, since not every item is -S -M -L or anything like this, I think I am going to just have to restructure the database.