andrewshka
asked on
CF query results displaying in two sorted columns
I'd like to query a sql 2000 DB and return a recordset that will be sorted alphabetically into a two column table like the one below. I'd like to do it using CF 5 code. I can write the query and sort the results, but don't know how to get the results to display in two table columns sorted alphabetically. The two columns need to be balanced so that the columns are the same length, or as close as possible, and there is no way to know how many items there will be in the list before the query runs so the row number can't be hard-coded in the query.
COLUMN 1 COLUMN 2
American Elk Nubian Goat
Asian Elephant Pygmy Goat
Brazilian Tapir Sicilian Donkey
Common Zebra Sika Deer
Domestic Sheep Vietnamese Pot Belly Pig
Dromedary Camel
COLUMN 1 COLUMN 2
American Elk Nubian Goat
Asian Elephant Pygmy Goat
Brazilian Tapir Sicilian Donkey
Common Zebra Sika Deer
Domestic Sheep Vietnamese Pot Belly Pig
Dromedary Camel
oops, the </td> should be after the second loop instead of before.
Use IIF() function like you do altering row colors. In this case you can easily change number of columns you need and all output will be balanced.
Hello there! andrewshka.
I have a simple example for your problem. Here is the code:
<html>
<head>
<title>Two Columns</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<cfquery name="List_of_Animals" datasource="Database_Name"
select * from Animals
order by Animal_Name
</cfquery>
<cfset number_of_rows=(List_of_An
<cfset start_column2=number_of_ro
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Column 1</td>
<td>Column 2</td>
</tr>
<cfoutput query="List_of_Animals" startrow="1" maxrows="#number_of_rows#"
<tr>
<td>#pc_name#</td>
<cfloop query="List_of_Animals" startrow="#start_column2#"
<td>#pc_name#</td>
<cfset start_column2=#IncrementVa
</cfloop>
</tr>
</cfoutput>
</table>
</body>
</html>
Then try running this one... I hope this will help you a lot...
GOODLUCK!
I have a simple example for your problem. Here is the code:
<html>
<head>
<title>Two Columns</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<cfquery name="List_of_Animals" datasource="Database_Name"
select * from Animals
order by Animal_Name
</cfquery>
<cfset number_of_rows=(List_of_An
<cfset start_column2=number_of_ro
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Column 1</td>
<td>Column 2</td>
</tr>
<cfoutput query="List_of_Animals" startrow="1" maxrows="#number_of_rows#"
<tr>
<td>#pc_name#</td>
<cfloop query="List_of_Animals" startrow="#start_column2#"
<td>#pc_name#</td>
<cfset start_column2=#IncrementVa
</cfloop>
</tr>
</cfoutput>
</table>
</body>
</html>
Then try running this one... I hope this will help you a lot...
GOODLUCK!
ASKER
While both suggestions worked, entrance2002's solution is what I am looking for -- a 2 column table with individual cells.
I'll award the points to entrance2002, and I have one follow-up question for you.
Since the last <td> does not get written, the last cell does not have a border (this would not be a problem with a borderless table but I need a border). Do you know a way I can get the last <td> to output? Right now the output looks like this:
<table border="1" cellspacing="0" cellpadding="2">
<tr>
<td>Column 1</td>
<td>Column 2</td>
</tr>
<tr>
<td>American Elk</td>
<td>Asian Elephant</td>
</tr>
<tr>
<td>Brazilian Tapir</td>
<td>Common Zebra</td>
</tr>
<tr>
<td>Domestic Sheep</td>
</tr>
</table>
I'll award the points to entrance2002, and I have one follow-up question for you.
Since the last <td> does not get written, the last cell does not have a border (this would not be a problem with a borderless table but I need a border). Do you know a way I can get the last <td> to output? Right now the output looks like this:
<table border="1" cellspacing="0" cellpadding="2">
<tr>
<td>Column 1</td>
<td>Column 2</td>
</tr>
<tr>
<td>American Elk</td>
<td>Asian Elephant</td>
</tr>
<tr>
<td>Brazilian Tapir</td>
<td>Common Zebra</td>
</tr>
<tr>
<td>Domestic Sheep</td>
</tr>
</table>
hi how abt giving this a try !
<cfquery name="List_of_Animals" datasource="Database_Name"
select * from Animals order by Animal_Name
</cfquery>
<CFSET x = ValueList((List_of_Animals
<CFSET xlen = round(Evaluate(listlen(x)/
<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
<tr>
<td>#Listgetat(x,i)#</TD>
<td><cfif i+xlen LTE listlen(x)>#Listgetat(x,(i
</tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>
K'Rgds
Anand
<cfquery name="List_of_Animals" datasource="Database_Name"
select * from Animals order by Animal_Name
</cfquery>
<CFSET x = ValueList((List_of_Animals
<CFSET xlen = round(Evaluate(listlen(x)/
<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
<tr>
<td>#Listgetat(x,i)#</TD>
<td><cfif i+xlen LTE listlen(x)>#Listgetat(x,(i
</tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>
K'Rgds
Anand
ASKER
Anand, that is an elegant solution, thank you. Can I use the same code if I want to display grouped results? Say for instance I write the query to group animals as mammals, reptiles, etc., and then want to display the list like this:
Mammals
Anteater
Giraffe
Reptiles
Cobra
Rattlesnake
Fish
Sea Bass
Yellowtail
Mammals
Anteater
Giraffe
Reptiles
Cobra
Rattlesnake
Fish
Sea Bass
Yellowtail
ASKER
Anand, I should add that I still want to use the two column table, but I would like to display the results grouped by the different top-level categories I mention above on some pages.
Thanks,
Andrewshka
Thanks,
Andrewshka
ASKER
Anand, For the moment, please forget my request about grouping results. I'm having a problem getting the titles to link to their proper pages. All titles link to the same page rather than each title linking to their own page. The query and the code that I wrote works, I use it for a single column display page now, bit I'm missing something in adapting your code into mine.
<html>
<cfquery name="test_query" datasource="B_power">
SELECT documents.doc_id, documents.keywords, documents.doc_title, templates.template_file
FROM dbo.documents INNER JOIN dbo.templates
ON documents.template_id = templates.template_id
WHERE documents.template_id = 197
AND documents.doc_status = 'A'
ORDER BY doc_title DESC
</cfquery>
<head>
<title>Two Column Query Test</title>
</head>
<body>
<CFSET x = ValueList(test_query.doc_t
<CFSET xlen = round(Evaluate(listlen(x)/
<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
<tr>
<td><a href="/#test_query.templat
<td><cfif i+xlen LTE listlen(x)><a href="/#test_query.templat
</tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>
</body>
</html>
<html>
<cfquery name="test_query" datasource="B_power">
SELECT documents.doc_id, documents.keywords, documents.doc_title, templates.template_file
FROM dbo.documents INNER JOIN dbo.templates
ON documents.template_id = templates.template_id
WHERE documents.template_id = 197
AND documents.doc_status = 'A'
ORDER BY doc_title DESC
</cfquery>
<head>
<title>Two Column Query Test</title>
</head>
<body>
<CFSET x = ValueList(test_query.doc_t
<CFSET xlen = round(Evaluate(listlen(x)/
<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
<tr>
<td><a href="/#test_query.templat
<td><cfif i+xlen LTE listlen(x)><a href="/#test_query.templat
</tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>
</body>
</html>
hi,
I am abit lost with this .. one sec
did the suggestion i gave abt 2 sorted columns work ???
ok above u have some query - but do u want one column or 2 column in it ???
if its one columne - then the code will need some modifications
let me know
K'Rgds
Anand
I am abit lost with this .. one sec
did the suggestion i gave abt 2 sorted columns work ???
ok above u have some query - but do u want one column or 2 column in it ???
if its one columne - then the code will need some modifications
let me know
K'Rgds
Anand
ok got u
replace ur code with this one
<html>
<cfquery name="test_query" datasource="B_power">
SELECT documents.doc_id, documents.keywords, documents.doc_title, templates.template_file
FROM dbo.documents INNER JOIN dbo.templates
ON documents.template_id = templates.template_id
WHERE documents.template_id = 197
AND documents.doc_status = 'A'
ORDER BY doc_title DESC
</cfquery>
<head>
<title>Two Column Query Test</title>
</head>
<body>
<CFSET x = ValueList(test_query.doc_t
<CFSET xlen = round(Evaluate(listlen(x)/
<CFSET y = ValueList(test_query.templ
<CFSET z = ValueList(test_query.doc_i
<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
<tr>
<td><a href="/#Listgetat(y,i)#?do
<td><cfif i+xlen LTE listlen(x)><a href="/#Listgetat(y,(i+xle
</tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>
</body>
</html>
Let me know
K'Rgds
Anand
replace ur code with this one
<html>
<cfquery name="test_query" datasource="B_power">
SELECT documents.doc_id, documents.keywords, documents.doc_title, templates.template_file
FROM dbo.documents INNER JOIN dbo.templates
ON documents.template_id = templates.template_id
WHERE documents.template_id = 197
AND documents.doc_status = 'A'
ORDER BY doc_title DESC
</cfquery>
<head>
<title>Two Column Query Test</title>
</head>
<body>
<CFSET x = ValueList(test_query.doc_t
<CFSET xlen = round(Evaluate(listlen(x)/
<CFSET y = ValueList(test_query.templ
<CFSET z = ValueList(test_query.doc_i
<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
<tr>
<td><a href="/#Listgetat(y,i)#?do
<td><cfif i+xlen LTE listlen(x)><a href="/#Listgetat(y,(i+xle
</tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>
</body>
</html>
Let me know
K'Rgds
Anand
Sorry for not responding immediately cause I have a client far from my computer.
You can have this code:
<cfoutput query="List_of_Animals" startrow="1" maxrows="#number_of_rows#"
<tr>
<td> #pc_name#</td>
<td>
<cfloop query="List_of_Animals" startrow="#start_column2#"
#pc_name#
<cfset start_column2=#IncrementVa
</cfloop>
</td>
</tr>
</cfoutput>
Instead of:
<cfoutput query="List_of_Animals" startrow="1" maxrows="#number_of_rows#"
<tr>
<td>#pc_name#</td>
<cfloop query="List_of_Animals" startrow="#start_column2#"
<td>#pc_name#</td>
<cfset start_column2=#IncrementVa
</cfloop>
</tr>
</cfoutput>
It is just a matter of the presence of the space( ).
And be sure that looping is after the ... OK
Best wishes.....
You can have this code:
<cfoutput query="List_of_Animals" startrow="1" maxrows="#number_of_rows#"
<tr>
<td> #pc_name#</td>
<td>
<cfloop query="List_of_Animals" startrow="#start_column2#"
#pc_name#
<cfset start_column2=#IncrementVa
</cfloop>
</td>
</tr>
</cfoutput>
Instead of:
<cfoutput query="List_of_Animals" startrow="1" maxrows="#number_of_rows#"
<tr>
<td>#pc_name#</td>
<cfloop query="List_of_Animals" startrow="#start_column2#"
<td>#pc_name#</td>
<cfset start_column2=#IncrementVa
</cfloop>
</tr>
</cfoutput>
It is just a matter of the presence of the space( ).
And be sure that looping is after the ... OK
Best wishes.....
hi,
r things solved ... or u still need more help on this
let me know
r things solved ... or u still need more help on this
let me know
ASKER
Anand, excellent. The table displays in two columns, and the links are correct. There is one error has to do with the empty cell in the last row of column two. This is the error message:
An error occurred while evaluating the expression:
#Listgetat(y,(i+xlen))#
Error near line 31, column 49.
In function ListGetAt(list, index [, delimiters]) the value of index, which is 35, is not a valid index for the list given as a the first argument (this list has 34 elements). Valid indexes are in the range 1 through the number of elements in the list
The error occurred while processing an element with a general identifier of (#Listgetat(y,(i+xlen))#),
An error occurred while evaluating the expression:
#Listgetat(y,(i+xlen))#
Error near line 31, column 49.
In function ListGetAt(list, index [, delimiters]) the value of index, which is 35, is not a valid index for the list given as a the first argument (this list has 34 elements). Valid indexes are in the range 1 through the number of elements in the list
The error occurred while processing an element with a general identifier of (#Listgetat(y,(i+xlen))#),
ASKER
Anand, excellent. The table displays in two columns, and the links are correct. There is one error has to do with the empty cell in the last row of column two. This is the error message:
An error occurred while evaluating the expression:
#Listgetat(y,(i+xlen))#
Error near line 31, column 49.
In function ListGetAt(list, index [, delimiters]) the value of index, which is 35, is not a valid index for the list given as a the first argument (this list has 34 elements). Valid indexes are in the range 1 through the number of elements in the list
The error occurred while processing an element with a general identifier of (#Listgetat(y,(i+xlen))#),
An error occurred while evaluating the expression:
#Listgetat(y,(i+xlen))#
Error near line 31, column 49.
In function ListGetAt(list, index [, delimiters]) the value of index, which is 35, is not a valid index for the list given as a the first argument (this list has 34 elements). Valid indexes are in the range 1 through the number of elements in the list
The error occurred while processing an element with a general identifier of (#Listgetat(y,(i+xlen))#),
hi,
this is bcos of improper DB values entered
i assumed that if the name is present the corresponding ID's & link names wld also be present.
but for the last record [chk the DB] things r not right
this can be solved by having another condition for links
<tr>
<td><a href="/#Listgetat(y,i)#?do
<td><cfif i+xlen LTE listlen(x)>
<cfif i+ylen LTE listlen(y)><a href="/#Listgetat(y,(i+xle
</tr>
but i wont recommend this
c what u feel is comfortable !
let me know
K'Rgds
Anand
this is bcos of improper DB values entered
i assumed that if the name is present the corresponding ID's & link names wld also be present.
but for the last record [chk the DB] things r not right
this can be solved by having another condition for links
<tr>
<td><a href="/#Listgetat(y,i)#?do
<td><cfif i+xlen LTE listlen(x)>
<cfif i+ylen LTE listlen(y)><a href="/#Listgetat(y,(i+xle
</tr>
but i wont recommend this
c what u feel is comfortable !
let me know
K'Rgds
Anand
ASKER
Anand, I found out that the error is happening because there is one title that has a comma in it, so it throws off the valuelist function and generates an extra record with no doc_id, etc...
I can fix that one title, but is there a way to get around that problem by using a different delimiter, or?
Thanks so much for staying with me on this.
Andy
I can fix that one title, but is there a way to get around that problem by using a different delimiter, or?
Thanks so much for staying with me on this.
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Anand, thank you very much.
<!---use your own query here --->
<cfquery name="myquery">
select animalname from inventory
</cfquery>
<cfset half = myquery.recordcount \ 2 >
<cfset half_plus_one = half + 1 >
<table>
<tr>
<td>
<cfloop query="myquery" endrow="#half#">
#myquery.myfield# <br>
</cfloop>
</td>
<td>
</td>
<cfloop query="myquery" startrow="#half_plus_one#"
#myquery.myfield# <br>
</cfloop>
</tr>
</table>