testing the first charcter of a sting 1

[Aeros]

I have a query that pulls up a set of listings. I would like to test to see if there are listings that start with certain characters. What I am doing is creating an alpha list from A-Z and would like to link the letters only if there are listings that start with that letter. What is the best way to test for this from my query?

Thanks

 

[TruthInSatire]

something like this?

<a href = "listByLetter.cfm?letter=A">A</a>
<a href = "listByLetter.cfm?letter=B">B</a>
<a href = "listByLetter.cfm?letter=C">C</a>

<cfquery name = "qGetResultsByLetter" datasoure = "dsn">
SELECT *
FROM   table
WHERE  fieldName like '#url.letter#%'
</cfquery>

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Aeros]

I am looking to determine the alpha page list after I do a query. Something like this:


<cfquery name="selectListings" datasource="dsn">
select *
from Listings
</cfquery>

<!--- Alpha List --->
A
B
C
D

<br>
<br>
<cfoutput query="selectListings">
#selectListings.listinName#<br>
</cfoutput>

----------------------------------------------

So I need to determine that if there are listings being returned by the 'selectListings' query that start with 'A' then link 'A' in the alpha list, if there are listings that start with 'B'link that...etc.

 

[Aeros]

I almost have it here but im getting screwed up on the looping. As it stands now im getting the letter 3 times because there are 3 listings starting with 'A' then B 2 times...etc:

<cfloop query="selectListings">
<!--- indexLetter = left(lst_list.itemat(1,"lst_sortname"),1) --->
<cfif Left(selectListings.SortName,1) eq 'a'><a href="">A</a></cfif>&nbsp;
<cfif Left(selectListings.SortName,1) eq 'b'><a href="">B</a></cfif>&nbsp;
<cfif Left(selectListings.SortName,1) eq 'c'><a href="">C</a></cfif>&nbsp;
<cfif Left(selectListings.SortName,1) eq 'd'><a href="">D</a></cfif>&nbsp;
<cfif Left(selectListings.SortName,1) eq 'u'><a href="">U</a></cfif>&nbsp;
</cfloop>

Im sure its a simple looping thing...my brain is still asleep.

 

[Aeros]

dangit...im just got getting anywhere with this. Whats the proper way of doing a loop through 'A-Z & 0-9' then test the conditional in the previous message?

 

[TruthInSatire]

why not just

<a href = "">#ucase(left(selectListings.sortName, 1))#</a>

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

i wouldn't use looping, there's no need to do that sort of "heavy lifting" yourself



let's say you were on the page to list the M's, and yet you also wanted to have the alpha list of links at the top of the page

just go through the query twice --

<cfquery name="selectListings" datasource="dsn">
  select left(listinName,1) as firstletter
       , listinName
  from Listings
  order by listinName
</cfquery>

<!--- Alpha List --->
<p>
<cfoutput query="selectListings" 
    group="firstletter">
<cfif firstletter EQ 'M'>
   <span class="grey">M</span>
<cfelse>
   <a href="listByLetter.cfm?letter=#firstletter#"
      >#firstletter#</a>
</cfif> &nbsp;
<cfoutput><!--- nothing ---></cfoutput>
</cfoutput>
</p>

<!--- M's --->
<p>
<cfoutput query="selectListings">
<cfif firstletter EQ 'M'>
 #selectListings.listinName#<br /></cfif>
</cfoutput>
</p>

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[Aeros]

Perfect! Thanks guys for the responses! You saved my morning