Suchfunktion in Coldfusion
Verfasst: 01.06.2004, 17:58
Hallo zusammen, ich bin gerade dabei an einer Übung, ich habe ein Formular in das ich Personalien von Kunden in eine Datenbank speichere.
So dass ich mit einer Suchfunktion (LIKE) nach Kriterien suchen kann.
Aber irgend wie bringe ich es nicht fertig, kann mir irgend jemand dabei helfen?
Vieln Dank für eure Hilfe.
Gruss Alesis
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form">
<cfquery datasource="kundendata" username="root" password="root">
INSERT INTO kunden (firma, name, vorname, adresse, email, tel, faxnr, ort, plz) VALUES
(
<cfif IsDefined("FORM.firma") AND #FORM.firma# NEQ "">
'#FORM.firma#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.name") AND #FORM.name# NEQ "">
'#FORM.name#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.vorname") AND #FORM.vorname# NEQ "">
'#FORM.vorname#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.adresse") AND #FORM.adresse# NEQ "">
'#FORM.adresse#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.email") AND #FORM.email# NEQ "">
'#FORM.email#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.tel") AND #FORM.tel# NEQ "">
'#FORM.tel#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.faxnr") AND #FORM.faxnr# NEQ "">
'#FORM.faxnr#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.ort") AND #FORM.ort# NEQ "">
'#FORM.ort#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.plz") AND #FORM.plz# NEQ "">
#FORM.plz#
<cfelse>
NULL
</cfif>
)
</cfquery>
<cflocation url="index.cfm">
</cfif>
<cfquery name="abfrage" datasource="kundendata" password="root" username="root">
select *
from kunden
where 0=0
<cfif #name# is NOT "">
AND name LIKE '%#name#%'
</cfif>
<cfif #vorname# is NOT "">
AND vorname LIKE '%#vorname#%'
</cfif>
<cfif #adresse# is NOT "">
AND adresse LIKE '%#adresse#%'
</cfif>
<cfif #email# is NOT "">
AND email LIKE '%#email#%'
</cfif>
<cfif #tel# is NOT "">
AND tel LIKE '%#tel#%'
</cfif>
<cfif #faxnr# is NOT "">
AND faxnr LIKE '%#faxnr#%'
</cfif>
<cfif #datum# is NOT "">
AND datum LIKE '%#datum#%'
</cfif>
<cfif #ort# is NOT "">
AND ort LIKE '%#ort#%'
</cfif>
<cfif #plz# is NOT "">
AND plz LIKE '%#plz#%'
</cfif>
<cfif #firma# is NOT "">
AND firma LIKE '%#firma#%'
</cfif>
<cfif #kunde_id# is NOT "">
AND kunde_id LIKE '%#kunde_id#%'
</cfif>
</cfquery>
<cfif ABFRAGE.RECORDCOUNT IS 0>
Es wurden keine passenden Datensätze gefunden<br>
<cfelse>
<cfoutput>#abfrage.RecordCount#</cfoutput>Datensätze gefunden:<br>
<cftable query="abfrage" HTMLTABLE>
<cfcol header="vorname" text="<i>#vorname#</i>">
<cfcol header="name" text="<i>#name#</i>">
<cfcol header="adresse" text="#adresse#">
<cfcol header="email" text="#email#">
<cfcol header="tel" text="#tel#">
<cfcol header="datum" text="#datum#">
<cfcol header="ort" text="#ort#">
<cfcol header="plz" text="#plz#">
<cfcol header="firma" text="#firma#">
<cfcol header="kunde_id" text="#kunde_id#">
</cftable>
</cfif>
<html>
<head>
<title>publit_offerte</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css.css" rel="stylesheet" type="text/css">
<link href="css_formular.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#E0E0E0" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="145" colspan="2" bgcolor="#999999"> <div align="center">
<form name="form" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
<table width="1000" cellpadding="1" cellspacing="1" bordercolor="#666699">
<tr>
<td width="120" height="26" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Firma:</div></td>
<td colspan="5" bgcolor="#5EBBCC"> <input type="text" name="firma" size="28">
<span class="titelbar">°</span></td>
</tr>
<tr>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Name:
</div></td>
<td width="219" bgcolor="#99CCCC"> <input type="text" name="name" size="28">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Adresse:</div></td>
<td width="290" bgcolor="#99CCCC"> <input type="text" name="adresse" size="28">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Natel
oder Fixnr.</div></td>
<td width="145" bgcolor="#99CCCC"> <input type="text" name="tel" size="14">
<span class="titelbar">°</span></td>
</tr>
<tr>
<td width="120" height="24" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Vorname:</div></td>
<td bgcolor="#99CCCC"><input type="text" name="vorname" size="28">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">PLZ
/ Ort:</div></td>
<td bgcolor="#99CCCC"> <input type="text" name="plz" size="5"> <input type="text" name="ort" size="22">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Faxnr.</div></td>
<td bgcolor="#99CCCC"><input type="text" name="faxnr" size="14"></td>
</tr>
<tr bgcolor="#CCCCCC">
<td colspan="3"> </td>
<td bgcolor="#CCCCCC"> </td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">E-Mail:</div></td>
<td bgcolor="#99CCCC"><input type="text" name="email" size="20"></td>
</tr>
<tr bgcolor="#CCCCCC">
<td colspan="3"><div align="right"></div>
<div align="left" class="inhalt">Die Markierten Felder (<span class="titelbar">°</span>)
sind muss Felder!</div></td>
<td colspan="3" bgcolor="#999999"> <input type="submit" name="Submit1" value="save">
<input type="reset" name="Submit2" value="clear"></td>
</tr>
</table>
<input type="hidden" name="MM_InsertRecord" value="form">
</form>
</div></td>
</tr>
<tr>
<td height="19" colspan="2" bgcolor="#CCCCCC"><hr color="#666666" width="100%"></td>
</tr>
<tr>
<td height="76" colspan="2" valign="top" bgcolor="#CCCCCC"><div align="center">
<form name="form1" method="POST" action="index.cfm">
<div align="center">
<table width="1000" border="0" cellpadding="1" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="406" bgcolor="#999999" class="inhalt"> <div align="right">Offerte
suchen<span class="titelbar"> [</span>Suchbegriff eingeben<span class="titelbar">]</span></div></td>
<td width="151" bgcolor="#999999"> <div align="center">
<input type="text" name="eingabe" value="<cfoutput>#abfrage#</cfoutput>">
</div></td>
<td width="230" bgcolor="#333333"><span class="titelbar_hell">Suchen
in</span> <select name="">
<option value="kunde_id">Offerte Nr.</option>
<option value="firma">Firma</option>
<option value="name">Name</option>
<option value="vorname">vorname</option>
<option value="plz">PLZ / Ort</option>
<option value="adresse">Adresse</option>
<option value="tel">Telefon Nr.</option>
<option value="faxnr">Fax Nr.</option>
<option value="email">E-Mail</option>
</select> </td>
<td width="12" valign="middle" bgcolor="#999999"> <div align="center">
</div></td>
<td width="185" valign="middle" bgcolor="#999999"> <input type="submit" name="goto" value=">SEARCH">
</td>
</tr>
</table>
</div>
</form>
</div></td>
</tr>
</table>
</body>
</html>
So dass ich mit einer Suchfunktion (LIKE) nach Kriterien suchen kann.
Aber irgend wie bringe ich es nicht fertig, kann mir irgend jemand dabei helfen?
Vieln Dank für eure Hilfe.
Gruss Alesis
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form">
<cfquery datasource="kundendata" username="root" password="root">
INSERT INTO kunden (firma, name, vorname, adresse, email, tel, faxnr, ort, plz) VALUES
(
<cfif IsDefined("FORM.firma") AND #FORM.firma# NEQ "">
'#FORM.firma#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.name") AND #FORM.name# NEQ "">
'#FORM.name#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.vorname") AND #FORM.vorname# NEQ "">
'#FORM.vorname#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.adresse") AND #FORM.adresse# NEQ "">
'#FORM.adresse#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.email") AND #FORM.email# NEQ "">
'#FORM.email#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.tel") AND #FORM.tel# NEQ "">
'#FORM.tel#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.faxnr") AND #FORM.faxnr# NEQ "">
'#FORM.faxnr#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.ort") AND #FORM.ort# NEQ "">
'#FORM.ort#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.plz") AND #FORM.plz# NEQ "">
#FORM.plz#
<cfelse>
NULL
</cfif>
)
</cfquery>
<cflocation url="index.cfm">
</cfif>
<cfquery name="abfrage" datasource="kundendata" password="root" username="root">
select *
from kunden
where 0=0
<cfif #name# is NOT "">
AND name LIKE '%#name#%'
</cfif>
<cfif #vorname# is NOT "">
AND vorname LIKE '%#vorname#%'
</cfif>
<cfif #adresse# is NOT "">
AND adresse LIKE '%#adresse#%'
</cfif>
<cfif #email# is NOT "">
AND email LIKE '%#email#%'
</cfif>
<cfif #tel# is NOT "">
AND tel LIKE '%#tel#%'
</cfif>
<cfif #faxnr# is NOT "">
AND faxnr LIKE '%#faxnr#%'
</cfif>
<cfif #datum# is NOT "">
AND datum LIKE '%#datum#%'
</cfif>
<cfif #ort# is NOT "">
AND ort LIKE '%#ort#%'
</cfif>
<cfif #plz# is NOT "">
AND plz LIKE '%#plz#%'
</cfif>
<cfif #firma# is NOT "">
AND firma LIKE '%#firma#%'
</cfif>
<cfif #kunde_id# is NOT "">
AND kunde_id LIKE '%#kunde_id#%'
</cfif>
</cfquery>
<cfif ABFRAGE.RECORDCOUNT IS 0>
Es wurden keine passenden Datensätze gefunden<br>
<cfelse>
<cfoutput>#abfrage.RecordCount#</cfoutput>Datensätze gefunden:<br>
<cftable query="abfrage" HTMLTABLE>
<cfcol header="vorname" text="<i>#vorname#</i>">
<cfcol header="name" text="<i>#name#</i>">
<cfcol header="adresse" text="#adresse#">
<cfcol header="email" text="#email#">
<cfcol header="tel" text="#tel#">
<cfcol header="datum" text="#datum#">
<cfcol header="ort" text="#ort#">
<cfcol header="plz" text="#plz#">
<cfcol header="firma" text="#firma#">
<cfcol header="kunde_id" text="#kunde_id#">
</cftable>
</cfif>
<html>
<head>
<title>publit_offerte</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css.css" rel="stylesheet" type="text/css">
<link href="css_formular.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#E0E0E0" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="145" colspan="2" bgcolor="#999999"> <div align="center">
<form name="form" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
<table width="1000" cellpadding="1" cellspacing="1" bordercolor="#666699">
<tr>
<td width="120" height="26" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Firma:</div></td>
<td colspan="5" bgcolor="#5EBBCC"> <input type="text" name="firma" size="28">
<span class="titelbar">°</span></td>
</tr>
<tr>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Name:
</div></td>
<td width="219" bgcolor="#99CCCC"> <input type="text" name="name" size="28">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Adresse:</div></td>
<td width="290" bgcolor="#99CCCC"> <input type="text" name="adresse" size="28">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Natel
oder Fixnr.</div></td>
<td width="145" bgcolor="#99CCCC"> <input type="text" name="tel" size="14">
<span class="titelbar">°</span></td>
</tr>
<tr>
<td width="120" height="24" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Vorname:</div></td>
<td bgcolor="#99CCCC"><input type="text" name="vorname" size="28">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">PLZ
/ Ort:</div></td>
<td bgcolor="#99CCCC"> <input type="text" name="plz" size="5"> <input type="text" name="ort" size="22">
<span class="titelbar">°</span></td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">Faxnr.</div></td>
<td bgcolor="#99CCCC"><input type="text" name="faxnr" size="14"></td>
</tr>
<tr bgcolor="#CCCCCC">
<td colspan="3"> </td>
<td bgcolor="#CCCCCC"> </td>
<td width="120" bgcolor="#CCCCCC" class="inhalt"> <div align="right">E-Mail:</div></td>
<td bgcolor="#99CCCC"><input type="text" name="email" size="20"></td>
</tr>
<tr bgcolor="#CCCCCC">
<td colspan="3"><div align="right"></div>
<div align="left" class="inhalt">Die Markierten Felder (<span class="titelbar">°</span>)
sind muss Felder!</div></td>
<td colspan="3" bgcolor="#999999"> <input type="submit" name="Submit1" value="save">
<input type="reset" name="Submit2" value="clear"></td>
</tr>
</table>
<input type="hidden" name="MM_InsertRecord" value="form">
</form>
</div></td>
</tr>
<tr>
<td height="19" colspan="2" bgcolor="#CCCCCC"><hr color="#666666" width="100%"></td>
</tr>
<tr>
<td height="76" colspan="2" valign="top" bgcolor="#CCCCCC"><div align="center">
<form name="form1" method="POST" action="index.cfm">
<div align="center">
<table width="1000" border="0" cellpadding="1" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="406" bgcolor="#999999" class="inhalt"> <div align="right">Offerte
suchen<span class="titelbar"> [</span>Suchbegriff eingeben<span class="titelbar">]</span></div></td>
<td width="151" bgcolor="#999999"> <div align="center">
<input type="text" name="eingabe" value="<cfoutput>#abfrage#</cfoutput>">
</div></td>
<td width="230" bgcolor="#333333"><span class="titelbar_hell">Suchen
in</span> <select name="">
<option value="kunde_id">Offerte Nr.</option>
<option value="firma">Firma</option>
<option value="name">Name</option>
<option value="vorname">vorname</option>
<option value="plz">PLZ / Ort</option>
<option value="adresse">Adresse</option>
<option value="tel">Telefon Nr.</option>
<option value="faxnr">Fax Nr.</option>
<option value="email">E-Mail</option>
</select> </td>
<td width="12" valign="middle" bgcolor="#999999"> <div align="center">
</div></td>
<td width="185" valign="middle" bgcolor="#999999"> <input type="submit" name="goto" value=">SEARCH">
</td>
</tr>
</table>
</div>
</form>
</div></td>
</tr>
</table>
</body>
</html>
