Is there a way to get each row's value from a database into an array?

Question

Say I have a query like the one below. What would be the best way to put each value into an array if I don't know how many results there will be? Normally I would do this with a loop, but I have no idea how many results there are. Would I need run another query to count the results first?

<CFQUERY name="alllocations" DATASOURCE="#DS#">
    SELECT locationID 
    FROM   tblProjectLocations
    WHERE  projectID = '#ProjectName#'
</CFQUERY>

Answer 1

Depending on what you want to do with the array, you can just refer to the column directly for most array operations, eg:

i = arrayLen(alllocations["locationID"]);

Using that notation will work for most array operations.

Note that this doesn't "create an array", it's simply a matter that a query columns - a coldfusion.sql.QueryColumn object is close enough to a CFML array for CF to be able to convert it to one when an array is needed. Hence the column can be passed to an array function.

What one cannot do is this:

myArray = q["locationID"];

This is because by default CF will treat q["locationID"] as a string if it can, and the string value is what's in the first row of the locationID column in the q query. It's only when an array is actually required will CF convert it to an array instead. This is basically how loose-typing works.

So if you just need to pass your query column to some function that expects an array, you can use the syntax above. If you want to actually put the column into a variable, then you will need to do something like this:

myArray = listToArray(valueList(q.localtionID));

NB: make sure you use <cfqueryparam> on your filter values instead of hard-coding them into your SQL statement.

Answer 2

Since you're only retrieving 1 field value from the query, you could use ValueList() to convert the query results into a comma-delimited list of locationIds, then use listToArray() to change that list into an array.

If you were retrieving multiple field values from the query, then you'd want to loop through the query, copy all the field values from the given row into a struct, and then add that struct to an array using arrayAppend().

(If you're not familiar with these functions, you can look them up in the Adobe docs or on cfquickdocs.com).