?
Solved

CF query results displaying in two sorted columns

Posted on 2003-03-22
19
Medium Priority
?
226 Views
Last Modified: 2013-12-24
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
Comment
Question by:andrewshka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +2
19 Comments
 
LVL 3

Expert Comment

by:cdillon
ID: 8190195
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
 
LVL 3

Expert Comment

by:cdillon
ID: 8190197
oops, the </td> should be after the second loop instead of before.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8190728
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 14

Expert Comment

by:Renante Entera
ID: 8192404
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8192514
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8192884
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8193008
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8193042
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8193193
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8193310
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8193420
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 8193896
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8196866
hi,

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

let me know
0
 
LVL 1

Author Comment

by:andrewshka
ID: 8197690
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8197795
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
 
LVL 17

Expert Comment

by:anandkp
ID: 8200412
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8205475
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
 
LVL 17

Accepted Solution

by:
anandkp earned 2000 total points
ID: 8208091
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
 
LVL 1

Author Comment

by:andrewshka
ID: 8208207
Anand, thank you very much.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
What You Need to Know when Searching for a Webhost Provider
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question