SQL Table Fun



  • This is something interesting I came across, and I'm not sure if it's really a WTF but it sure seems like one to me.

    Given a table called "Widgets", it has the following fields and data:

     

    mcid    mc      sc1id   sc1             sku             idProduct   description    smallImageURL                 cat_pg_num
    ---------------------------------------------------------------------------------------------------------------------------
    6 Widgets 154 Foo Widgets ABC-12345 157580 Foo Widget ../itempics/foo_widget.jpg 48

    Several times over (different data, of course). The calling page, in Classic ASP, looks vaguely like this (all formatting left as-is):

    if request("cat")<>"" then
    padd="_2"
    pcat=request("cat")
    mySQL="SELECT max(sc1) as mc, sc1id as mcid from widgets where mcid='"& pcat &"' group by sc1id"
    else
    padd=""
    mySQL="SELECT max(mc) as mc, mcid from widgets group by mcid"
    end if

    There are two pages, widgets.asp and widgets_2.asp. The first one displays either the top level category (mc) or the subcategory (sc1) based on whether or not you clicked a category (e.g. first time you look at the page, it queries for top level categories; you click a link and it queries the same page, passing in the category ID, and finds the sub categories). The second page is forwarded if you click on a subcategory, and queries the same table, this time based on the sub category ID, to get a list of all products for that sub category. The rest of the code on the page is like this, along with some kind of poor man's alternating CSS styles (hardcoded, of course):

    clrctr=1
    clr=""
    do until rstemp.eof
    select case clrctr
    case 1
    clr="red"
    fclr="white"
    case 2
    clr="blue"
    fclr="white"
    case 3
    clr="yellow"
    fclr="black"
    case else
    clr="lime"
    fclr="black"
    clrctr=0
    end select
    response.write "<tr><td bgcolor="&clr&" style='BORDER-RIGHT: black 3px solid;BORDER-TOP: black 3px solid;BORDER-LEFT: black 3px solid;COLOR: black;BORDER-BOTTOM: black 3px solid;'></td>
    <td align=center width=550 bgcolor="&clr&" style='BORDER-RIGHT: black 3px solid;BORDER-TOP: black 3px solid;BORDER-LEFT: black 3px solid;COLOR: black;BORDER-BOTTOM: black 3px solid;'>
    <a href='widget"&padd&".asp?cat=" & rstemp("mcid") & "' style='COLOR: "& fclr &";'>
    <strong>" & rstemp("mc") & "</strong></a></td><td><img src='images\widget_"&clr&".gif'></tr>" &vbnewline&vbnewline

    clrctr=clrctr+1
    rstemp.movenext
    loop

    Now, what I want to know is there any good reason to throw all of this data into a single table and query itself repeatedly based on whether or not there's a querystring, instead of using a View or hell, anything else really, and relate categories to widgets?



  • @ObiWayneKenobi said:

    BORDER-RIGHT: black 3px solid;BORDER-TOP: black 3px solid;BORDER-LEFT: black 3px solid;COLOR: black;BORDER-BOTTOM: black 3px solid;

    IMO that's worth pointing out.



  •  Yeah.  I don't think the original developer knew that you could set them all together.  Although, since the site is half done in Dreamweaver, maybe he just used the WYSIWYG editor and it set them all individually.



  • The first thing that came to mind was the following URL fragment: widgets.asp?cat=%27%3B+drop+table+widgets%3B+--

    I'm pretty sure that's not a good sign...



  • @Quietust said:

    The first thing that came to mind was the following URL fragment: widgets.asp?cat=%27%3B+drop+table+widgets%3B+--

    I'm pretty sure that's not a good sign...

     

    Not at all.  More proof that the guy before me didn't know WTF he was talking about.


Log in to reply