Retrieving Array from single column in database

[seanmayhew]

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.

[vgarcia]

Can you show us some source code please?

[seanmayhew]

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>!

[seanmayhew]

sorry my post must have been too long it cut off the rest

[vgarcia]

For this block:

 <%
'--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:

<input type=""checkbox"" name=""categoriesChecked"" value="""& LinkID & """>

Otherwise your generated code may end up like this:

<input type="checkbox" name="categoriesChecked" value=21">

[seanmayhew]

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

[Jmi]

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'"

[seanmayhew]

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

[Jmi]

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

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

[seanmayhew]

Is this what you mean?

<%
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
%>

[Jmi]

yeah ;p
not sure abt the codes but yup tats wat I meant..
will it do??