more readable, this is a matter of opinion. I like to have all the logic
in the cfquery itself. When I look at your code, I find it a lot more
complex than the code Robert had.
There is nothing wrong with using 0 = 0. It just allows you to have less
cfif's.
I totally agree on the join and the select * though.
Pascal
> -----Original Message-----
> From: [EMAIL PROTECTED] [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 15 juni 2004 23:04
> To: CF-Talk
> Subject: Re: SQL search query
>
> Robert
> First off you need to get rid of the where 0=0 in the where
> clause and use some logic to set how you really want your
> where statement to look.
> I've found that the easier way to handle these is to do it
> all in an cfif statement before the actual query. That way
> you don't have to do all of the logic in the query.
> I've found it more readable and easy to use.
> For example
> <CFPARAM NAME=" FORM.ponumber" DEFAULT=""> <CFPARAM
> NAME="FORM.invoicenumber" DEFAULT=""> <CFPARAM
> NAME="FORM.vendor" DEFAULT=""> <CFPARAM NAME="SearchAll" DEFAULT="">
>
> <CFIF Form.ponnumber neq "">
> <CFSET Searchpon=Where ponumber like '%#Form.ponumber#%'
> <CFIF FORM.invoicenumber neq "">
> <CFSET SearchInv="And invoicenumber LIKE'%#FORM.invoicenumber#%'"
> </CFIF>
> <CFIF FORM.vendor neq "">
> <CFSET Searchven= "And vendor LIKE '#trim(FORM.vendor)#'">
> </CFIF>
> <CFSET SearchAll="#SearchPon##SearchInv##SearchVen#">
> <CFELSEIF Form.Ponnum is "" and Form.Invoicenumber NEQ "">
> <CFSET SearchInv="WHERE invoicenumber
> LIKE'%#FORM.invoicenumber#%'">
> <CFIF FORM.vendor neq "">
> <CFSET Searchven= "And vendor LIKE '#trim(FORM.vendor)#'">
> </CFIF>
> <CFSET SearchAll="#SearchInv##SearchVen#">
> <CFELSEIF Form.Ponnum is "" and Form.Invoicenumber is "" and
> Form.vendor NEQ "">
> <CFSET SearchVendor= "Where vendor LIKE '#trim(FORM.vendor)#'">
> <CFSET SearchAll="#SearchVendor#">
> </CFIF>
>
> <CFQUERY NAME="Getpo" datasource="purchaseorders"> SELECT *
> FROM purchaseorders p, itemsordered I inner join
> p.CommonID=i.CommonID #SearchAll#
>
> You also need to connect the tables in your from clause.
> Your current from would list out a record for each record
> from both tables. (ie:if you have 10 in the I table and 7 in
> table P you'd have 70 records that show). I added an inner
> join, just add whatever id hooks the 2 tables.
> I hope that you did a select * just for the example here and
> that you are not using it for the actual query. You should
> only select what you really need. The * is going to pull
> every single field from both tables- do you really need/want
> all that info. (and do you want your database chugging away
> on all that unwanted stuff?) Hope this gets you started.
> J
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
