• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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



0
andrewshka
Asked:
andrewshka
  • 8
  • 6
  • 2
  • +2
1 Solution
 
cdillonCommented:
Something like this should work.

<!---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>
0
 
cdillonCommented:
oops, the </td> should be after the second loop instead of before.
0
 
WasistdasCommented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Renante EnteraSenior PHP DeveloperCommented:
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_Animals.recordcount \ 2) + 1>
<cfset start_column2=number_of_rows + 1>

<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#" endrow="#start_column2#">
        <td>#pc_name#</td>
        <cfset start_column2=#IncrementValue(start_column2)#>
      </cfloop>
    </tr>
  </cfoutput>
</table>

</body>
</html>

Then try running this one... I hope this will help you a lot...

GOODLUCK!
0
 
andrewshkaAuthor Commented:
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>
0
 
anandkpCommented:
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.pc_name)>
<CFSET xlen = round(Evaluate(listlen(x)/2))>

<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+xlen))#<CFELSE>&nbsp;</CFIF></TD>
    </tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>

K'Rgds
Anand
0
 
andrewshkaAuthor Commented:
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
0
 
andrewshkaAuthor Commented:
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
0
 
andrewshkaAuthor Commented:
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_title)>
<CFSET xlen = round(Evaluate(listlen(x)/2))>

<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
   <tr>
       <td><a href="/#test_query.template_file#?doc_id=#test_query.doc_id#">#Listgetat(x,i)#</a></TD>
       <td><cfif i+xlen LTE listlen(x)><a href="/#test_query.template_file#?doc_id=#test_query.doc_id#">#Listgetat(x,(i+xlen))#</a><CFELSE>&nbsp;</CFIF></TD>
   </tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>

</body>
</html>
0
 
anandkpCommented:
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
0
 
anandkpCommented:
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_title)>
<CFSET xlen = round(Evaluate(listlen(x)/2))>

<CFSET y = ValueList(test_query.template_file)>
<CFSET z = ValueList(test_query.doc_id)>

<CFOUTPUT>
#listlen(x)#<BR>
#xlen#<BR>
<table BORDER="1">
<CFLoop INDEX="i" FROM="1" TO="#xlen#">
  <tr>
      <td><a href="/#Listgetat(y,i)#?doc_id=#Listgetat(z,i)#">#Listgetat(x,i)#</a></TD>
      <td><cfif i+xlen LTE listlen(x)><a href="/#Listgetat(y,(i+xlen))#?doc_id=#Listgetat(z,(i+xlen))#">#Listgetat(x,(i+xlen))#</a><CFELSE>&nbsp;</CFIF></TD>
  </tr>
</CFLOOP>
</TABLE>
</CFOUTPUT>

</body>
</html>

Let me know

K'Rgds
Anand
0
 
Renante EnteraSenior PHP DeveloperCommented:
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>&nbsp;#pc_name#</td>
       <td>&nbsp;
      <cfloop query="List_of_Animals" startrow="#start_column2#" endrow="#start_column2#">
        #pc_name#
        <cfset start_column2=#IncrementValue(start_column2)#>
      </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#" endrow="#start_column2#">
       <td>#pc_name#</td>
       <cfset start_column2=#IncrementValue(start_column2)#>
     </cfloop>
   </tr>
</cfoutput>

It is just a matter of the presence of the space(&nbsp;).
And be sure that looping is after the &nbsp;... OK

Best wishes.....
0
 
anandkpCommented:
hi,

r things solved ... or u still need more help on this

let me know
0
 
andrewshkaAuthor Commented:
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))#), occupying document position (31:48) to (31:70).

0
 
andrewshkaAuthor Commented:
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))#), occupying document position (31:48) to (31:70).

0
 
anandkpCommented:
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)#?doc_id=#Listgetat(z,i)#">#Listgetat(x,i)#</a></TD>
     <td><cfif i+xlen LTE listlen(x)>
     <cfif i+ylen LTE listlen(y)><a href="/#Listgetat(y,(i+xlen))#?doc_id=#Listgetat(z,(i+xlen))#">#Listgetat(x,(i+xlen))#</a><CFELSE>#Listgetat(x,(i+xlen))#</CFIF><CFELSE>&nbsp;</CFIF></TD>
 </tr>

but i wont recommend this
c what u feel is comfortable !

let me know

K'Rgds
Anand
0
 
andrewshkaAuthor Commented:
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
0
 
anandkpCommented:
u'll need to put up a default delimeter as "~" & then use the list fucntions

here is how u can do it:

<CFSET x = ValueList(test_query.doc_title,'~')>
<CFSET xlen = round(Evaluate(listlen(x)/2))>

<CFSET y = ValueList(test_query.template_file,'~')>
<CFSET z = ValueList(test_query.doc_id,'~')>

& then use the delimeter option in all the listgetat fucntions & have thigns solvd out for u - like :
#Listgetat(y,(i+xlen),'~')#

if u need more help - let me know

once u start playing around with the list functions - u'll realise - how easy it is to manage things using them !

K'Rgds
Anand
0
 
andrewshkaAuthor Commented:
Anand, thank you very much.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now