<cfquery name="Getpo" datasource="purchaseorders">
SELECT *
FROM purchaseorders p JOIN itemsordered i
ON p.SOMETHING=i.SOMETHING
Where 0=0
...
but also, unless those form fields are checkboxes, they will be defined upon form submission. I'm guessing here, but you probably want something along the lines of <cffif len(trim(FORM.ponumber))>...</cfif>
Also, someone else suggested you pull out the 0=0 and pre-build the query, I personally do it the way you have it, but I think that's more a matter of style than actual value judgement.
Other than that, I'd second what others have said, re: CFQUERYPARAM and also grab only what you need.
John
-----Original Message-----
From: Robert Orlini [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 2:27 PM
To: CF-Talk
Subject: OT: SQL search query
I'm still new to SQL. I want to search two tables from a form. The search below does not give me any errors, but neither does it generate any hits even though I enter the correct items to look for. How do I edit the SQL to make it search info in both the "purchaseorders" and/or "itemsordered" tables? Thanks.
<cfquery name="Getpo" datasource="purchaseorders">
SELECT * FROM purchaseorders p, itemsordered i
Where 0=0
<CFIF IsDefined("FORM.ponumber")>
And ponumber LIKE '%#FORM.ponumber#%'
</CFIF>
<CFIF IsDefined("FORM.invoicenumber")>
And invoicenumber LIKE '%#FORM.invoicenumber#%'
</CFIF>
<CFIF IsDefined("FORM.vendor")>
And vendor LIKE '#trim(FORM.vendor)#'
</CFIF>
Order by p.cnumber DESC
Robert O.
ô¿ô
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
