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!
Hi deucalion0, welcome to SPF! This doesn’t sound to complicated. Let me rephrase but please correct me if I’m wrong. You have a serach page and a result page. You would like to search on keywords. In that case the query on the result page would look something like below using wildcards:
<cfquery name="getRecords" datasource="#dsn#">
SELECT
field1
, field2
, field3
FROM
yourTable
WHERE (
field1 LIKE '%#Form.txtSearch#%'
OR field2 LIKE '%#Form.txtSearch#%'
OR field3 LIKE '%#FORM.txtSearch#%'
)
</cfquery>
Hey guys thanks for your replies and informations!
BAsically I have a web page that is called links, on this page I have a form box where a visitor can search my database table called links by band name. I want the results to appear on the links page next to the serach box, in the form of links so the visitor can click them and then the browser opens in a new page.
So far I have the search box on my links page, when I do a search it opens the search.cfm and shows the results in the browser but not as links, just text. I will show you the code I have for the form.cfm page and the search.cfm page, and then how I have it in the links page, I still don’t know how to get the serach.cfm to work within the links.html, I’ve tried everything.
Here is the code:
search.cfm:
<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 what the search results look like at the moment:
If anyone has any suggestions on how I can get the query to bring back the description information along with the url information from th elinks table all in the links page, I would be grateful, I have spent a week on this and it’s starting to feel like a wast of time.
Much appreciated!!!
I thought I may add an image of what my database looks like:
First of all as Rudy already mentioned, the wild cards for access are * instead of %. For more on widcard characters in Access you could read this.
Secondly, since you only want to return the url from your table your query shoud read:
<cfquery name="qUsers" datasource="0902221">
SELECT
url
FROM
links
<cfif structKeyExists( Form, 'keyword' )>
WHERE
url LIKE '*#form.keyword#*'
</cfif>
</cfquery>
notice a few things. Instead of using the SELECT * (SELECT ALL) I used SELECT url, since that is the only output you need after all. Try not to use the SELECT * statement, but instead use the fields you would like to output. I furthermore replaced isDefined with structKeyExists. structKeyExists test for a specific variable defined in a specific scope where isDefined, If you would forget the scope definition form, would search in many different scopes. I furthermore replaced the MySQL widcards with Access wildcards.
Hey guys thanks for all the input! I have taken on board the advice and altered my code, my search.cfm file now has two cfoutputs as I want to output in url format, I have left both in to show you what the actual output looks like. here is 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>
I need the % as when I use * it does nothing. Here is what the results look like, I cannot get rid of all the #
Any help with getting the results to come back as clickable urls will be appreciated!!!
<p>The links based on your keyword are:</p>
<ul>
<CFOUTPUT QUERY="qUsers">
<li><a href="#qUsers.address#">#qUsers.description#</a></li>
</CFOUTPUT>
</ul>
Thanks a lot r937, that was excellent! I feel I almost there, the page looks better, the way it should but the actual links when clicked on do nothing but the url of the page changes though, like this: Search Results
This is what happens with all the links when i click on them, is this something to do with my database? I do apprecaite your help, I am totally stuck!
I apologise for that misunderstanding, I changed the column name from url to address as I was advised by someone that the url name may be causing me issues due to it perhaps being a reserved word in ColdFusion. I do have address in my table instead of url, sorry about the confusion!
I still can’t see how my links are the search.cfm url THEN the link url name, its so confusing!
My last question was when I do the search on my links page the search.cfm is called and brings back the results as links as I wanted but the links do not work, all that happens when I click on them is the url bar in the browser changes, I’ll post a pic of it.
I have no idea wht the urls are not hyperlinks to the actual page, this is strange! I appreciate your expertise on this, I’d be lost without it!
here is what it looks like:
I hope you can make out from the image the issue I am having.
well, it’s certainly no mystery why the links aren’t working
here’s what they look like –
<p>The links based on your keyword are:</p>
<ul>
<li><a href="#http://www.pantera.com/#">Pantera official website</a></li>
<li><a href="#http://en.wikipedia.org/wiki/Pantera#">Pantera Wikipedia website</a></li>
<li><a href="#http://www.myspace.com/pantera#">Panter mySpace website</a></li>
<li><a href="#http://www.google.co.uk/images?q=pantera&oe=utf-8&rls=org.mozilla:en-GB:official&client=firefox-a&um=1&ie=UTF-8&source=og&sa=N&hl=en&tab=wi&biw=1920&bih=819#">Pantera Google images</a></li>
<li><a href="#http://www.youtube.com/user/pantera?feature=chclk#">Pantera youTube channel</a></li>
</ul>
now all you have to do is track down where those octothorps (pound or hash signs) are coming from
Ok, no problem I saw that too I had no idea how they got there. Here is the CFM code for the search:
<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>
<p>The links based on your keyword are:</p>
<ul>
<CFOUTPUT QUERY="qUsers">
<li><a href="#qUsers.address#">#qUsers.description#</a></li>
</CFOUTPUT>
</ul>
</cfif>
</body>
</html>
It is my understanding that this the only piece of code that interacts with the search, the form on the links page only takes a keyword, but here is the form code anyway just in case:
<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>
Again, much appreciated having your time on this issue!!
