SO I know where to put the cell break - half way through the current
letter. To know when I am halfway I need to know how many there are.
Output code:
<cfoutput>
<cfset request.currentLetter = "">
<cfset request.currentLetterStartRow = 1>
<!--- get number of non alp[habetical characters --->
<cfquery name="qry_getCurrentLettercount" dbtype="query">
select count(gameid) as lettercount
from qry_getgames
where name not like 'A%'
and name not like 'B%'
</cfquery>
<table border=1>
<tr><td valign="top" width="50%">
<!--- start Loop --->
<cfloop query="qry_getgames">
<!--- if we are starting a new letter record currentrow and find
out how many of this letter there are --->
<cfif (request.currentLetter neq left(qry_getgames.name,1) and
request.currentLetter neq "") or (request.currentLetter eq "" and
(left(qry_getgames.name,1) eq "A" or left(qry_getgames.name,1) eq "a"))>
<!--- if last letter only had one row then add in blank
second column cell --->
<cfif qry_getCurrentLettercount.lettercount eq 1>
<td width="50%"> </td>
</cfif>
<!--- start new row --->
</td></tr><tr>
<cfset request.currentLetter =
left(qry_getgames.name,1)>
<cfset request.currentLetterStartRow =
qry_getgames.currentRow>
<cfquery name="qry_getCurrentLettercount"
dbtype="query">
select count(gameid) as lettercount
from qry_getgames
where name like '#request.currentLetter#%'
</cfquery>
<!--- output letter header --->
<td colspan="2">'#request.currentLetter#' count =
#qry_getCurrentLettercount.lettercount#</td>
<!--- start first cell of new letter --->
</tr><tr><td valign="top" width="50%">
</cfif>
<!--- if we are at or just after halfway insert break between
two columns --->
<cfif qry_getgames.currentRow - request.currentLetterStartRow eq
ceiling(qry_getCurrentLettercount.lettercount/2)>
</td><td valign="top" width="50%">
</cfif>
<!--- output records --->
<input type="checkbox" name="game"
value="#qry_getgames.gameid#"> #qry_getgames.name#<br>
</cfloop>
<cfif qry_getCurrentLettercount.lettercount eq 1>
<td> </td>
</cfif>
</tr></td>
</table>
</cfoutput>
Giles Roadnight
http://giles.roadnight.name
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: 21 November 2002 16:21
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] Counting titles that do not start with letter
using SQL
why do you need to know how many records there are under A in order to
output them in 2 columns? what's your output code look like?
Duncan Cumming
IT Manager
http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600
Creative solutions in a technical world
----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------
"Giles Roadnight"
<giles.roadnight@goldcockerelbo To:
<[EMAIL PROTECTED]>
oks.co.uk> cc:
Subject:
[ cf-dev ] Counting titles that do not start with letter
11/21/02 04:16 PM using SQL
Please respond to dev
I am looping through a query of titles and I want to output the results
per letter in two columns so under A there would be two columns of
titles starting with A. To do this I need the count of records beginning
with A which I have done like this:
<cfquery name="qry_getCurrentLettercount" dbtype="query">
select count(gameid) as lettercount
from qry_getgames
where name like '#request.currentLetter#%'
</cfquery>
How though do I get the count of titles that do not start with a letter?
I could do it like this:
<cfquery name="qry_getCurrentLettercount" dbtype="query">
select count(gameid) as lettercount
from qry_getgames
where name not like 'A%'
and name not like 'B%'
ect...
</cfquery>
But I don't want to have to write that out 52 times (upper and
lowercase)
Also is there a way to make SQL non case sensitive so the first query
will count upper and lower case?
Thanks
Giles Roadnight
http://giles.roadnight.name
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
