Array's

[csandbach]

I beleive what i want to create is an array, but not sure it is so i'm not sure on what to google.

I have a table

custid address enabled
1 bill@test.com 1
1 bob@test.com 1
1 dave@test.com 0


enabled being true false,

i have a form mail script, that you can give it a whole load of addresses seperated by a comma. SO

is there any way i can define a variable from the results of my query i.e. with loop?


i.e. select * from table where cid = '1' and enabled = 1

so i would have 2 results from that, bill and bob @test.com seperated with a comma so it can be defined as my to list in formmail (brainjar)

[Daemonspyre]

Since you are pulling these from your db server, use the recordset.GetRows() feature.

It takes your query and pulls the results into an array automatically.

Here's the usage:

' Assuming you know how to define and open a connection and recordset
'
rs.open "select * from table where cid = '1' and enabled = 1", conn
if not rs.eof then
arrResults = rs.GetRows()
iRowMax = ubound(arrResults,2)
end if
rs.close
'
for x = 0 to iRowMax
'loop through your results using arrResults(0,x)
next
'
Erase arrResults


Just remember that using "SELECT *" is not the best idea. Define which fields you want to see and put them into your SELECT.

[csandbach]

Yeah i can connect to database, i am i wrint in thinking i just need to put

from = arrResults

?

[Daemonspyre]

Unfortunately, with GetRows() or any arrays, you can't do just a simple from = array.

Arrays need to be written out one element at a time.

If you want comma separated values, then use CONCAT() on your SQL string.

SELECT concat(custid,',',address,',',enabled) 'result' FROM your_table WHERE cid = '1' AND enabled = 1

Then do a loop or Array pull based on that.

[Roelf]

as far as i can see, the way to go here is:

query the db with:
select address from table where enabled = 1

then return the results with getrows, this will give the array.

Now you can make this into a commadelimited string with the join function:

Dim strAddresses

strAdresses = Join(arrResults,",")

Which will join all elements in the array, separating them with the provided string, in this case a comma

good luck!

[Spudhead]

... or you could use the Recordset getString() (http://www.devguru.com/technologies/ado/8679.asp) method to do it without all that tedious mucking about with arrays ;)

[Roelf]

... or you could use the Recordset getString() (http://www.devguru.com/technologies/ado/8679.asp) method to do it without all that tedious mucking about with arrays ;)

That is a good one spudhead, i did not know this method. :thumbsup: