Browseable Index

[InstantJoe]

Hi – I’m not sure if this belongs in the advanced techniques section but being that i'm a beginning to intermediate ColdFusion programmer, it seems advanced to me.

I have a task that I need some help devising a strategy for. I am creating a searchable obituaries index database for a library. It will of course be searchable by such criteria as name, date, date range, etc. One of the ways that the library would like the index to be searchable is a ‘browse’ option, whereby a user could enter say the first three letters of the last name of the deceased and the display would show 15 records or so that start with last names beginning with those 3 letters.

Here is where I’m not sure how to approach this, however. The query should return all of the records in the database, however the first records displayed should be those beginning with the letters entered by the end user, with next and previous buttons to browse through the other records at will. I know how to do Next Previous records; I’m just not quite sure how to do my query(ies) or display my <cfoutput> on my initial disply so as to start with the records in that section.

Any help would be appreciated. Thanks.

[Newsgroup_User]

UNION sql operator comes to mind...
correct syntax depends on your db...

(SELECT id, lastname, firstname, dob, dod, 1 AS sortcol
FROM obituaries
WHERE UPPER(lastname) LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#UCASE(searchphrase)#%">)
UNION
(SELECT id, lastname, firstname, dob, dod, 2 AS sortcol
FROM obituaries
WHERE id NOT IN (SELECT id FROM obituaries WHERE UPPER(lastname) LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="#UCASE(searchphrase)#%">))
ORDER BY sortcol, lastname, firstname

hth


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[-__cfSearching__-]

Azadi wrote:
> UNION sql operator comes to mind...
> correct syntax depends on your db...

If the query should return all records, what about a slight modification? I have not tried it, but I was thinking you could drop the UNION and just use CASE.

-- without cfqueryparam for clarity
SELECT id, lastname, firstname, dob, dod,
CASE WHEN UPPER(LastName) LIKE '#search#%' THEN 1 ELSE 2 END AS sortcol
FROM obituaries
ORDER BY sortcol, lastname, firstname

[Newsgroup_User]

yes, it is definitely more readable.
but execution times were the same for both, at least in my quick tests.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[-__cfSearching__-]

> yes, it is definitely more readable.
> but execution times were the same for both, at least in
> my quick tests.

As both are ultimately returning the entire table, yes that sounds possible.

[InstantJoe]

Thanks for the help guys!