2 Replies Latest reply on Jan 28, 2013 8:26 AM by cf_junkie

    Unwanted links when using ORDER BY

    cf_junkie Level 1

      I have a search results page that has been giving me a headache for a few weeks now lol. I have the following SQL to filter what the user is looking for with the search engine. Everything works fine UNTIL i sort the results by province. When i do so i get sereval unwanted image links. For example. If i get 7 results when i sort by ON (ontario) and 1 out of the 7 pets has an image which gives the word "image" repeating 7x based on the sort result and only 1 of those links actually work. Not sure why the others are appearing. I know it's because of the ORDER BY i have in the SQL, because when i remove the order by it works prefectly. I searched all over to find someone else with somewhat of a similar problem, but no go. Looked at my sql reference book and still nothing. Now sure whats wrong. Hope someone can see something i can't.

       

       

      ------ SQL for search engine -------

        <cfquery name="qSearch" datasource="#APPLICATION.dataSource#">

        SELECT PetID, PetName, PetType, PetStatus, PetGender, Province, City, MyPetID, PostDate, PetBreed, ImageName

        FROM PetFind

        WHERE 0=0

        AND (PetName = '#Search#' OR MyPetID LIKE '%#Search#%')

      <cfif IsDefined('URL.Province')>

        AND Province = <cfqueryparam value="#URL.Province#" cfsqltype="cf_sql_varchar">

        ORDER BY City

        ASC

      </cfif>

        </cfquery>

       

      ------ Image link (popUp) ------

              <a href="javascript:popImage('../pimages/#URLEncodedFormat(qSearch.ImageName)#.jpg','Picture of #qSearch.PetName#')" onLoad="window.location.href=window.location.href">
                Image
              </a>