SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Matching Arrays from a Recordset

    Hello!

    I've been battling with the below for a few days now and cannot reach a simple solution! I'm hoping somebody will be able to point me in the right direction!

    I have two tables in my database: A COLOURS table and a PRODUCT table.

    The COLOURS table goes along the lines of:

    ColourID ColourName
    1 Red
    2 Green
    3 Blue
    4 Yellow

    In my PRODUCTS table I have a product with the colours listed as comma seperated values, such as:

    ProductID ProductName ProductColours
    1 Nice Trousers 2,3,4

    Ultimately I want to bring back a drop down menu in my form that would show:

    2 Green
    3 Blue
    4 Yellow

    for this example.

    I'm not sure if I should be retreiving the records and building the above in the SQL (e.g SELECT ColourName FROM ColourTable WHERE ColourID LIKE ..... etc) or if I can build it in ASP.

    I have succeeded in bringing back the drop-down menu if I used the ColourName only in the array (e.g Red,Green,Yellow), but now I'm stuck with having to use ID numbers instead, so I need to present the names based on the IDs!

    I've searched through Google and this forum and can't find anything usable - will somebody be able to point me in the right direction?

    Thank you for your time.

  2. #2
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would probably execute one SQL to collect the Product information and then execute the second SQL to collect the colour names using an 'IN' clause on the current product's colour names. With the colour names selected, just build your select and option tags.

  3. #3
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    reformatting bbcode tags

    Many thanks, Karl - this seems to be going in the right direction. However, I think I've now hit upon another issue... My ProductColours array (2,3,4) is of course a string, whereas my ColourID is an integer. While the code seems happy, no results are returned. I know this is overblown code (Dreamweaver!) but here it is in it's entirity:

    Code VBNET:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!--#include file="Connections/cms.asp" -->
    <%
    Dim RS_Products
    Dim RS_Products_cmd
    Dim RS_Products_numRows
     
    Set RS_Products_cmd = Server.CreateObject ("ADODB.Command")
    RS_Products_cmd.ActiveConnection = MM_cms_STRING
    RS_Products_cmd.CommandText = "SELECT product_id, product_name, product_colours FROM ufw.tbl_products WHERE product_id = 5" 
    RS_Products_cmd.Prepared = true
     
    Set RS_Products = RS_Products_cmd.Execute
    RS_Products_numRows = 0
    %>
     
     
    <%
    Dim RS_GetColours__varcid
    RS_GetColours__varcid = "2,3"
    If ((RS_Products.Fields.Item("product_colours").Value) <> "") Then 
      RS_GetColours__varcid = (RS_Products.Fields.Item("product_colours").Value)
    End If
    %>
    <%
    Dim RS_GetColours
    Dim RS_GetColours_cmd
    Dim RS_GetColours_numRows
     
    Set RS_GetColours_cmd = Server.CreateObject ("ADODB.Command")
    RS_GetColours_cmd.ActiveConnection = MM_cms_STRING
    RS_GetColours_cmd.CommandText = "SELECT colour_id, colour_name FROM ufw.tbl_colours WHERE colour_id IN (?)" 
    RS_GetColours_cmd.Prepared = true
    RS_GetColours_cmd.Parameters.Append RS_GetColours_cmd.CreateParameter("param1", 5, 1, -1, RS_GetColours__varcid) ' adDouble
     
    Set RS_GetColours = RS_GetColours_cmd.Execute
    RS_GetColours_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
     
    Repeat1__numRows = -1
    Repeat1__index = 0
    RS_GetColours_numRows = RS_GetColours_numRows + Repeat1__numRows
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>
     
    <body>
     
    <% 
    While ((Repeat1__numRows <> 0) AND (NOT RS_GetColours.EOF)) 
    %>
      <%=(RS_GetColours.Fields.Item("colour_name").Value)%><br /><br />
      <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      RS_GetColours.MoveNext()
    Wend
    %>
     
    </body>
    </html>
    <%
    RS_Products.Close()
    Set RS_Products = Nothing
    %>
    <%
    RS_GetColours.Close()
    Set RS_GetColours = Nothing
    %>
    Last edited by Mittineague; Dec 22, 2010 at 12:49.

  4. #4
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,609
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I would even go so far as to suggest normalising the database to create a ProductsColours table ...

    ProductID | ColourID
    1 | 2
    1 | 3
    1 | 4

    ... and removing the ProductColours field from PRODUCTS. Then your (single) SQL command becomes a lot simpler ...

    Code:
    sSQL = "SELECT c.ColourID,c.ColourName FROM Colors c " &_
    "INNER JOIN ProductsColours p ON c.ColourID=p.ColourID " &_
    "WHERE p.ProductID=" & varProductID & " " &_
    "ORDER BY c.ColourID"
    It also means adding/removing colours for products is a much easier task to manage.
    Ian Anderson
    www.siteguru.co.uk

  5. #5
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like it!
    Many thanks for that, siteguru! I'll make some progress with that idea...

  6. #6
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have the ability to change the DB schema, then I would go with siteguru's suggestion, much easier to code and manage a table with the products colors, providing a one to many relationship with your product ID. If not, I would check your 'while conditional' loop.

  7. #7
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for everyone's help - I've got everything running just great!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •