Jump to content


Photo

** SOLVED ** Interesting MySQL SELECT query issue


  • Please log in to reply
2 replies to this topic

#1 criticman

criticman
  • New Members
  • Pip
  • Newbie
  • 5 posts
  • LocationAtlanta, GA

Posted 13 November 2005 - 07:57 AM

[!--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.
Criticman [A+, MCP, Member: HWG, IWA, ACM, NAAP]
Rackspace Solution Partner
10+ Years Professional Experience

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 14 November 2005 - 05:13 PM

[!--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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 criticman

criticman
  • New Members
  • Pip
  • Newbie
  • 5 posts
  • LocationAtlanta, GA

Posted 14 November 2005 - 11:42 PM

[!--quoteo(post=318395:date=Nov 14 2005, 12:13 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 14 2005, 12:13 PM) View Post[/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--]
[/quote]

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.
Criticman [A+, MCP, Member: HWG, IWA, ACM, NAAP]
Rackspace Solution Partner
10+ Years Professional Experience




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users