RE: SQL search query
First off you need to get rid of the where 0=0 in the where clause SNIP CFIF Form.ponnumber neq CFSET Searchpon=Where ponumber like '%#Form.ponumber#%' CFIF FORM.invoicenumber neq CFSET SearchInv=And invoicenumber LIKE'%#FORM.invoicenumber#%' /CFIF Actually the 0=0 (or something like it) is required, in your example code included above if form.ponnumber is indeed blank then your statement would be entirely missing the WHERE statement although it could have many AND's, this will cause an error. -- Jay [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL search query
I am now in the habit of putting all my cfif/else statements before the query because I actually never use cfquery.I put everything in stored procedures.So I either put it before the stored proc or sometimes do a case/if/then in the proc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL search query
Can't help you much with that code. What columns are in what tables and what are you trying to achieve? You are definitly missing a join there! And of course, you should be using cfqueryparam !! Pascal -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juni 2004 20:27 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]
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. Ive found that the easier way to handle these is to do it all in an cfif statement before the actual query.That way you dont have to do all of the logic in the query. Ive 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 youd 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]
RE: SQL search query
AS others have said, you are definitely missing some sort of join mechanism such as: 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]
RE: SQL search query
Nonono: this way you can't use cfqueryparam. As for finding something 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=purchaseordersSELECT * 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]
RE: SQL search query
Why can't you use cfqueryparam? I found the original more readable as well. Also, I'd triple and quadruple check to see if I could get rid of the LIKE operator. It's a process hog big time. LIKE for fields of this nature doesn't make sense to me, but I've got no clue what data you're dealing with :-) John -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 15, 2004 5:22 PM To: CF-Talk Subject: RE: SQL search query Nonono: this way you can't use cfqueryparam. As for finding something 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=purchaseordersSELECT * 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]
