Need Help Creating a Search Page

[Walyer]

Hey,
I've been trying to setup a search page for the last week with no luck. I've been reading books (maybe not the right ones) and searching the web for examples, also with no luck.

I'm using Microsoft Access for my database and Coldfusion 6.1 for my server.

What I've been trying to do is - all on the same page; have a text box to enter your search word, a button to click to start the search, and last a list box for the search result(s) to be displayed.

If anyone can help with the code, web link with a good example, or even a high quality book so I can finish this project of mine that would be great.

Thank you all for your time and effort.

[Walyer]

Nevermind!

[tigeralex]

Easy dude!

The best way I've found in doing it was to create a form that sends keywords/whateverYourSearchingFieldsAre to a search results page geared to receive the field data and spew it out.

SELECT *
FROM tablename
WHERE yourfield = '%#FORM.field1#%' AND yourfield = '%#FORM.field2#%'

Change where you need to... obviously.

Tiggy :cool:

[Walyer]

Thanks, I'll give it a try :D

[Walyer]

This is how I got my search page to work, for those who may need help to do the same thing

Thanks to tigeralex for some much needed information

MAIN SEARCH PAGE

\\Database information for my Artist search
<cfparam name="FORM.Artist" default="1">
<cfquery name="Search_Artist" datasource="MusicList">
SELECT *
FROM MusicList
WHERE Artist LIKE '%#FORM.Artist#%'
</cfquery>

\\Database information for my song Title search
<cfparam name="FORM.Title" default="1">
<cfquery name="Search_Title" datasource="MusicList">
SELECT *
FROM MusicList
WHERE Title LIKE '%#FORM.Title#%'
</cfquery>
....
....
....
\\forms I used search my database

\\Artist search form
<cfform method="post" action="artist.cfm">
<cfinput type="text" name="Artist" required="yes" message="Please enter the Artist name" size="40">
<input name="submit" type="submit">
</cfform>

\\Title search form
<cfform method="post" action="title.cfm">
<cfinput type="text" name="Title" required="yes" message="Please enter the Song Title" size="40">
<input name="submit" type="submit">
</cfform>

OUTPUT FOR SEARCHED DATA

Take your "music.cfm" page and do two "SAVE AS" and rename to "artist.cfm" and "title.cfm". Now add the following code to the bottom of:

"artist.cfm" then re-save

//Displays "Artist" data that was search
<cfloop query="Search_Artist">
<cfoutput># Search_Artist.Artist#</cfoutput>
</cfloop>

//Displays the "Title" information that corresponds with the "Artist" data that was search
<cfloop query="Search_Artist">
<cfoutput># Search_Artist.Title#</cfoutput>
</cfloop>

"title.cfm" then re-save

//Displays "Title" data that was search
<cfloop query="Search_Title">
<cfoutput># Search_Title.Title#</cfoutput>
</cfloop>

//Displays the "Artist" information that corresponds with the "Title" data that was search
<cfloop query="Search_Title">
<cfoutput># Search_Title.Artist#</cfoutput>
</cfloop>

I hope this helps others with the same problem I had

[Torch7]

Also if you wanted to combine both fields to one search you can do <cfif> statements within the query to check for values other than blank.

You can then give searches based on Artist and Title, to give more accurate results.

[Walyer]

Thanks Torch7 for the added info.

Do you think you could show the coding for the <cfif> statement you talked about?

If you could that would be great for the ones that are new to Coldfusion, I'm one of them :D

[Torch7]

Thanks Torch7 for the added info.

Do you think you could show the coding for the <cfif> statement you talked about?

If you could that would be great for the ones that are new to Coldfusion, I'm one of them :D

Sure:

Using the CFIF statements to check against parameters that are defined in the form, and create a Where statement based upon present Parameters.

In ColdFusion there is no need to give a parameter a value, and it can remain blank; which will work to our advantage on this example. So I would remove the 1's from the default values.

<cfparam name="FORM.Artist" default="">
<cfparam name="FORM.Title" default="">

WE CAN NOW Combine the searches Into One Query.

<cfquery name="Search" datasource="MusicList">
SELECT *
FROM MusicList
WHERE 0 = 0
<CFIF Form.Artist IS NOT "">
AND Artist LIKE '%#Form.Artist#%'
</CFIF>

<CFIF FORM.Title IS NOT "">
AND Title LIKE '%#Form.Title#%'
</CFIF>
</cfquery>

Now the Query will search for whatever field is present in the form, and even search for Artist and Title if they are both present...

This could be to your advantage if you have two tracks with the same title, but different artists.

Also... I would get intot the habit of writing my SQL a little differently, because it can get confusing, when the Queries get more complicated.

1.) I would use the tableName.fieldName method to define everything in my query... This cuts down on confusion when you have to select from multiple tables.

IE: SELECT table1.artist, table2.artist, table2.song
FROM table1, table2
WHERE 0 = 0
AND table1.artist = table2.artist;

This is still a pretty simple SQL statement but you see how things could get confusing once you start getting into Inner and Outer joins.

2.) I would define an Application Scope variable for the Datasource Name

IE: <CFQUERY NAME="Query" DataSource="#application.DSN#">
IF you ever begin to write more complicated applications, and the Database name needs to change you change it in one place. The application.cfm

3.) I would get in the habit of using <cfqueryparam> tag around all variables passed to a query for a couple of reasons.
1.) its more secure
2.) it speeds up your query.

IE: WHERE Title LIKE <cfqueryparam value=%#Form.Title#% cfsqltype=cf_sql_char>

I hope my suggestions weren't too weighty...but chime in and I will explain further.