Creating a form to search my database by keyword

Question

deucalion0 0 Junior Poster in Training

13 Years Ago

Hey guys, I have been trying for a few days to create a form on a web page where someone can search my database by keyword, I am having many difficulties figuring this out, and Googling help has probably confused me more.

I have a database called links which contains four columns: ID, Band Name, url and Description. There are 25 rows of information, and I want to be able to search the database by keyword, say for example youtube, pantera or images.

I created two CFM files, one for searching and one to do the search, ulimately I want to link this to my Links page where a search box has the action to call the CFM page that does the search. I am really struggling to get anything to work, if anyone can give me a clue as to how I can do this I would be grateful!
I can already perform a simple query from my Access database which is on a server, but thats as far as I got.

Thanks to anyone who can help me out and perhaps make these past few days worth the effort!

coldfusion

2 Contributors
19 Replies
639 Views
3 Days Discussion Span
Latest Post 13 Years Ago Latest Post by arrgh

arrgh 22 Posting Whiz

13 Years Ago

Post a small sample of what you've tried and what it's doing wrong/not doing.

arrgh 22 Posting Whiz

13 Years Ago

I need the results in url form not text

Use cfoutput to display the query, and use the columns to construct a link ie <a href="(link url)">link title</a>. Though URL is a reserved word in CF. You might get weird results if you use just #url#. So think about changing the name your "url" column.

<cfoutput query="qUsers">
       <a href="#qUsers.url#">#qUsers.url#</a>
   </cfoutput>

stay in the links.html page

CF code can only be executed in a .cfm page. So you'd need somethin like jquery or iframes to do that.

Edited 13 Years Ago by arrgh because: n/a

arrgh 22 Posting Whiz

13 Years Ago

when i do the search, it has all these # at the start and end i dont know how to get rid of them

Sounds like the code isn't even being executed. It's just being displayed like html. Either the code isn't inside a .CFM script or maybe your server isn't set up properly. Are you sure the CF server is installed correctly?

arrgh 22 Posting Whiz

13 Years Ago

Now I can see the results at least :) From the output, it looks like the url addresses are messed up. Almost as if the db value had extra # signs in it. Like "#http://en.wikipedia.org/wiki/In_Flames#" instead of just "http://en.wikipedia.org/wiki/In_Flames"

CFdump the query on at the top of search.cfm so we can see the raw values.

<cfdump var="#qUsers#">

Edited 13 Years Ago by arrgh because: n/a

arrgh 22 Posting Whiz

13 Years Ago

I can see the dump. The values do have extra # signs in them. That's why they look strange. The code adding inserting them into your db is probably wrong (ie has too many # signs somewhere). Can you post that code?

You can clean up the existing values w/a query. What db type are you using?

Edited 13 Years Ago by arrgh because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

deucalion0 0 Junior Poster in Training · Answer 1 · 2011-03-03T15:55:13+00:00

Hi Arghh thanks for your reply. What I have is a form on my links page calling search.cfm, which does a query but I need the results in url form not text and to stay in the links.html page instead of opening a new page search.cfm. Here is the code for the search file:

<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

<cfif isDefined ("form.keyword")>

<!--- // KEYWORD SEARCH TO PERFORM THE QUERY // --->

<cfquery name="qUsers" datasource="0902221">
SELECT *
FROM links
WHERE url LIKE '%#form.keyword#%'
</cfquery>

<p>The name you choose was:</p>
<cfoutput query="qUsers">#qUsers.url#<br></cfoutput>

</cfif>
</body>
</html>

Here is the links.html:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">

	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="description" content="Index page of Top five bands website" />
		<meta name="keywords" content="top, five, bands, pantera, rammstein, in flames, children of bodom, 69 eyes" />
		<meta name="language" content="en"/>
		<meta name="viewport" content="width=480" />
		<title>Page One</title>	
		<link rel="stylesheet" href="style.css" type="text/css" />
		<!--[if IE]> <link rel="STYLESHEET" type="text/css" href="master.css" title="Normal browsers"/> <![endif]-->
		<script type="text/javascript"  src="script.js"></script>
	</head>

	<body> 
	
		<div id="wrapper">
				
			<div id="header3" title="Home page banner graphic" >
				<h1 class="hidden">Links</h1>
			</div>
			
			<div id="jukeboxbar">
				<div id="jukebox">
			<!--[if !IE]>-->	<OBJECT id="Player"type="application/x-ms-wmp"width="300" height="60" >
					
						<PARAM NAME="URL" VALUE="playlist.asx">
						<PARAM NAME="SendPlayStateChangeEvents" VALUE="True">
						<PARAM NAME="AutoStart" VALUE="False">
						<PARAM name="uiMode" value="Full">
						<PARAM name="PlayCount" value="9999">
					</OBJECT> <!--<![endif]--> 
					
					
					
						<OBJECT id="Player"type="application/x-ms-wmp"width="300" bgcolor="darkblue" height="60" CLASSID="CLSID:6BF52A52-394A-11d3-B153-00C04F79FAA6">
					
						<PARAM NAME="URL" VALUE="playlist.asx">
						<PARAM NAME="SendPlayStateChangeEvents" VALUE="True">
						<PARAM NAME="AutoStart" VALUE="False">
						<PARAM name="uiMode" value="Full">
						<PARAM name="PlayCount" value="9999">
					</OBJECT>
				</div>
			</div>	
			
			<div id="navigation">
				<ul id="nav">
					<li><a href="topFive.html" tabindex="1" accesskey="Z">Top Five</a></li>
					<li><a href="index.html" tabindex="2" accesskey="X">Home</a></li>
					<li><a href="join.html" tabindex="3" accesskey="C">Join</a></li>
					<li><a href="index.html" tabindex="4" accesskey="V">Home</a></li>
				</ul>
			</div>

			<div id="main">
			<br/>
				<p>Welcome to the top five metal bands!</p>
						
				<p>Welcome to the top five metal bands! Welcome to the top five metal bands! Welcome to the top five metal bands! Welcome to the top five metal bands!</p>
						
				<p>Welcome to the top five metal bands! Welcome to the top five metal bands! Welcome to the top five metal bands! Welcome to the top five metal bands!</p>
						
			
			</div>
			
			<div id="mainleft">	<!--- THIS IS THE FORM.CFM --->
					<form action="search.cfm" method="post" name="Search" id="Search">
					<input name="keyword" type="text" id="keyword">
					<input type="submit" name="Submit" value="GO">
					</form>	
						
			</div>
				
				<div id="mainright">	<!--- THIS IS THE SEARCH.CFM --->
				
						
				<!--- THIS IS WHERE I NEED THE SERACH RESULTS TO DISPLAY --->
					

						
			</div>
				
			<div id="footer">
				<p class="hidden"> Conformance: XHTML 1.0 Strict | Copyright © 2010 R.Leadingham.</p>	
			</div>
			
		</div>
	</body>
</html>

Here is what happens when I get my serach results back:
http://0902221.studentweb.abertay.ac.uk/1.jpg

Here is what my table looks like:
http://0902221.studentweb.abertay.ac.uk/links.jpg

Thanks for any help whatsoever!

deucalion0 0 Junior Poster in Training · Answer 2 · 2011-03-04T19:20:51+00:00

Thanks a lot for those tips Arghh! i have a question if you don't mind. Here is my search.cfm code with two versions of cfoutput, I will show you what I get in the browser when i do the search, it has all these # at the start and end i dont know how to get rid of them, I changed thr url column to address as you suggested but no change.

The cfm code:

<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

<cfif isDefined ("form.keyword")>

<!--- // KEYWORD SEARCH TO PERFORM THE QUERY // --->

<cfquery name="qUsers" datasource="0902221">
SELECT *
FROM links
WHERE  address LIKE '%#form.keyword#%' OR description LIKE '%#form.keyword#%'
</cfquery>

   
      
   
<cfoutput query="qUsers"><a href="#qUsers.address#">#qUsers.address#, The description of the links are: #qUsers.description# </a></cfoutput>
   
      

<cfoutput query="qUsers"><p>The links based on your keyword are:</p>#qUsers.address#,	The description of the links are: #qUsers.description# <br></cfoutput>

</cfif>
</body>
</html>

A screen shot of teh results page:
http://0902221.studentweb.abertay.ac.uk/help.png

I hope that you can help me out, if I can figure this out then I can to proceed to build a better search.cfm page.

Many thanks!!!!!

deucalion0 0 Junior Poster in Training · Answer 3 · 2011-03-05T01:02:05+00:00

Hi Arrgh, I know CF is configured on the server they taught us some CF on this server and it all worked OK, I have tried to get this working all day long. If you go ot this page and type pantera in the search box, you will see how the results appear weird with all these # plus they do not work.

http://0902221.studentweb.abertay.ac.uk/links.html

Here is the latest code I have for running the serach something in here is not right and I have no idea what it is:

<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

<cfif isDefined ("form.keyword")>

<!--- // KEYWORD SEARCH TO PERFORM THE QUERY // --->

<cfquery name="qUsers" datasource="0902221">
SELECT *
FROM links
WHERE  address LIKE '%#form.keyword#%' 
</cfquery>


<p>The links based on your keyword are:</p>
<ul>
<CFOUTPUT QUERY="qUsers">
<li><a href="#qUsers.address#">#qUsers.address#</a></li>
</CFOUTPUT>
</ul>
   
      
   


</cfif>
</body>
</html>

If you have any suggestions please let me know I am ripping my hair out.

Thank you!!

deucalion0 0 Junior Poster in Training · Answer 4 · 2011-03-05T02:27:44+00:00

OK arrgh I have done as you asked :)

I never knew where to put the code I just put it everywhere until it worked! :)

So hopefully that page with weird stuff is what you were looking for, it does have a lot of information!

Thanks for all the tips and information!!

:)

deucalion0 0 Junior Poster in Training · Answer 5 · 2011-03-05T02:38:13+00:00

I am using Access and I catually created this database using access and all the information in the columns was entered when I created the database, I set the column address to url type. Something interesting, I just built a little form to add urls to the site and violia they work when I add them via the form, how on earth id this happen? I can basically re enter everyting in my databse by using this form, but why didn't it work when it was entered manualy into accesss?

Do a search for mod and modmyi website url appears and it works!

At last I got this to work, what a pain it has been, so thanks for your help on this !!!!!!

:)

arrgh 22 Posting Whiz · Answer 6 · 2011-03-05T02:59:53+00:00

the column address to url type

Well that's a new one. I've never used it. I checked ... and seems that's what's adding the # signs. Not sure what the best answer is here. Normally I'd say use "text". But it's max is 255 chars. Not long enough for url's. "memo" is an option, but it's got some limitations too.

deucalion0 0 Junior Poster in Training · Answer 7 · 2011-03-05T03:01:44+00:00

Well Arrgh thanks for your support on this, helped me a lot!

Cheers!!!

:)

arrgh 22 Posting Whiz · Answer 8 · 2011-03-05T03:04:39+00:00

Do a search for mod and modmyi website url appears and it works!

Great!

Just to make it more user-friendly, you might default to (show all records) if the text field is left blank though.

deucalion0 0 Junior Poster in Training · Answer 9 · 2011-03-05T03:40:18+00:00

Yeah mate I'll do that and if you have any other tips I'll be glad to hear them, I'm a beginner at this but I love learning and getting things working! I tried using memo but the existing records still had the # so I reckon it is some problem between access and coldfusion at least I'll know for the future!

Cheers!

;)

arrgh 22 Posting Whiz · Answer 10 · 2011-03-05T04:28:30+00:00

Yeah, a straight conversion won't remove the # signs that are already there. You'd need to run an UPDATE to get rid of them. Just find records starting or ending with # and remove them. Not tested. So don't use it on a live table without a back up ;-)

<!--- starts with --->
UPDATE YourTableName
SET    ColumnName = Right( ColumnName, Len(ColumnName)-1)
WHERE  ColumnName LIKE '##%'

<!--- ends with --->
UPDATE YourTableName
SET    ColumnName = Left( ColumnName, Len(ColumnName)-1)
WHERE  ColumnName LIKE '%##'

From what I could tell it's Access that adds the #'s when the "hypertext" data type is used. Weird. Unless there's a built-in function that returns the url without the # signs, I'd probably avoid that data type. Too bad though, perfect in every other way.

deucalion0 0 Junior Poster in Training · Answer 11 · 2011-03-05T15:39:00+00:00

Yeah I just created a new links table and used hyperlink as the type and manually added all my links again via the form on my links page and searched for them and tested them and they work! So Everything is fine, but I have a question for you, I noticed I am doing a query where the keyword matches an address word, which is the url, it will then output the address it found as a url, what I discovered I actually need is to search the column band and THEN out put the address of that, as some of the urls do not have a the band name Children of bodom is sometimes abbreviated as COB, so this url is not found.

Any clues as to how I can query one column, then out a different column, so search the bands then output the address for the result? I am messing about but getting nowhere with it, any help is appreciated Arrgh and cheers for the code above, that will come in handy!!
:)

arrgh 22 Posting Whiz · Answer 12 · 2011-03-06T02:39:34+00:00

Sure you can do that. You can query 1 column, but return totally different columns in your SELECT list. Just do what you described, ie Filter on the "Band" column and return the Band and Address in the SELECT list.

SELECT Band, Address
FROM   YourTable
WHERE Band LIKE '%#form.keyword#%'

Then you can use either or both in your output. ie Use "Address" for the link href and Band for the link title

<a href="#address#">Band Name: #Band# URL: #Address#</a>

deucalion0 0 Junior Poster in Training · Answer 13 · 2011-03-06T04:09:53+00:00

Thanks Arrgh, I tried this and it didn't work, the code you gave me doesn't work either, I get an error as soon as I change address to band after WHERE

This is part of the error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'band'.

I don't know why it works with one column but not another? Any ideas mate?

Thanks again, I appreciate it!
:)

arrgh 22 Posting Whiz · Answer 14 · 2011-03-06T07:24:46+00:00

That was just psuedo-sql ;-) Though ... it should work ok. Notice it's almost the same as your original, aside from the table name

Can you post the actual query you used?

psuedo-sql

SELECT Band, Address
FROM   YourTable
WHERE  Band LIKE '%#form.keyword#%'

original

SELECT *
FROM   links
WHERE  url LIKE '%#form.keyword#%'