Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

URGENT!! A-Z List/ Display Query

Posted on 2002-06-21
15
Medium Priority
?
281 Views
Last Modified: 2013-12-24
Hello,  I would to create a list that goes from A-Z and when I user clicks on it, it will query the database show the results that start with the letter they chose.  The only other part is that there will be several categories they can choose from a drop down box...  

So first they will choose their category
Then they will pick a letter thats a hyperlink
A B C D E F G and so on

then the results will be shown.

Thank you!
0
Comment
Question by:jeffmace
[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
  • 5
  • 5
  • 5
15 Comments
 
LVL 5

Expert Comment

by:Yog
ID: 7099460
page1.cfm


<script>
function doit(link) {
 var iIndex = document.myform.category.selectedIndex
 var catId = document.myform.category[iIndex].value
 window.location.href = "page1.cfm?link=" + link + "cat=" + catID
}
</script>

<form name="myform">

<cfquery name="getcategory" datasource="test">
select * from category
</cfquery>

<select name="category">
<cfoutput query = "getcategory">
<option value="#categoryid#"> #categoryname#
</cfoutput>
</select>

<a href="javascript:doit('A')"> A </a>
<a href="javascript:doit('B')"> B </a>
--
--
<a href="javascript:doit('Z')"> Z </a>

<CFQUERY NAME="SEARCHQUERY" ....>

SELECT * FROM TABLENAME
WHERE NAME LIKE '%#url.link#'
AND CATEGORYID = #URL.CATID#
</CFQUERY>
</form>

If its a different table, you need a join blah blah..
0
 

Author Comment

by:jeffmace
ID: 7099496
This looks good, but I am getting an error... Do we have to specify a <CFPARAM> or something so when the page loads you don't get an error like this:

An error occurred while evaluating the expression:

#url.link#

Error near line 32, column 27.

How can I specigy the letter 'A' or something, becuase I think its looking for a letter to load by defaut... What do you think??
0
 
LVL 5

Expert Comment

by:Yog
ID: 7099532
hi jeff yeah, you are right

<cfparam name="link" default="A">

and in the query just change like

<CFQUERY NAME="SEARCHQUERY" ....>

SELECT * FROM TABLENAME
WHERE NAME LIKE '%#url.link#'

<cfif isdefined("url.cat")>
AND CATEGORYID = #URL.CAT#
</cfif>

</CFQUERY>
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 5

Expert Comment

by:Yog
ID: 7099539
sorry this can be

WHERE NAME LIKE '#url.link#%'

'%' at the end, meaning begins with A..., B...etc

0
 

Author Comment

by:jeffmace
ID: 7099738
This is the code I have.. I am still getting the same error:


<cfparam name="link" default="A">

<script>
function doit(link) {
var iIndex = document.form.category.selectedIndex
var catId = document.form.category[iIndex].value
window.location.href = "letter.cfm?link=" + link + "cat=" + catID
}
</script>

<cfquery name="getLetters" datasource="#Data#">
Select * from Letters
ORDER by Letter ASC
</cfquery>

<form name="form">
<cfquery name="getcategory" datasource="#Data#">
select * from pt_linkcat
</cfquery>

<select name="category">
<cfoutput query = "getcategory">
<option value="#pt_linkcatID#"> #pt_linkCatName#
</cfoutput>
</select>

<cfoutput query="getLetters">
<a href="javascript:doit('#letter#')">#letter#</a>&nbsp;
</cfoutput>

<CFQUERY NAME="SEARCHQUERY" datasource="#Data#">
SELECT pt_linkcat.pt_linkcatName, pt_link.*
FROM pt_link INNER JOIN pt_linkcat ON pt_link.pt_linkcatID = pt_linkcat.pt_linkcatID
WHERE pt_linkName LIKE '#url.link#%'
<cfif isdefined("url.cat")>
AND pt_linkcatID = #URL.CATID#
</cfif>
</CFQUERY>
</form>

<cfoutput query="SEARCHQUERY">
#pt_linkName#
</cfoutput>


0
 

Author Comment

by:jeffmace
ID: 7099741
Also here is the test page so that you can see the error using this code:

http://216.234.242.114/letter.cfm
0
 
LVL 6

Expert Comment

by:dash420
ID: 7100108
You mean to write in the same page user will choose the category then click the hyperlink to get result.

Here is the code for that.

I did this page name as test.cfm

<script language="JavaScript">
  function fntest(val){
     document.frmtest.hdnlink.value = val
       document.frmtest.method = "post"
       document.frmtest.action = "test5.cfm"
       document.frmtest.submit()
  }
</script>
<body>
<form name="frmtest">


<select name="lstCategory">
    <option value="">All
    <option value="1">Category1
     <option value="2">Category2
     <option value="3">Category3
 </select>
 <br>
 
 <cfloop index="i" from="65" to="90">
    <cfif url.link is not #chr(i)#>
       <a href="test5.cfm" onclick="fntest('#chr(i)#');return false;">#chr(i)#</a>
     <cfelse>
        #chr(i)#  
     </cfif>  
 </cfloop>
 
 
<cfquery name="qrytest" datasource="dsn>
   SELECT * FROM TABLENAME
   WHERE 1 = 1
   <cfif isDefined("form.hdnlink") and form.hdnlink is   not "">
      AND UPPER(NAME) LIKE '#form.hdnlink#%'
   </cfif>
   <cfif isdefined("form.lstCategory") and form.lstCategory is not "">
     AND CATEGORY_ID = #form.lstCategory#
   </cfif>
</cfquery>
<br>
<cfoutput>
  <cfloop query="qrytest">
     #first_name#<br>
  </cfloop>  
</cfoutput>
</form>
</body>

I just the code you can format it according to your requirement.




0
 
LVL 6

Expert Comment

by:dash420
ID: 7100116
there is some typo error, please modified it as

<cfloop index="i" from="65" to="90">
    <cfif isDefined("form.hdnlink") and form.hdnlink is not #chr(i)#>
       <a href="test.cfm" onclick="fntest('#chr(i)#');return false;">#chr(i)#</a>
     <cfelse>
        #chr(i)#  
     </cfif>  
 </cfloop>

0
 
LVL 6

Expert Comment

by:dash420
ID: 7100204
please make following line just above this loop
like

<cfparam name="form.hdnlink" default="A">

<cfloop index="i" from="65" to="90">
............
</cfloop>


actually there is no edit option edit your answer/comment.
once u submit it becoemes readonly. i just saw that there is no cfparam. so no link will comes. as it will always go to else part and form.hdnlink always will not be defined.

already three time i posted my answer with some modificatio hope this will be the last.
0
 

Author Comment

by:jeffmace
ID: 7100499
Here is the code I am using for dash420s code but getting errors:

<script language="JavaScript">
 function fntest(val){
    document.frmtest.hdnlink.value = val
      document.frmtest.method = "post"
      document.frmtest.action = "test5.cfm"
      document.frmtest.submit()
 }
</script>
<body>
<form name="frmtest">

<cfquery name="getcategory" datasource="#Data#">
select * from pt_linkcat
</cfquery>

<select name="lstCategory">
<cfoutput query = "getcategory">
<option value="#pt_linkcatID#"> #pt_linkCatName#
</cfoutput>
</select>
<br><br>


<cfparam name="form.hdnlink" default="A">

<cfloop index="i" from="65" to="90">
   <cfif isDefined("form.hdnlink") and form.hdnlink is not #chr(i)#>
      <a href="test.cfm" onclick="frmtest('#chr(i)#');return false;">#chr(i)#</a>
    <cfelse>
       #chr(i)#  
    </cfif>  
</cfloop>
 

<cfquery name="qrytest" datasource="#data#">
  SELECT pt_linkcat.pt_linkcatName, pt_link.*
  FROM pt_link INNER JOIN pt_linkcat ON pt_link.pt_linkcatID = pt_linkcat.pt_linkcatID
  WHERE 1 = 1
  <cfif isDefined("form.hdnlink") and form.hdnlink is not "">
     AND UPPER(NAME) LIKE '#form.hdnlink#%'
  </cfif>
  <cfif isdefined("form.lstCategory") and form.lstCategory is not "">
    AND pt_linkcatID = #form.lstCategory#
  </cfif>
</cfquery>
<br>
<cfoutput>
 <cfloop query="qrytest">
    #pt_linkName#<br>
 </cfloop>  
</cfoutput>
</form>
</body>

Also here is the link to the page so you can see whats going on: http://216.234.242.114/test5.cfm
0
 
LVL 6

Expert Comment

by:dash420
ID: 7102635
please embeded this code with cfoutpt
like
<cfoutput>
<cfloop index="i" from="65" to="90">
  <cfif isDefined("form.hdnlink") and form.hdnlink is not #chr(i)#>
     <a href="test.cfm" onclick="frmtest('#chr(i)#');return false;">#chr(i)#</a>
   <cfelse>
      #chr(i)#  
   </cfif>  
</cfloop>
</cfoutput>

0
 
LVL 6

Expert Comment

by:dash420
ID: 7102647
first error is
Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NAME'.

I think the table don't have the column name 'NAME'. Check it.

second one is because the value gone as #chr(i)# instead of 'A', 'B','C', so on.
so put cfoutput between cfloop for displaying A,B..
0
 
LVL 5

Accepted Solution

by:
Yog earned 2000 total points
ID: 7104281
jeff, instead of #URL.link#, use #link# in your query

<cfparam name="link" default="A">

<script>
function doit(link) {
var iIndex = document.form.category.selectedIndex
var catId = document.form.category[iIndex].value
window.location.href = "letter.cfm?link=" + link + "cat=" + catID
}
</script>

<cfquery name="getLetters" datasource="#Data#">
Select * from Letters
ORDER by Letter ASC
</cfquery>

<form name="form">
<cfquery name="getcategory" datasource="#Data#">
select * from pt_linkcat
</cfquery>

<select name="category">
<cfoutput query = "getcategory">
<option value="#pt_linkcatID#"> #pt_linkCatName#
</cfoutput>
</select>

<cfoutput query="getLetters">
<a href="javascript:doit('#letter#')">#letter#</a>&nbsp;
</cfoutput>

<CFQUERY NAME="SEARCHQUERY" datasource="#Data#">
SELECT pt_linkcat.pt_linkcatName, pt_link.*
FROM pt_link INNER JOIN pt_linkcat ON pt_link.pt_linkcatID = pt_linkcat.pt_linkcatID
WHERE pt_linkName LIKE '#link#%'
<cfif isdefined("url.cat")>
AND pt_linkcatID = #URL.CATID#
</cfif>
</CFQUERY>
</form>

<cfoutput query="SEARCHQUERY">
#pt_linkName#
</cfoutput>
0
 

Author Comment

by:jeffmace
ID: 7162583
This worked with a little modificat from my code... but you had the right thing going.. thanks and sorry for the delay.
0
 
LVL 5

Expert Comment

by:Yog
ID: 7164974
thanks jeff, you are welcome :)
0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

705 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