CF search function
Trying to create a user defined search function and keep getting errors. I wanted a user to type in a search field and the query would return results that match and are also similar to their search criteria. here is the code:
<!--- Get bulbs for Select List --->
<cfquery name="rsBulbs" datasource="rlBulbs">
SELECT item
FROM rlbBulbs
ORDER BY item ASC
</cfquery>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Member Search</title>
<link href="rlb.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="wrapper">
<cfinclude template="header.cfm">
<div id="main">
<div id="sidebar"><cfinclude template="sidebar.cfm"></div>
<div id="content">
<h2>Search for a Member</h2>
<form action="results.cfm" method="post">
<table width="500" border="0" cellpadding="2" cellspacing="0">
<tr>
<td colspan="2" valign="top"><p>Please enter data in one or more of the
fields below and <br />
click the Search button.<br />
</p></td>
</tr>
<tr>
<th>Item Number:</th>
<td><input type="text" name="item" size="50" /></td>
</tr>
<tr>
<th>Base Type:</th>
<td><input type="text" name="base" size="50" /></td>
</tr>
<tr>
<th>Glass Type:</th>
<td><input type="text" name="glass" size="50" /></td>
</tr>
<tr>
<td align="center" colspan="2"><input type="submit" value="Search" />
<input type="reset" value="Reset" name="reset" /> </td>
</tr>
</table>
</form>
</div>
</div>
<cfinclude template="footer.cfm">
</div>
</body>
</html>
here is the results page:
<cfparam name="FORM.item" default="" type="String">
<cfparam name="FORM.base" default="" type="String">
<cfparam name="FORM.glass" default="" type="String">
<!--- Find the Member Record --->
<cfquery name="rsSearch" datasource="rlBulbs">
SELECT id,
item,
base,
glass,
FROM rlbbulbs
WHERE 0 = 0
<cfif item IS NOT "">
AND item LIKE '#item#%'
</cfif>
<cfif base IS NOT "">
AND base LIKE '#base#%'
</cfif>
<cfif glass IS NOT "">
AND glass LIKE '#glass#%'
</cfif>
ORDER BY item
</cfquery>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Members Search Results</title>
<link href="members.css" rel="stylesheet" type="text/css" />
</head>
<body>
<cfinclude template="header-members.cfm">
<h2>Search Results</h2>
<table border="1" cellpadding="4" cellspacing="0">
<tr>
<th>Item</th>
<th>Base</th>
<th>Glass</th>
</tr>
<cfoutput query="rsSearch">
<tr>
<td><a href="details.cfm?id=#rsSearch.id#">#item#</a> </td>
<td><a href="details.cfm?id=#rsSearch.id#">#base#</a> </td>
<td><a href="details.cfm?id=#rsSearch.id#">#glass#</a> </td>
</tr>
</cfoutput>
</table>
<cfinclude template="footer-members.cfm">
</body>
</html>
Can anyone spot whats wrong? I get an error that says something about a SQL syntax error and its point to line 19: "and glass is LIKE '#glass#%'
any help would be greatly appreciated.
AdChoices