[deucalion0]

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!

[Craig328]

If you have a form page and an action page the only real issues you have left to deal with is the search of your database table for the appropriate info and how to display it.

In general terms, you're going to need to use CFQUERY to do a SELECT SQL retrieval from your database table. Per your description maybe some clarity is needed here. You have a database. That database contains tables. Those tables contain named columns to properly categorize and organize rows of data. Your search will be against this database table (and not the database).

In your CFQUERY tag you'll need to write the appropriate SQL statement to see if you have what the user is looking for. To that end, you'll need to have some sort of SQL WHERE statement that will require you to supply the formfield values the user gave you to look for. You should include them in your SQL query via the CFQUERYPARAM tag so as to avoid a potential security issue with your database and attendant data. These two tags used together constitute an inline SQL query. You can, if you know how to do so, create a stored procedure on your database and call that stored procedure via the CFSTOREDPROC tag...but that tends to be more advanced database/CF concepts and are likely unnecessary for what you're wanting to do. You can also do a CFINDEX, spider your own data and then do a CFSEARCH against the resultant indexed collection as well.

As in most things in life, there is more than one way to get from A to B. However, for you, it sounds like CFQUERY/CFQUERYPARAM (along with CFOUTPUT of the retrieved query recordset) is your best bet.

Try that and if you need further help, post up your action CFM here and we can see what you might need help with.

Good luck!

[deucalion0]

Craig 328, many thanks for your help I do appreciate it! I have only one table in my databse, it is very simple, but as I new to coldfusion AND databases this get s confusing for me very quickly. I felt I should have been able to simpley create a search box on my web page that a user can type in a keyword and then the results will display on the same page. My database table looks like this:


So using a WHERE query to retrive something from the database, is tricky for me as I tried to do it but couldn't get it to work, is there any chance you could give me an example on how to do this by looking at my table? Also, is what I am trying to achieve something normal? I feel this should be easier than I am making it, it is only a simple keyword search right? I imagined it to be a case of the visitor enters pantera and every table entry with that word in the name will be returned as a result.

I appreciate any help with this!

Many thanks!

[Craig328]

Sure. You said you tried to get a WHERE query to work but it didn't. What specific error message did you get? If no error message, go ahead and post up the code for the .cfm page you used (please use the [CODE] tags to do that) and we can go through it with you.

[deucalion0]

Great! I really appreciate the help!

OK, so here is the url to the first CFM file:

http://0902221.stude....ac.uk/form.cfm

This will show the search box and when a keyword is entered and searched, it opens the search.cfm page and the error is displayed there, you can see for yourself.
The code I have for the form.cfm is this:

<html>
<head>
<title>Search Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
Insert two keywords separated by "+" sign.<br>
<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>
</body>
</html>

The code for the search.cfm is this:

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

<cfif isDefined ("form.keyword")>


<!--- // IF THE KEYWORD SUBMITTED DOES NOT CONTAIN + SIGN THEN JUST DO A BASIC QUERY // --->
<cfif form.keyword DOES NOT CONTAIN "+">
<cfquery name="qUsers" datasource="0902221">
SELECT *
FROM links
WHERE login LIKE '%#form.keyword#%'
</cfquery>

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

<!--- // IF THE KEYWORD DOES CONTAIN A + THEN BREAK THE KEYWORD INTO TWO VARIABLES
AND SEARCH USERS USING "LIKE" & "OR" FUNCTIONS // --->
<cfelse>
<cfset search_1 = trim(ListFirst(form.keyword, '+'))>
<cfset search_2 = trim(Listlast(form.keyword, '+'))>
<cfoutput>Search word 1 is: #search_1#</cfoutput><br>
<cfoutput>Search word 2 is: #search_2#</cfoutput>
<br><br>

<cfquery name="qUsers" datasource="0902221">
SELECT *
FROM links
WHERE login LIKE '%#search_1#%' OR login LIKE '%#search_2#%'
</cfquery>

<cfoutput query="qUsers">#name#<br></cfoutput>

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

I don't remember where I got help to write those files, as I said I was googling for a few days and this is what I came up with, and alas it does not work. Once I do get it to work, then I can create a proper form on my links page for visitors to search the links table by using keywords, well hopefully!

Thanks again for all your help!

[Craig328]

Well, first thing I can see is that your WHERE statement in your queries is searching against links.login (lines 16 and 34 in your posted code)...and if the screenshot you posted up earlier is the database table you're querying against, your links table doesn't have a login column. Try querying it against "band name", "description" or "url" and see what you get.

This post has been edited by Craig328: 02 March 2011 - 10:57 AM

[deucalion0]

i knew that the login was incorrect, although I changed it to url and I received this error:



I don't know what on earth I can do to get around it!

Thanks!

[Craig328]

Well, that would appear to be happening at either line 20 or line 37. To understand why, I have to ask a question: do you know SQL? Specifically, do you know what the "*" means when your SQL reads:

SELECT * FROM links

The star is SQL shorthand for asking for every column back. That same query could also be written like this:

SELECT id, band name, URL, description FROM links

So, when you do this with your CFQUERY tag like you have in your code, that goes out and executes that SQL query and brings back a query recordset. One of the attributes of the CFQUERY tag is "name" and that simply denotes what you can call the returned recordset when it comes back. You can output the query with the CFOUTPUT tag (like you're doing at 20 and 37). The "query" attribute of CFOUTPUT tells CF to loop over the returned query of the same name as many times as records returned for that query. Now, all it does is loop. What actually gets output depends on what you put within the CFOUTPUT tags. Because you've specified qUsers as the query recordset, the CFOUTPUT tag looks for the variable called "name" within your qUsers recordset.

Because "name" wasn't one of the things brought back by the "*" in your query, CF cannot find it, cannot display it and so it throws an error. That said, the error message: "Element NAME is undefined in QUSERS" is pretty self-explanatory.

Now, I noticed the link you posted to your form page is from a school. Typically, DIC is willing to help you to understand how/why you would do something in a school project but usually we won't do it for you. I've explained why you're getting the error and how you came to arrive there. The solution should be fairly easy so I hope you can take it from there insofar as your error is concerned.

If you're still having trouble after this post, please feel free to post back any error message you're getting along with the code that's erroring and we can look into it further.

Good luck!

[deucalion0]

Thanks very much for your help! To answer your question, I did know some SQL, but I have forgotten almost everything, I remebmber the * bring back everyting in a query, that is why I left it there. I am at university, this work is for my coursework, I pride myself on being able to do extra things over and above the coursework specification given to me, I research what I want to achieve then try it over and over until I get there. Asking for help on this forum, is when I get to the stage of feeling like I have hit my head against a brick wall, I never want someone to give me a chunk of code to achieve my goals, but instead provide clues, or explanations as you have done for me! So many thanks, I feel like I have a better idea now, and it's funny I am back on the same path as I was when I set out to do this, sometimes the internet can cause confusion instead of help.

I really need to recap my SQL, I was basically just needing to know if what I wanted to do with Coldfusion was possible as I didn't want to waste anymore time.

Thanks again for your time!

[deucalion0]

I managed to get it to work, on my links page I can now search the database urls and the results work now! I was wondering though, is there a way I can get the search.cfm to open up inside a DIV on my links page? I can change the search.cfm file to look exactly like the links page so as when it loads it appears to have magically placed the results into a reserved space on the page, but I would feel happier having the cfm search results reditrected to my links.html page, I don't even know if this is possible?

I was also wondering, when I get the query results back, the urls, the links, start and end with a hash (#), is there anyway to avoid this, or is this the way it has to be? I was actually wondering if I could have the url's that come back, as actual clickable links, this would be perfect!

My university has taught us very minimal Coldfusion, I guess as it is a dying breed and PHP is the way ahead, but Coldfusion is a great language for the beginner to understand how a web page talks to a database!

Thanks again!

[Craig328]

deucalion0, on 02 March 2011 - 03:53 PM, said:

is there a way I can get the search.cfm to open up inside a DIV on my links page? I can change the search.cfm file to look exactly like the links page so as when it loads it appears to have magically placed the results into a reserved space on the page, but I would feel happier having the cfm search results reditrected to my links.html page, I don't even know if this is possible?

One of my favorite sayings is "nothing is impossible...it's just a matter of how much time do you want to spend on it". In your case though, no, it sounds as though it ought to be quite easy. Without seeing your links page, I can't really tell you how to do it. You have a form.cfm and a search.cfm page posted up. I don't know how you links page interacts with your form.cfm so it's not something I can tell you directly. That said, there's several ways to get query results to show up on a page. If you always want the results from your search.cfm to show up on a links page...you could have the form.cfm submit to the links page and then on the links page you do the query that you're doing on search.cfm in the event that you detect form.keyword (like you did on line 8) and then do the cfoutput loop inside of this div you mentioned. There's slightly more to it than that but that's the basic gist. In short, you'd copy most of what's on search.cfm into your links page and then dump search.cfm altogether.

Quote

I was also wondering, when I get the query results back, the urls, the links, start and end with a hash (#), is there anyway to avoid this, or is this the way it has to be?

Are you saying the outputted content is showing up on your browser with pound signs?

Quote

I was actually wondering if I could have the url's that come back, as actual clickable links, this would be perfect!

Sure. Not everything between cfoutput tags has to be returned query content. On either line 20 or 37 have it do this:

<cfoutput query="qUsers">The value is: #qUsers.name#<br></cfoutput>

That will spit out a text string before each query result value.

Quote

My university has taught us very minimal Coldfusion, I guess as it is a dying breed and PHP is the way ahead, but Coldfusion is a great language for the beginner to understand how a web page talks to a database!

Well, I'm going to admit to significant bias but I much MUCH prefer CF to PHP. It's much quicker to write, way easier to debug, is just as fast (or faster) and is natively more secure. It has 2 major open source platforms (Railo and OpenBD) and it can be deployed pretty much anywhere. For application development it takes much less time to code an app in CF than it does in PHP...and while CF has a robust script form of the language, it also has an equivalent tag based language that interacts seamlessly with script. The nice thing about the tag stuff is that it's much easier to read and follow what's going on.

[deucalion0]

Thanks a lot for all that information! Can I just show you what I have so far? Here is the search.cfm output in the browser:

That is when I use it as a cfm file. I am calling it from the original form.cfm which is in the links html now, and I will show you the code I have now with everything all in 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 --->
				
						<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>

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

I have already accomplished my coursework spec using coldfusion on my registration form page, but I know I can use more coldfusion, but getting it to all come together is prooving tricky. I was searching for help online the past few days and was more often than not told to drop coldfusion as its outdated, I have no opinion being a beginner. My lecturer informed us that it was a great language but if we wanted to use another server side language such as asp or php we could, but I want to make sure I understand Coldfusion before I decied it's not for me. I myslef love the tagging, and how I can merge it in with html pages, well when I get it to work that is!

It is great to have someone like yourself, taking the time to teach a total stranger, it does make all the time I spend worthwhile!

So thanks again!