21 Replies Latest reply on Jul 18, 2007 1:01 PM by JohnGree

    find surname from query

    JohnGree Level 1
      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,
        • 1. Re: find surname from query
          Scott_thornton Level 1
          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 *
          • 2. find surname from query
            JohnGree Level 1
            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>
            • 3. Re: find surname from query
              Chuck1411 Level 1
              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.
              • 4. Re: find surname from query
                JohnGree Level 1
                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
                • 5. find surname from query
                  virtually_john
                  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.
                  • 6. Re: find surname from query
                    JohnGree Level 1
                    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>
                    • 7. Re: find surname from query
                      virtually_john Level 1
                      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.
                      • 8. find surname from query
                        JohnGree Level 1
                        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.
                        • 9. Re: find surname from query
                          virtually_john Level 1
                          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.
                          • 10. Re: find surname from query
                            JohnGree Level 1
                            ok i have taken the and out and i still get the same error.

                            how to i make sure the collection exsitis?
                            • 11. Re: find surname from query
                              virtually_john Level 1
                              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.
                              • 12. Re: find surname from query
                                JohnGree Level 1
                                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 : >


                                • 13. Re: find surname from query
                                  virtually_john Level 1
                                  can you show the cfcollection statement you used to create the collection?
                                  and also show the code you used to index the collection
                                  • 14. Re: find surname from query
                                    JohnGree Level 1
                                    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"
                                    >
                                    • 15. Re: find surname from query
                                      virtually_john Level 1
                                      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
                                      • 16. Re: find surname from query
                                        JohnGree Level 1
                                        no seperate pages, tried to create it again and got an error sayig collection already exsists

                                        • 17. Re: find surname from query
                                          virtually_john Level 1
                                          and if you execute the cfquery and cfindex on a page by itself do you still get the same error message?
                                          • 18. Re: find surname from query
                                            JohnGree Level 1
                                            yes same error

                                            Unable to create temporary file



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


                                            • 19. Re: find surname from query
                                              virtually_john Level 1
                                              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.
                                              • 20. Re: find surname from query
                                                JohnGree Level 1
                                                ok many thanks for your help
                                                • 21. Re: find surname from query
                                                  JohnGree Level 1
                                                  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>