macromedia.coldfusion.database_access
[Top] [All Lists]
<prev [Date] next>
Google
 
<prev [Thread] next>

Searching the User List using <cfquery>

from [lunamana]
Subject: Searching the User List using <cfquery>
From: "lunamana" <webforumsuser@xxxxxxxxxxxxxx>
Date: Thu, 17 Jan 2008 20:20:05 +0000 (UTC)
Newsgroups: macromedia.coldfusion.database_access 

I have a inventory web site, and I'm trying to create multiple search engine, 
so I have a lot of text boxes and search buttons in one page and I wanna be 
able to search by each category. I'm suppose to have 121 records in my 
database, but it shows only 71 records after I did syntax...probably, I'm using 
AND, but if I changed to OR, the search doesn't work...Here is my code...

 <cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">
   <cfset 
MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>
   <cfset 
MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_U
serAuthorization",DE(""))>
 </cflock>
 <cfif MM_Username EQ "" OR MM_UserAuthorization EQ "" OR 
ListFind("admin,user",MM_UserAuthorization) EQ 0>
   <cfset MM_referer=CGI.SCRIPT_NAME>
   <cfif CGI.QUERY_STRING NEQ "">
     <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
   </cfif>
   <cfset MM_failureURL="search_parts.cfm?accessdenied=" & 
URLEncodedFormat(MM_referer)>
   <cflocation url="#MM_failureURL#" addtoken="no">
 </cfif>
 <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
 <cfparam name="PageNum_rs_parts" default="1">
 <cfparam name="URL.search_string" default="" />
 <cfparam name="URL.search_string2" default="" />
 <cfparam name="URL.search_string3" default="" />
 <cfparam name="URL.search_string4" default="" />
 <cfparam name="URL.search_string5" default="" />
 <cfparam name="URL.search_string6" default="" />
 <cfparam name="URL.search_string7" default="" />
 <cfparam name="URL.search_string8" default="" />
 <cfparam name="URL.search_string9" default="" />
 <cfparam name="URL.search_string10" default="" />


 <cfquery name="rs_parts" datasource="acca">
 SELECT *
 FROM acca.parts
 WHERE parts.parts_id = parts.parts_id
 AND (parts.no LIKE '%#URL.search_string#%')
 AND (parts.item_name LIKE '%#URL.search_string2#%')
 AND (parts.product LIKE '%#URL.search_string3#%')
 AND (parts.version_spec LIKE '%#URL.search_string4#%')
 AND (parts.mfr_number LIKE '%#URL.search_string5#%')
 AND (parts.price LIKE '%#URL.search_string6#%')
 AND (parts.status LIKE '%#URL.search_string7#%')
 AND (parts.owned_by LIKE '%#URL.search_string8#%')
 AND (parts.notes1 LIKE '%#URL.search_string9#%')
 AND (parts.type LIKE '%#URL.search_string10#%')


 </cfquery>
 <cfset MaxRows_rs_parts=5>
 <cfset 
StartRow_rs_parts=Min((PageNum_rs_parts-1)*MaxRows_rs_parts+1,Max(rs_parts.Recor
dCount,1))>
 <cfset 
EndRow_rs_parts=Min(StartRow_rs_parts+MaxRows_rs_parts-1,rs_parts.RecordCount)>
 <cfset TotalPages_rs_parts=Ceiling(rs_parts.RecordCount/MaxRows_rs_parts)>
 <cfset QueryString_rs_parts=Iif(CGI.QUERY_STRING NEQ 
"",DE("&"&XMLFormat(CGI.QUERY_STRING)),DE(""))>
 <cfset 
tempPos=ListContainsNoCase(QueryString_rs_parts,"PageNum_rs_parts=","&")>
 <cfif tempPos NEQ 0>
   <cfset QueryString_rs_parts=ListDeleteAt(QueryString_rs_parts,tempPos,"&")>
 </cfif>
 <!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";><!-- InstanceBegin 
template="/Templates/acca_inventory.dwt" codeOutsideHTMLIsLocked="false" -->
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=shift_jis" />
 <!-- InstanceBeginEditable name="doctitle" -->
 <title>Welcome to Inventory Web Site</title>
 <!-- InstanceEndEditable -->
 <style type="text/css">
 td img {display: block;}
 body {
        background-image: url();
        margin-left: 0px;
        margin-top: 0px;
        margin-right: 0px;
        margin-bottom: 0px;
        background-color: #1A0E7E;
 }
 body,td,th {
        font-family: Arial, Helvetica, sans-serif;
        color: #CCCCCC;
        font-size: 14px;
 }
 a:link {
        text-decoration: none;
        color: #FFFF00;
 }
 a:visited {
        text-decoration: none;
        color: #FFFF00;
 }
 a:hover {
        text-decoration: underline;
        color: #20A2BA;
 }
 a:active {
        text-decoration: none;
        color: #20A2BA;
 }
 .style19 {
        font-size: 12px;
        color: #FFFF00;
 }
 .style21 {color: #29B8C9}
 .style24 {font-size: 12px; color: #2093BF; }
 </style>
 <!-- InstanceBeginEditable name="head" -->
 <style type="text/css">
 <!--
 .style2 {      color: #29B8C9;
        font-weight: bold;
 }
 .style20 {     color: #FFFF00;
        font-weight: bold;
 }
 .style23 {color: #CCCCCC}
 .style24 {
        color: #45B0DC;
        font-weight: bold;
 }
 -->
 </style>
 <!-- InstanceEndEditable -->
 </head>

 <body>
   <table width="100%" border="0" cellspacing="0" cellpadding="0">
     <tr>
       <td><table width="800" border="0" align="center" cellpadding="0" 
cellspacing="0">
         <!-- fwtable fwsrc="inventory_raw.png" fwbase="inventory_raw.jpg" 
fwstyle="Dreamweaver" fwdocid = "1794558747" fwnested="0" -->
         <tr>
           <td><img src="spacer.gif" width="48" height="1" border="0" alt="" 
/></td>
           <td><img src="spacer.gif" width="752" height="1" border="0" alt="" 
/></td>
           <td><img src="spacer.gif" width="1" height="1" border="0" alt="" 
/></td>
         </tr>
         <tr>
           <td colspan="2"><img src="inventory_raw_r1_c1.jpg" alt="" 
name="inventory_raw_r1_c1" width="800" height="207" border="0" 
usemap="#inventory_raw_r1_c1Map" id="inventory_raw_r1_c1" /></td>
           <td><img src="spacer.gif" width="1" height="207" border="0" alt="" 
/></td>
         </tr>
         <tr>
           <td><img name="inventory_raw_r2_c1" src="inventory_raw_r2_c1.jpg" 
width="48" height="393" border="0" id="inventory_raw_r2_c1" alt="" /></td>
           <td valign="top" bgcolor="#180e7f"><span class="style19"><a 
href="display_pc&amp;prt.cfm">PC&amp;PRT</a> ( <a 
href="display_pc&amp;prt.cfm">View</a> || <a 
href="insert_pc&amp;prt.cfm">Insert</a> ) <strong>::</strong> <a 
href="display_software.cfm">Software</a> ( <a 
href="display_software.cfm">View</a> || <a 
href="insert_software.cfm">Insert</a> ) <strong>::</strong><a 
href="display_acca_pc&amp;prt.cfm"> ACCA PC&amp;PRT</a> ( <a 
href="display_acca_pc&amp;prt.cfm">View</a> || <a 
href="insert_acca_pc&amp;prt.cfm">Insert</a> )<strong> ::</strong><a 
href="display_radio.cfm"> Radio</a> ( <a href="display_radio.cfm">View</a> || 
<a href="insert_radio.cfm">Insert</a> )<strong> ::</strong> <a 
href="diaplay_parts.cfm">Parts</a> ( <a href="diaplay_parts.cfm">View</a> || <a 
href="insert_parts.cfm">Insert</a> )</span><br />
               <span class="style21"><a href="logout.cfm">Logout</a></span><br 
/>
           <!-- InstanceBeginEditable name="info" --><span class="style24">Parts
             </span>
                <cfoutput>
           <form action="#CGI.SCRIPT_NAME#" method="get">
             <table width="100%" border="1" cellpadding="0" cellspacing="0" 
bordercolor="##CCCCCC">
               <tr>
                 <td colspan="2" bgcolor="##333333"><span 
class="style20">Inventory-in</span></td>
                 <td colspan="2" bgcolor="##666666"><span 
class="style2">Inventory-out</span></td>
               </tr>
               <tr>
                 <td width="15%" bgcolor="##333333"><span class="style23">
                   <label>No.:</label>
                 </span></td>
                 <td width="32%" bgcolor="##333333"><input type="text" 
name="search_string" />
                   <input type="submit" name="search_button" value="Search" 
/></td>
                 <td width="18%" bgcolor="##666666"><span class="style23">Work 
Reference No.:</span></td>
                 <td width="35%" bgcolor="##666666"><input type="text" 
name="search_string11" /> <input type="submit" name="search_button11" 
value="Search" /></td>
               </tr>
               <tr>
                 <td bgcolor="##333333"><span class="style23">Item Name : 
</span></td>
                 <td bgcolor="##333333"><span class="style23">
                   <label></label>
                   <input type="text" name="search_string2" />
                   <input type="submit" name="search_button2" value="Search" />
                 </span></td>
                 <td bgcolor="##666666"><span 
class="style23">Purpose:</span></td>
                 <td bgcolor="##666666">&nbsp;</td>
               </tr>
               <tr>
                 <td bgcolor="##333333"><span 
class="style23">Product:</span></td>
                 <td bgcolor="##333333"><span class="style23">
                   <label></label>
                   <input type="text" name="search_string3" />
                   <input type="submit" name="search_button3" value="Search" />
                 </span></td>
                 <td bgcolor="##666666"><span class="style23">Date:</span></td>
                 <td bgcolor="##666666">&nbsp;</td>
               </tr>
               <tr>
                 <td bgcolor="##333333"><span 
class="style23">Version/Spec.:</span></td>
                 <td bgcolor="##333333"><span class="style23">
                   <label></label>
                   <input type="text" name="search_string4" />
                   <input type="submit" name="search_button4" value="Search" />
                 </span></td>
                 <td bgcolor="##666666"><span class="style23">Ck1:</span></td>
                 <td bgcolor="##666666">&nbsp;</td>
               </tr>
               <tr>
                 <td bgcolor="##333333"><span class="style23">Mfr. Number 
:</span></td>
                 <td bgcolor="##333333"><span class="style23">
                   <label></label>
                   <input type="text" name="search_string5" />
                   <input type="submit" name="search_button5" value="Search" />
                 </span></td>
                 <td bgcolor="##666666"><span class="style23">Ck2:</span></td>
                 <td bgcolor="##666666">&nbsp;</td>
               </tr>
               <tr>
                 <td bgcolor="##333333"><div align="left" 
class="style23">Price:<strong> $</strong></div></td>
                 <td bgcolor="##333333"><span class="style23">
                   <label></label>
                   <input type="text" name="search_string6" />
                   <input type="s
[More with this subject...]
<Prev in Thread] Current Thread [Next in Thread>
Previous by Date:  Unused IP addresses being used by jrun.exe, operator43
Next by Date:  CF8 Standard and Oracle, ccwebman
Previous by Thread:  Global Script Protect , data uploading, Robert032
Next by Thread:  Re: Searching the User List using <cfquery>, Dan Bracuk
Indexes:  [Date] [Thread] [Top] [All Lists]