refined search form

Report · Jul 22, 2010

I have a user defined search form that displays dynamic data in a results page table. Once the results have been displayed, I would like to take it a step futher and let the user narrow those results even more. For example, if a user types in Philips, for rsRecord.brandName, it may display 200 records. Then the user could reduce the records more by defining another recordset by a form.

I'm thinking that I would need another form on the results page, but I'm not sure how to store the records already retrieved so that they aren;t lost when the user inputs in the new form. I know this might not ve very clear, so if anyone has any ideas, or needs more info. please let me know. Thanks for the help! here is my search form. if you need my results page let me know and I will post. Thanks!

<!--- 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>Replacement Light Bulb Advanced 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>Advanced Bulb Search</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. The more fields you can fill out, the more refined your search will be.<br />
                 </p></td>
          </tr>
          <tr>
            <th nowrap="nowrap">Item Number:</th>
            <td><input type="text" name="item" size="35" /></td>
            <td nowrap="nowrap">Ex. 75K19</td>
          </tr>
          <tr>
            <th>Brand Name:</th>
            <td><input type="text" name="brandName" size="35" /></td>
            <td nowrap="nowrap">Ex. Eiko</td>
          </tr>
          <tr>
            <th>Base Type:</th>
            <td><input type="text" name="base" size="35" /></td>
            <td nowrap="nowrap">Image</td>
          </tr>
          <tr>
            <th>Glass Type:</th>
            <td><input type="text" name="glass" size="35" /></td>
            <td>Image</td>
          </tr>
          <tr>
            <th>Wattage:</th>
            <td><input type="text" name="wattage" size="35" /></td>
          </tr>
          <tr>
            <th>Voltage:</th>
            <td><input type="text" name="voltage" size="35" /></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>

Report · Jul 22, 2010

<!--- 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>Replacement Light Bulb Advanced 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>Advanced Bulb Search</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. The more fields you can fill out, the more refined your search will be.<br />
                 </p></td>
          </tr>
          <tr>
            <th nowrap="nowrap">Item Number:</th>
            <td><input type="text" name="item" size="35" /></td>
            <td nowrap="nowrap">Ex. 75K19</td>
          </tr>
          <tr>
            <th>Brand Name:</th>
            <td><input type="text" name="brandName" size="35" /></td>
            <td nowrap="nowrap">Ex. Eiko</td>
          </tr>
          <tr>
            <th>Base Type:</th>
            <td><input type="text" name="base" size="35" /></td>
            <td nowrap="nowrap">Image</td>
          </tr>
          <tr>
            <th>Glass Type:</th>
            <td><input type="text" name="glass" size="35" /></td>
            <td>Image</td>
          </tr>
          <tr>
            <th>Wattage:</th>
            <td><input type="text" name="wattage" size="35" /></td>
          </tr>
          <tr>
            <th>Voltage:</th>
            <td><input type="text" name="voltage" size="35" /></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>

hmm ok well twice I have tried posting code..lol I click the >> insert button and post the code and it shows when I paste it but when I click post...nothing there! let me try just pasting.

<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>Replacement Light Bulb Advanced 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>Advanced Bulb Search</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. The more fields you can fill out, the more refined your search will be.<br />
                </p></td>
          </tr>
          <tr>
            <th nowrap="nowrap">Item Number:</th>
            <td><input type="text" name="item" size="35" /></td>
            <td nowrap="nowrap">Ex. 75K19</td>
          </tr>
          <tr>
            <th>Brand Name:</th>
            <td><input type="text" name="brandName" size="35" /></td>
            <td nowrap="nowrap">Ex. Eiko</td>
          </tr>
          <tr>
            <th>Base Type:</th>
            <td><input type="text" name="base" size="35" /></td>
            <td nowrap="nowrap">Image</td>
          </tr>
          <tr>
            <th>Glass Type:</th>
            <td><input type="text" name="glass" size="35" /></td>
            <td>Image</td>
          </tr>
          <tr>
            <th>Wattage:</th>
            <td><input type="text" name="wattage" size="35" /></td>
          </tr>
          <tr>
            <th>Voltage:</th>
            <td><input type="text" name="voltage" size="35" /></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>

Report · Jul 22, 2010

Here is my results page.

<cfquery name="rsSearch" datasource="rlBulbs">
SELECT      a.item,
            b.brandName,
            b.brandID,
   a.base,
   a.glass,
            a.wattage,
            a.voltage,
            a.oldPage
FROM rlbbulbs a,rlbbrand b
WHERE a.brandID = b.brandID

<cfif FORM.item IS NOT "">
AND a.item LIKE '#FORM.item#%'
</cfif>
<cfif FORM.brandName IS NOT "">
AND b.brandName LIKE '%#FORM.brandName#%'
</cfif>
<cfif FORM.base IS NOT "">
AND a.base LIKE '%#FORM.base#%'
</cfif>
<cfif FORM.glass IS NOT "">
AND a.glass LIKE '%#FORM.glass#%'
</cfif>
<cfif FORM.wattage IS NOT "">
AND a.wattage LIKE '#FORM.wattage#'
</cfif>
<cfif FORM.voltage IS NOT "">
AND a.voltage LIKE '%#FORM.voltage#%'
</cfif>

ORDER BY b.brandName
</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>Replacement Bulb Search Results</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 Results</h2>

      
<cfoutput><cfif rsSearch.RecordCount IS ""><p>Total Bulbs Found: #rsSearch.RecordCount#</p></cfif></cfoutput>

<table border="1" cellpadding="4" cellspacing="0">
        <tr>
          <th>Item #</th>
          <th>Brand</th>
          <th>Base</th>
          <th>Glass</th>
          <th>Wattage</th>
          <th>Voltage</th>
          <th>Details</th>
        </tr>
        <cfoutput query="rsSearch">
          <tr>
            <td align="center">#item#</td>
            <td>#rsSearch.brandName#</td>
            <td>#base#</td>
            <td>#glass#</td>
            <td>#wattage#</td>
            <td>#voltage#</td>
            <td><a href="#rsSearch.oldPage#">View Details</a></td>
          </tr>
        </cfoutput>
        <tr>
          <td align="center" colspan="7"><a href="search.cfm">Search Again</a></td>
        </tr>
      </table>
    </div>
</div>

<cfinclude template="footer.cfm">

</div>
</body>
</html>

Report · Jul 23, 2010

You could make your query a session variable.