criticman Posted November 13, 2005 Share Posted November 13, 2005 [!--sizeo:5--][span style=\"font-size:18pt;line-height:100%\"][!--/sizeo--][!--fonto:Arial Black--][span style=\"font-family:Arial Black\"][!--/fonto--]SOLVED - Thanks[!--fontc--][/span][!--/fontc--][!--sizec--][/span][!--/sizec--] 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. Quote Link to comment https://forums.phpfreaks.com/topic/2853-solved-interesting-mysql-select-query-issue/ Share on other sites More sharing options...
ryanlwh Posted November 14, 2005 Share Posted November 14, 2005 [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM ProductList WHERE cat = '$cat' AND subcat = '$subcat' AND RIGHT(itemNumber,1)='S' ORDER BY brand, prodName, details ASC [!--sql2--][/div][!--sql3--] Quote Link to comment https://forums.phpfreaks.com/topic/2853-solved-interesting-mysql-select-query-issue/#findComment-9628 Share on other sites More sharing options...
criticman Posted November 14, 2005 Author Share Posted November 14, 2005 [!--quoteo(post=318395:date=Nov 14 2005, 12:13 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 14 2005, 12:13 PM) 318395[/snapback][/div][div class=\'quotemain\'][!--quotec--] [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM ProductList WHERE cat = '$cat' AND subcat = '$subcat' AND RIGHT(itemNumber,1)='S' ORDER BY brand, prodName, details ASC [!--sql2--][/div][!--sql3--] Thanks. I posted this on several forums, same response on all. Not all item numbers follow that format, so I will have to restructure the table when I have the time. For now they will have to deal with what I have given them. Quote Link to comment https://forums.phpfreaks.com/topic/2853-solved-interesting-mysql-select-query-issue/#findComment-9650 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.