SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict seanmayhew's Avatar
    Join Date
    Aug 2002
    Posts
    209
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieving Array from single column in database

    I have about 26 checkboxes that place all of my contacts into categories so in my database for contacts i have a field called categories that contains all of the categeories that each contact belongs to seperated by a ",". 1,5,17 etc. I need help getting these values out of the database so that when I user select category 17 they get all of the contacts that containg the 17 in their respective categories field. Any help would be greatly appreciated.

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Can you show us some source code please?

  3. #3
    SitePoint Addict seanmayhew's Avatar
    Join Date
    Aug 2002
    Posts
    209
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the code that creates the checkboxes


    [CODE] <%
    '--Declare variables
    Dim SQLSTR, objRS, arrCategories, DisplayCategory, LinkID, RowCounter, DisplayName, ColCounter, NumRows, NumRecords, Category
    '--Change this constant to tell it how many columns in which to display the table
    Const NumColumns = 3
    '--Open connection to SQL Server database containing category records

    SQLSTR = "SELECT * FROM tblCategory ORDER BY Category"
    '--Open recordset and then use GetRows to move it into the array
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open SQLSTR, oCm, 3, 3
    arrCategories = objRS.getrows
    '--Close and terminate connection and recordset objects
    objRS.close
    set objRS = nothing
    %>
    <table ALIGN="CENTER" BORDER="0" CELLPADDING="1" CELLSPACING="3" width="400">
    <%
    '-- Find out how many records are in the array (Add 1 since array coordinate starts at 0)
    NumRecords = ubound(arrCategories,2) + 1
    '--Calculates how many rows there should be based on the specified number of columns
    '--The last column will always the the short column if there is one
    if NumRecords mod NumColumns = 0 then
    NumRows = NumRecords\NumColumns
    Else
    NumRows = NumRecords\NumColumns + 1
    End if

    '--The outer loop walks down the rows
    for RowCounter = 1 to NumRows

    '--The inner loop steps across the columns
    For ColCounter = 0 to NumColumns-1

    if RowCounter + ColCounter * NumRows <= NumRecords then
    '--Build the display name
    DisplayCategory = arrCategories(1, RowCounter + ColCounter * NumRows-1)
    LinkID = arrCategories(0, RowCounter + ColCounter * NumRows-1)
    %>

    <%
    '--Write out the checkbox with the value set to the Category ID
    response.write "<td><input type=""checkbox"" name=""categoriesChecked"" value="& LinkID & """><font face=""Arial, Helvetica, sans-serif"" size=""1"">"& DisplayCategory & "</font></td>"
    Else
    '--This condition takes care of the case where your last column has fewer rows than the first one
    Response.write "<td>&nbsp;</td>"
    end if
    Next
    response.write "</tr>"
    Next%>
    </table>
    <DIV>!

  4. #4
    SitePoint Addict seanmayhew's Avatar
    Join Date
    Aug 2002
    Posts
    209
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry my post must have been too long it cut off the rest

  5. #5
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    For this block:
    Code:
     <%
    '--Write out the checkbox with the value set to the Category ID
    response.write "<td><input type=""checkbox"" name=""categoriesChecked"" value="& LinkID & """><font face=""Arial, Helvetica, sans-serif"" size=""1"">"& DisplayCategory & "</font></td>"
    Else
    '--This condition takes care of the case where your last column has fewer rows than the first one
    Response.write "<td>&nbsp;</td>"
    end if
    Next
    response.write "</tr>"
    Next%>
    You seem to be missing a quotation. Here's the fix:
    Code:
    <input type=""checkbox"" name=""categoriesChecked"" value="""& LinkID & """>
    Otherwise your generated code may end up like this:
    Code:
    <input type="checkbox" name="categoriesChecked" value=21">

  6. #6
    SitePoint Addict seanmayhew's Avatar
    Join Date
    Aug 2002
    Posts
    209
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My problem though is not the value because I know thats getting into the final html output my problem is how do I get a field out of the database that contains several comma seperated values

  7. #7
    ********* User Jmi's Avatar
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can i confirm something?
    -you got a user tbl and a category tbl
    -each user record has a column field that contains the id of a category (multiple delimited by commas)
    -you wanna select all the users with the catagory which u have selected

    from this, i think u got a many-to-many relationship and you should create another table to hold the userid and the category to break this relationship.

    if I am wrong abt the above,
    you can use a LIKE keyword in ur sql
    "SELECT * From User Where UserCat Like '17'"
    Signature not found.

  8. #8
    SitePoint Addict seanmayhew's Avatar
    Join Date
    Aug 2002
    Posts
    209
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You know I did exactly what you suggested created a join table for categories and userids but this guy I work with kind of insisted I was doing it the wrong way so I took his advice and am kind of confused.

    The only problem with the join table is that when I want to insert a new user I need to not only insert all of the data into the user table but also the userid and all associated categories into the join table. In Access and ASP there is no request_id as there is in PHP & MySQL. Any ideas on how to overcome that besides creating the userid at runtime. Currently the userid is just an auto-incremented column in the user table

  9. #9
    ********* User Jmi's Avatar
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Eh, forgive me if I am wrong but i dun see any problems with inserting the userid and catid into the join table?

    Are you concern abt the new userid (coz its autonumber) which you cant get for your insert statement?

    Usually, I use ADO to add just values rather than excuting and sqlstring. Because I could retrive the id right after I added the new record and use that new userid to generate the sqlstring for the insertion of records into the Join table.

    Eh.. here is wat i mean, codes isnt tat great though dun mind

    Code:
    Dim rsUser
    Dim strSQL
    Dim oConn
    Dim tempId
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open connectstr
    
    Set rsUSer = Server.CreateObject("ADODB.Recordset")
    strSQL = "Select * from [tblUser] where [userId] -1"
    rsUser.Open strSQL, oConn, adOpenStatic, adLockPessimistic
    rsUser.AddNew
    rsUser.Fields("name") = "name"
    rsUser.Update
    
    tempId = rsUser.Fields("userId")
    rsUser.Close
    Set rsUser = nothing
    
    Dim aryCat
    aryCat = Split(Request.Form("categoriesChecked"), ",")
    
    For i = 0 to UBound(aryCat) - 1
    	strSQL = "Insert into [tblUserCat] Values (" & tempId & ", " & aryCat(i) & ")	
    	oConn.Execute(strSQL)
    Next
    
    Set oConn = Nothing
    Signature not found.

  10. #10
    SitePoint Addict seanmayhew's Avatar
    Join Date
    Aug 2002
    Posts
    209
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this what you mean?

    Code:
    <%
    Set commInsert = Server.CreateObject("ADODB.Connection")
    commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data_
               Source=C:\WebSiteDirectory\DatabaseDirectory\YourDB.mdb; Persist_Security Info=False" ' Replace with your OLE DB connection string.
    
    varInsertCommand = "INSERT INTO tableName(TextField, NumericField)_VALUES('ABCDE', 12345);"
    
    commInsert.Execute(varInsertCommand) ' Execute the insert command
    
    Set rsNewID = commInsert.Execute("SELECT @@IDENTITY") ' 
    
    Create a recordset and_SELECT the new Identity
    varNewID = rsNewID(0) ' Store the value of the new identity in variable varNewID
    rsNewID.Close
    Set rsNewID = Nothing
    %>

  11. #11
    ********* User Jmi's Avatar
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah ;p
    not sure abt the codes but yup tats wat I meant..
    will it do??
    Signature not found.


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
  •