<CF_QueryToArray>

Takes a Query--which could be the results of a CFQUERY or any other CF tag that returns a query result set, like CFPOP or CFLDAP-- and returns it as a two-dimensional array. Optionally, the columns and rows can be transposed (pivoted) as the array is created.

Parameters

QUERY Required. The name of the query you want to convert into an Array.

ARRAY Required. The name of the new array to be created. The first dimension of the array will represent the columns of the original query, and the second dimension will represent the rows. So, after this tag executes, if ARRAY="MyArray", then #MyArray[3][1]# will return the value in the first row of the third column of QUERY.

ADDCOLUMNNAMES Optional. Yes or No. If ADDCOLUMNNAMES="Yes", then the column names from the query will be used as the first the first row of the array (see example 1). The array will thus have one more "row" than the query did. Default is No.1

TRANSPOSE Optional. Yes or No. If TRANSPOSE="Yes", then the rows and columns will be "pivoted" in the new Array. Each "row" in the array will represent a column from the query, and each "column" in the array will represent a row from the query (see example 2). Default is No.

USECFX Optional. Yes or No. Highly recommended to be set to yes at the time of this writing (4/98).

If USECFX="Yes", then my CFX_QueryColumns tag will be used to obtain the list of column names from QUERY that this tag needs internally. This will ensure that the "columns" of the new array are in the order in which you would normally expect. Obviously, the CFX tag must be installed on your server if USECFX="Yes", or an error message will appear.

If USECFX="No" or is ommitted, then CF's built-in ColumnList variable will be used to obtain the list of query columns. This means that the order of the "columns" in the new array will be in the order that is reflected by the ColumnList variable, which is very often not the same order as you would expect. For instance, if the query comes from a CFQUERY tag that executes SQL like
SELECT FirstName, LastName, Company FROM Customers
then #QueryName.ColumnList# will often be something like LastName,Company,FirstName, rather than FirstName,LastName,Company. This may cause you to need to do some extra work when working with the resulting array, since #ArrayName[1][2]# would return the LastName from the second row of the query, rather than the FirstName as you might expect. In order to get the first name reliably, you would need to use something like #ListFindNoCase(QueryName.ColumnList, "FirstName")# to find which position in ColumnList that FirstName happens to be at, and use that number instead of the "1" in the above example.

Example
These examples assume that a CFQUERY named MyQuery has been run, with results that look like this:

<CFQUERY NAME="GetData" DATASOURCE="A2Z"> SELECT * FROM Customers </CFQUERY>

CUSTOMERID	COMPANY	FIRSTNAME	MIDDLEINIT	LASTNAME	ADDRESS1	ADDRESS2	CITY	STATE	ZIP	PHONE	EMAIL	CUSTOMERSINCE
1	ABC Company	Arnold	B	Cooper	123 West 1st Street	1st Floor	Anywhere	NY	00001	212 555 1212	acooper@abc.com	1996-07-16 00:00:00
2	DEF Company	Diana	E	Fontaine	456 East 2nd Street	Suite 1200	Anyplace	NY	00002	718 555 1212	dfontaine@def.com	1995-05-05 00:00:00

Example 1

<CF_QueryToArray QUERY="MyQuery" ARRAY="MyNewArray" USECFX="Yes" ADDCOLUMNNAMES="Yes"> This would create an array which, if you could "see" it visually, would look like the following. The array has three "rows".

MyNewArray[1][1]
CUSTOMERID

MyNewArray[2][1]
COMPANY

MyNewArray[3][1]
FIRSTNAME

MyNewArray[4][1]
MIDDLEINIT

MyNewArray[5][1]
LASTNAME

MyNewArray[6][1]
ADDRESS1

MyNewArray[7][1]
ADDRESS2

MyNewArray[8][1]
CITY

MyNewArray[9][1]
STATE

MyNewArray[10][1]
ZIP

MyNewArray[11][1]
PHONE

MyNewArray[12][1]
EMAIL

MyNewArray[13][1]
CUSTOMERSINCE

MyNewArray[1][2]
1

MyNewArray[2][2]
ABC Company

MyNewArray[3][2]
Arnold

MyNewArray[4][2]
B

MyNewArray[5][2]
Cooper

MyNewArray[6][2]
123 West 1st Street

MyNewArray[7][2]
1st Floor

MyNewArray[8][2]
Anywhere

MyNewArray[9][2]
NY

MyNewArray[10][2]
00001

MyNewArray[11][2]
212 555 1212

MyNewArray[12][2]
acooper@abc.com

MyNewArray[13][2]
1996-07-16 00:00:00

MyNewArray[1][3]
2

MyNewArray[2][3]
DEF Company

MyNewArray[3][3]
Diana

MyNewArray[4][3]
E

MyNewArray[5][3]
Fontaine

MyNewArray[6][3]
456 East 2nd Street

MyNewArray[7][3]
Suite 1200

MyNewArray[8][3]
Anyplace

MyNewArray[9][3]
NY

MyNewArray[10][3]
00002

MyNewArray[11][3]
718 555 1212

MyNewArray[12][3]
dfontaine@def.com

MyNewArray[13][3]
1995-05-05 00:00:00

Example 2

<CF_QueryToArray QUERY="MyQuery" ARRAY="MyNewArray" USECFX="Yes" TRANSPOSE="Yes">

MyNewArray[1][1] 1	MyNewArray[2][1] 2
MyNewArray[1][2] ABC Company	MyNewArray[2][2] DEF Company
MyNewArray[1][3] Arnold	MyNewArray[2][3] Diana
MyNewArray[1][4] B	MyNewArray[2][4] E
MyNewArray[1][5] Cooper	MyNewArray[2][5] Fontaine
MyNewArray[1][6] 123 West 1st Street	MyNewArray[2][6] 456 East 2nd Street
MyNewArray[1][7] 1st Floor	MyNewArray[2][7] Suite 1200
MyNewArray[1][8] Anywhere	MyNewArray[2][8] Anyplace
MyNewArray[1][9] NY	MyNewArray[2][9] NY
MyNewArray[1][10] 00001	MyNewArray[2][10] 00002
MyNewArray[1][11] 212 555 1212	MyNewArray[2][11] 718 555 1212
MyNewArray[1][12] acooper@abc.com	MyNewArray[2][12] dfontaine@def.com
MyNewArray[1][13] 1996-07-16 00:00:00	MyNewArray[2][13] 1995-05-05 00:00:00

QUERY	Required. The name of the query you want to convert into an Array.
ARRAY	Required. The name of the new array to be created. The first dimension of the array will represent the columns of the original query, and the second dimension will represent the rows. So, after this tag executes, if ARRAY="MyArray", then #MyArray[3][1]# will return the value in the first row of the third column of QUERY.
ADDCOLUMNNAMES	Optional. Yes or No. If ADDCOLUMNNAMES="Yes", then the column names from the query will be used as the first the first row of the array (see example 1). The array will thus have one more "row" than the query did. Default is No.1
TRANSPOSE	Optional. Yes or No. If TRANSPOSE="Yes", then the rows and columns will be "pivoted" in the new Array. Each "row" in the array will represent a column from the query, and each "column" in the array will represent a row from the query (see example 2). Default is No.
USECFX	Optional. Yes or No. Highly recommended to be set to yes at the time of this writing (4/98). If USECFX="Yes", then my CFX_QueryColumns tag will be used to obtain the list of column names from QUERY that this tag needs internally. This will ensure that the "columns" of the new array are in the order in which you would normally expect. Obviously, the CFX tag must be installed on your server if USECFX="Yes", or an error message will appear. If USECFX="No" or is ommitted, then CF's built-in ColumnList variable will be used to obtain the list of query columns. This means that the order of the "columns" in the new array will be in the order that is reflected by the ColumnList variable, which is very often not the same order as you would expect. For instance, if the query comes from a CFQUERY tag that executes SQL like SELECT FirstName, LastName, Company FROM Customers then #QueryName.ColumnList# will often be something like LastName,Company,FirstName, rather than FirstName,LastName,Company. This may cause you to need to do some extra work when working with the resulting array, since #ArrayName[1][2]# would return the LastName from the second row of the query, rather than the FirstName as you might expect. In order to get the first name reliably, you would need to use something like #ListFindNoCase(QueryName.ColumnList, "FirstName")# to find which position in ColumnList that FirstName happens to be at, and use that number instead of the "1" in the above example.