• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

find surname from query

Guest
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

I have a page which displays 1 record from a query, then i have a previous and next button to go through each record.

i also have a search by surname text box, i need this to find the first match from the query and display it. ie if the search was "Jones" it would go through the query until it find the record that matches.

my code is attached below, i am just not sure how to get the query find the surname match so i can still use the next and previous buttons,
TOPICS
Advanced techniques

Views

1.9K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

Something like ?

<cfquery name="Sget_names" datasource="#application.ds#">
SELECT *
FROM customerenquiryaddresses
<CFIF URL.SURNAMESEARCH NEQ "">
WHERE
SURNAME = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.SURNAME#">
</CFIF>
ORDER BY EqSurname
LIMIT #start_record#, #records_per_page#
</cfquery>


PS:
Please don't use Select *

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 10, 2007 Jul 10, 2007

Copy link to clipboard

Copied

Hi i have tried that but get an error,

any ideas?

Communication link failure: Unknown command

The error occurred in D:\inetpub\vhosts\httpdocs\EnquiryResultsInner.cfm: line 25

23 : </CFIF>
24 : ORDER BY EqSurname
25 : LIMIT #start_record#, #records_per_page#
26 : </cfquery>
27 :



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

SQL SELECT * FROM customerenquiryaddresses WHERE EqSurname LIKE (param 1) ORDER BY EqSurname LIMIT 0, 1



<cfquery name="get_names" datasource="#application.ds#">
SELECT *
FROM customerenquiryaddresses
<CFIF isdefined ("form.Surname")>
WHERE
EqSurname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Surname#%">
</CFIF>
ORDER BY EqSurname
LIMIT #start_record#, #records_per_page#
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

Use a query of query instead.

First get all the records into one query, call it your master.
Then for your flipping back and forth, do a QoQ on the master, and for your search, do another QoQ on the master.

Or something to that effect, hope that helps.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

thats ok but i stil dont understand how that will get all my records with a query then navigate to the nearest match wihin the query that matches the surname search,

so if i type in PH in the search box it will go to record "PHILIPS" and then if i press previous it will go to "NIGHT" and if i press next it goes to "RICHARDS" and so on.

i have tried QofQ but it wont work

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

this is an example where verity works wonders.

build a collection with surname as the body and your primary key as the key for the collection.

when a user does a search, execute a cfsearch on the collection pulling the primary keys
loop over the collection results and execute a cfquery on the datasource selecting just the record you want.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

ok thanks, i think i get it, i just need to know what the key and variable should be?


<cfsearch collection="collection_name" name="queryname" criteria="#form.Surname#%">

<cfloop query="queryname" startrow="#variable#" endrow="#variable + x#">
<cfquery datasource="#application.ds#" name="GetNames">
select *
from customerenquiryaddresses
where EqMainID = #key#
</cfquery>

<cfoutput query="GetNames">#EqSurname#</cfoutput>
</cfloop>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

KEY is returned by the cfsearch and would be used "as-is"

variable is the current record you want to display on the screen, variable + x would be the number of records you want displayed.
if you are only displaying one record (as it seems you are) then you would use startrow="#variable#" and endrow="#variable#"

we pass the startrow within the url as in the attached code

of course, you'll want to use your own variable names and ensure that they are all defined.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

ok many thanks for your quick response, so would this be right?

<cfsearch collection="collection_name" name="queryname" criteria="#form.Surname#%">

<cfloop query="queryname" startrow="#FirstRecord#" and endrow="#LastRecord#">
<cfquery datasource="#application.ds#" name="GetNames">
select *
from customerenquiryaddresses
where EqMainID = #key#
</cfquery>

<cfoutput query="GetNames">#EqSurname#</cfoutput>
</cfloop>


<cfoutput>
<a href="A_Results.cfm?FirstRecord=#evaluate(current_record + 1)#">Next Page</a>
<a href="A_Results.cfm?FirstRecord=#evaluate(current_record - 1)#">Previous Page</a>
</cfoutput>


i have tried this and i get this error?

The collection collection_name does not exist.
The collection you specified does not exists or is not registered with the ColdFusion Search Service.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

with a syntax error (omit the AND in your cfloop statement)

as long as all the variables are assigned somewhere in the code AND the collection exists and has been "<cfindex"ed then it should work fine.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

ok i have taken the and out and i still get the same error.

how to i make sure the collection exsitis?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

create the collection in CF Administrator (under verity) or use the <cfcollection> tag (find out how to use the tag within the coldfusion documentation)

then you will need to create a page to index the collection with your query
and will probably want to set up a scheduled task to reindex the query at certain intervals (depending on how often the table changes)

your index page would look like this:
PLEASE NOTE: I would call the collection something other than "collection_name" as it should be descriptive enough that you can look at it and know what it is.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

ok many thanks, i have now created the verity with <cfcollection>

but i get this error?

Unable to create temporary file


The error occurred in D:\inetpub\vhosts\httpdocs\VerityCreate.cfm: line 12

10 : collection="SurnameCollection"
11 : key="eqmainid"
12 : body="eqsurname"
13 : type="custom"
14 : >


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

can you show the cfcollection statement you used to create the collection?
and also show the code you used to index the collection

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

Yes hope this is correct?

CREATE

<cfcollection
action = "create"
collection = "SurnameCollection"
path = "D:\inetpub\vhosts\httpdocs\verity" >



INDEX

<cfquery name="index_names" datasource="#application.ds#">
SELECT eqsurname, eqmainid
FROM customerenquiryaddresses
ORDER BY EqSurname
</cfquery>
<cfindex
query="index_names"
action="refresh"
collection="SurnameCollection"
key="eqmainid"
body="eqsurname"
type="custom"
>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

and this code is executed on two seperate pages?

NOT on one page?

the cfcollection tag should only be executed ONCE and place a trailing "\" after the word verity within the path (making sure that path exists as well)

and then the cfquery/cfindex tag should be executed

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

no seperate pages, tried to create it again and got an error sayig collection already exsists

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

and if you execute the cfquery and cfindex on a page by itself do you still get the same error message?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

yes same error

Unable to create temporary file



10 : collection="SurnameCollection1"
11 : key="eqmainid"
12 : body="eqsurname"
13 : type="custom"
14 : >


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

it appears as if this error message has something to do with the OS Enviornment variables. I've never seen it before and have no idea how to trouble shoot it for you. Maybe someone else can pick this up from here.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 12, 2007 Jul 12, 2007

Copy link to clipboard

Copied

ok many thanks for your help

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 18, 2007 Jul 18, 2007

Copy link to clipboard

Copied

LATEST
Hi John,

i have spoke to my hosting company who have sorted the verity search, and it now indexes.

i just get an error when i run my results page, any ideas?

Error Executing Database Query.
Communication link failure: Unknown command

The error occurred in EnquiryResultsInner.cfm: line 25

23 : </CFIF>
24 : ORDER BY EqSurname
25 : LIMIT #start_record#, #records_per_page#
26 : </cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation