RE: How to do
I did this with java script: function chkData() { if((document.frmname.radiobuttonname.checked)&&(document.frmName.RadioButtonName.value.length <= 0)) { alert("If you checked 'Other' for question 8, please describe."); return false; } else { return true; } } I called this function in the form tag: hth miriam -Original Message- From: Sumeet Khanna [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 11, 2000 2:34 PM To: '[EMAIL PROTECTED]' Subject: How to do Can anyone please tell me how to do radio button validation ,if I want to use the check "if radio button "Other" is clicked then only enter Other option text in text box" Sumee -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: How to do
Ok, let's assume that your radio button field is named FOO and your text box is named BAR. Instead of doing a , you will have to do this one "by hand": INSERT INTO someTable (Answer) VALUES ('#iif(FORM.FOO EQ "Other", DE("#FORM.BAR#"), DE("#FORM.FOO#"))#') It might be DE(FORM.FOO) and DE(FORM.BAR), but I always get confused with the DE() function. Try both forms and see which one works. I'll probably get flamed for using nested pounds.. *shrug* -Original Message- From: Sumeet Khanna [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 11, 2000 2:34 PM To: '[EMAIL PROTECTED]' Subject: How to do Can anyone please tell me how to do radio button validation ,if I want to use the check "if radio button "Other" is clicked then only enter Other option text in text box" Sumee -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: How to do that?
One would think they have multiple entries in the database, with 2 primary keys, article id, and page id. Dave -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 2:10 PM To: CF-Talk Subject: How to do that? Hi please see this link http://www.kodefusion.com/category/index.cfm?CategoryID=5 KodeFusion break articles with several pages inside a great recordset... How to do that? How to "trim" a big article in several small pages, included in DB? Thanx in advance -- ___ Get your free email from http://www.mail.com _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do that?
Thanx Dave maybe regex? Like inserting a, hmmm, statement and before inserting in DB regex find that statement and break it? Looks for the next statement and save. Looks for... Ugly solution?? Thanx once again... - Original Message - From: "Dave Sueltenfuss" <[EMAIL PROTECTED]> Date: Mon, 26 Jan 2004 14:17:24 -0500 To: CF-Talk <[EMAIL PROTECTED]> Subject: RE: How to do that? One would think they have multiple entries in the database, with 2 primary keys, article id, and page id. Dave -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 2:10 PM To: CF-Talk Subject: How to do that? Hi please see this link http://www.kodefusion.com/category/index.cfm?CategoryID=5 KodeFusion break articles with several pages inside a great recordset... How to do that? How to "trim" a big article in several small pages, included in DB? Thanx in advance -- ___ Get your free email from http://www.mail.com _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do that?
> please see this link > > http://www.kodefusion.com/category/index.cfm?CategoryID=5 > > KodeFusion break articles with several pages inside a great > recordset... How to do that? How to "trim" a big article in > several small pages, included in DB? > > Thanx in advance I would think they break it manually with multiple records for the article - a simple one to many join in a database would handle that properly [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: How to do that?
Or, the tool that they built for the articles allows them to enter page by page. Spectrum WebDesign wrote: > Thanx Dave > > maybe regex? Like inserting a, hmmm, statement and before > inserting in DB regex find that statement and break it? Looks for the > next statement and save. Looks for... Ugly solution?? > > Thanx once again... > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do that?
It could be that they are using what I am using to split up my content, i.e. database functionality DATALENGTH() or READTEXT which allows you to read in only certain X bytes. In my database I have like 20 content entries, but I only want to show 25K per page, so I use the above to split them up evenly. Hope that helps. Taco Fleur Blog http://www.tacofleur.com/index/blog/ Methodology http://www.tacofleur.com/index/methodology/ 0421 851 786 Tell me and I will forget Show me and I will remember Teach me and I will learn -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 January 2004 5:10 AM To: CF-Talk Subject: How to do that? Hi please see this link http://www.kodefusion.com/category/index.cfm?CategoryID=5 KodeFusion break articles with several pages inside a great recordset... How to do that? How to "trim" a big article in several small pages, included in DB? Thanx in advance -- ___ Get your free email from http://www.mail.com _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do that?
I suspect they are not doing this. If so then you are guaranteed to muck up your content. for example page 1 gets cut off at .. my guess is some sort of marker to mark a page end/begin and one row entry of the whole doc or one row per page. Heck, it could be static pages with faux url vars fooling us all! Doug -Original Message- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 3:11 PM To: CF-Talk Subject: RE: How to do that? It could be that they are using what I am using to split up my content, i.e. database functionality DATALENGTH() or READTEXT which allows you to read in only certain X bytes. In my database I have like 20 content entries, but I only want to show 25K per page, so I use the above to split them up evenly. Hope that helps. Taco Fleur Blog http://www.tacofleur.com/index/blog/ Methodology http://www.tacofleur.com/index/methodology/ 0421 851 786 Tell me and I will forget Show me and I will remember Teach me and I will learn -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 January 2004 5:10 AM To: CF-Talk Subject: How to do that? Hi please see this link http://www.kodefusion.com/category/index.cfm?CategoryID=5 KodeFusion break articles with several pages inside a great recordset... How to do that? How to "trim" a big article in several small pages, included in DB? Thanx in advance -- ___ Get your free email from http://www.mail.com _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do that?
of course you have to have something in place that checks for these kind of problems, where it could cut of the content within HTML markup. Taco Fleur Blog http://www.tacofleur.com/index/blog/ Methodology http://www.tacofleur.com/index/methodology/ 0421 851 786 Tell me and I will forget Show me and I will remember Teach me and I will learn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 January 2004 6:28 AM To: CF-Talk Subject: RE: How to do that? I suspect they are not doing this. If so then you are guaranteed to muck up your content. for example page 1 gets cut off at .. my guess is some sort of marker to mark a page end/begin and one row entry of the whole doc or one row per page. Heck, it could be static pages with faux url vars fooling us all! Doug -Original Message- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 3:11 PM To: CF-Talk Subject: RE: How to do that? It could be that they are using what I am using to split up my content, i.e. database functionality DATALENGTH() or READTEXT which allows you to read in only certain X bytes. In my database I have like 20 content entries, but I only want to show 25K per page, so I use the above to split them up evenly. Hope that helps. Taco Fleur Blog http://www.tacofleur.com/index/blog/ Methodology http://www.tacofleur.com/index/methodology/ 0421 851 786 Tell me and I will forget Show me and I will remember Teach me and I will learn -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 January 2004 5:10 AM To: CF-Talk Subject: How to do that? Hi please see this link http://www.kodefusion.com/category/index.cfm?CategoryID=5 KodeFusion break articles with several pages inside a great recordset... How to do that? How to "trim" a big article in several small pages, included in DB? Thanx in advance -- ___ Get your free email from http://www.mail.com _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do that?
Or is it too obvious. The article manager allows the creation of multiple pages for the content to be displayed, otherwise why would they have the page=xx in the url.. If it was me, I would have a content management system that would allow for multiple pages for an article. Secondly there is no real indication that the article is even stored in a DB, only the reference might be stored there. _ From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 January 2004 7:11 AM To: CF-Talk Subject: RE: How to do that? It could be that they are using what I am using to split up my content, i.e. database functionality DATALENGTH() or READTEXT which allows you to read in only certain X bytes. In my database I have like 20 content entries, but I only want to show 25K per page, so I use the above to split them up evenly. Hope that helps. Taco Fleur Blog http://www.tacofleur.com/index/blog/ Methodology http://www.tacofleur.com/index/methodology/ 0421 851 786 Tell me and I will forget Show me and I will remember Teach me and I will learn -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 January 2004 5:10 AM To: CF-Talk Subject: How to do that? Hi please see this link http://www.kodefusion.com/category/index.cfm?CategoryID=5 KodeFusion break articles with several pages inside a great recordset... How to do that? How to "trim" a big article in several small pages, included in DB? Thanx in advance -- ___ Get your free email from http://www.mail.com _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How to do this?
Your best options, IMO, are either Java, DHTML/CSS, or Flash. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, February 23, 2001 11:21 AM To: CF-Talk Subject: How to do this? Here's something that would be nice to do. I'm pretty sure this is an issue that server side scripting alone can't handle, but any comments would be nice. I'll describe the hypothetical application, which is building an outline tree. The user starts with a single parent item. Under that, he can add multiple other children, each of which can also be a parent to its own children. The question is - whats the best way to handle the dynamic building? I've seen a few Java applets that do it, but afterwards you have to get the information out of Java. I could do this with CF, but it means redrawing the screen every time you add a new child...thats just not satisfying, especially when the outline get very large. I've thought of Javascript to handle it, but while JS isn't taboo in our company, it isn't highly encouraged since it can be turned off... Any suggestions? Right now I'm leaning toward Java. Is there a good CF thing that could be used instead? Brian ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: how to do Google-like search??
Han, I would suggest taking a look at SQL server's Full Text Searching and the Verity engine built into CFAS. HTH, Joseph DeVore VeloxWeb Technologies -Original Message- From: han peng [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 2:01 AM To: CF-Talk Subject: how to do Google-like search?? Hi ppl.. is there a built in function or wat in MSSQL to perform search query like Google..?? for example.. i need to search for "Hello beautiful World"... how to write the query to search thru a column of a table where the return results can be... Beautiful Hello World World Helllo Beautiful Helllo World beautiful how to do it?? han __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: how to do Google-like search??
Also, SQL Server supports SOUNDEX phonetic translation searching. Look it up in MSDN for examples. This works really well where our client's large entertainment industry database is accessed by an international audience -- where people are always mis-spelling things like "Veen Raymes" and "Jon Trovolda". brendan avery 2.0 - [EMAIL PROTECTED] 310.779.2211 - santa monica, california > -Original Message- > From: Joseph DeVore [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 29, 2002 02:14 > To: CF-Talk > Subject: RE: how to do Google-like search?? > > > Han, > > I would suggest taking a look at SQL server's Full Text > Searching and the > Verity engine built into CFAS. > > > HTH, > > Joseph DeVore > VeloxWeb Technologies > > > > -Original Message- > From: han peng [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 29, 2002 2:01 AM > To: CF-Talk > Subject: how to do Google-like search?? > > > Hi ppl.. > is there a built in function or wat in MSSQL to perform search query > like Google..?? > > for example.. i need to search for "Hello beautiful World"... > > how to write the query to search thru a column of a table > where the return results can be... > > Beautiful Hello World > World Helllo Beautiful > Helllo World beautiful > > > how to do it?? > > > han > > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: how to do Google-like search??
// Assemble Search Queryif (NOT LEN(Keyword)) { Keyword = 'none'; } else { Keyword= Replace(Keyword,"'","","ALL"); } Delimiters = ' ,;:'; SearchName = ''; SearchDesc = ''; L = ListLen(Keyword, Delimiters); for(k=1 ; k LTE L; k=k+1) { SearchName = SearchName & "LOWER(p.description) LIKE '%#LCase(ListGetAt(KeyWord,k,Delimiters))#%'"; SearchDesc = SearchDesc & "LOWER(p.details) LIKE '%#LCase(ListGetAt(KeyWord,k,Delimiters))#%'"; if (k neq L) { SearchName = SearchName & ' AND '; SearchDesc = SearchDesc & ' AND '; } } SELECT whatever FROM product WHERE ((#PreserveSingleQuotes(SearchName)#) OR (#PreserveSingleQuotes(SearchDesc)#)) > Hi ppl.. > is there a built in function or wat in MSSQL to perform search query > like Google..?? > > for example.. i need to search for "Hello beautiful World"... > > how to write the query to search thru a column of a table > where the return results can be... > > Beautiful Hello World > World Helllo Beautiful > Helllo World beautiful > > > how to do it?? > > > han > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: How to do this without the subselect?
Break it into 2 queries. Select RW.ClientID from RentalWaivers RW where Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year#) Select UW.UtilityWaiverID from utilitywaivers UW where Month(UW.IssueDate) = #Form.Month# and Year(UW.IssueDate) = #Form.Year# and UW.ClientID not in (#filter#) > -Original Message- > From: Rick Faircloth [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 19, 2002 1:50 AM > To: CF-Talk > Subject: How to do this without the subselect? > > > Hi, all. > > How can I get the same results as this query with using the subselect? > > > > Select UW.UtilityWaiverID > from utilitywaivers UW > where Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in >(Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# >and Year(RW.RentalWaiverDate) = #Form.Year#) > > > > (I'll be s glad when they get subselects in mySQL 4.2!) > > Thanks, > > Rick > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: How to do this without the subselect?
Rick Faircloth wrote: > Hi, all. > > How can I get the same results as this query with using the subselect? > > > > Select UW.UtilityWaiverID > from utilitywaivers UW > where Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in >(Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# >and Year(RW.RentalWaiverDate) = #Form.Year#) > > > > (I'll be s glad when they get subselects in mySQL 4.2!) do a left outer join on RentalWaivers in the query and then do a check that where UW.ClientID is null (if, this is standard perfomance improvement anyway as it uses a index...) "not in" is actually a real bad performance killer... this technique is found in most good oracle books... should work for mysql but i don't know z ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: How to do this without the subselect?
Hi, Zac. I played around a bit with the left outer join before mailing to the list, but couldn't work out the syntax for this query. In the original message I did leave out a line which complicates the query. The original query with the subselect looked like the one below. The part about "#MultipleWaivers[CurrentRow][1]# is there because I'm looping the query through the names of Utility Companies. I tried some left outer join solutions, but nothing was working. Can you show me how you would set up the code with a left outer join? Since the where statement is taken by the "#MultipleWaivers..." line, it doesn't leave room for another where statement such as where UW.ClientID is NULL... Thanks for your help. Rick Select UW.UtilityWaiverID from utilitywaivers UW where UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# and Month(UW.IssueDate) = #Form.Month# and Year(UW.IssueDate) = #Form.Year# and UW.ClientID not in (Select RW.ClientID from RentalWaivers RW where Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year#) -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 4:46 AM To: CF-Talk Subject: Re: How to do this without the subselect? Rick Faircloth wrote: > Hi, all. > > How can I get the same results as this query with using the subselect? > > > > Select UW.UtilityWaiverID > from utilitywaivers UW > where Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in >(Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# >and Year(RW.RentalWaiverDate) = #Form.Year#) > > > > (I'll be s glad when they get subselects in mySQL 4.2!) do a left outer join on RentalWaivers in the query and then do a check that where UW.ClientID is null (if, this is standard perfomance improvement anyway as it uses a index...) "not in" is actually a real bad performance killer... this technique is found in most good oracle books... should work for mysql but i don't know z ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: How to do this without the subselect?
Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > Since the where statement is taken by the "#MultipleWaivers..." line, > it doesn't leave room for another where statement such as > where UW.ClientID is NULL... Shouldn't it be RW.ClientID IS NULL. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: How to do this without the subselect?
Hi, Jochem. >>Shouldn't it be RW.ClientID IS NULL Now, that's a good question. I've always used subselects to get around having to spend time with those unfriendly (at least to me!) joins. I don't know enough about the fine points of "joinery" to answer that. Suggestions on how to setup the code to accomplish what's below with a subselect? Select UW.UtilityWaiverID from utilitywaivers UW where UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# and Month(UW.IssueDate) = #Form.Month# and Year(UW.IssueDate) = #Form.Year# and UW.ClientID not in (Select RW.ClientID from RentalWaivers RW where Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year#) Thanks, Rick -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:29 AM To: CF-Talk Subject: RE: How to do this without the subselect? Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > Since the where statement is taken by the "#MultipleWaivers..." line, > it doesn't leave room for another where statement such as > where UW.ClientID is NULL... Shouldn't it be RW.ClientID IS NULL. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: How to do this without the subselect?
Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > Now, that's a good question. I've always used subselects to get > around having to spend time with those unfriendly (at least to me!) > joins. Then maybe MySQL isn't the database for you :) > Select UW.UtilityWaiverID > from utilitywaivers UW >where UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# > and Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in > (Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# > and Year(RW.RentalWaiverDate) = #Form.Year#) I think it should be: SELECT UW.UtilityWaiverID FROM utilitywaivers UW LEFT JOIN RentalWaivers RW ON (UW.ClientID = RW.ClientID AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year# ) WHERE UW.UtilityCompanyID = #MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND RW.ClientID IS NULL Don't forget to add the appropriate cfqueryparam tags. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: How to do this without the subselect?
> Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > > > Since the where statement is taken by the "#MultipleWaivers..." line, > > it doesn't leave room for another where statement such as > > where UW.ClientID is NULL... > > Shouldn't it be RW.ClientID IS NULL. > You're probably right Two possibles... (Neither tested, so expect typo's etc.) One is : SELECT UW.UtilityWaiverID FROM utilitywaivers AS UW RIGHT JOIN RentalWaiver AS RW ON UW.ClientID = RW.ClientID WHERE RW.ClientID IS NULL AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) The other is : SELECT UW.UtilityWaiverID FROM UtilityWaiver AS UW, RentalWaiver AS RW WHERE UW.ClientID <> RW.ClientID AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) I have a sneaky suspicious that the second query won't work, but give them a go and let us know Regards Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: How to do this without the subselect?
These two didn't work out, Stephen. Looks like Jochem's solution did it. I think the two solutions you suggested didn't work because they both required that the RentalWaiverDate's had to match the Form dates. Because the only hits in the query would be those that did have matching RentalWaiver dates, that caused nothing to be returned even though there were 3 records that should have been returned for the test date. It would return the correct hits only when there was a RentalWaiver for the Form dates and the ClientID's didn't match. If there was not RentalWaiver for the Form dates, nothing was returned. I tried coding various scenarios within the query, i.e., UW.ClientID=RW.Client, but RW.WaiverIssueDate <> Form Dates or UW.ClientID<>RW.Client, but RW.WaiverIssueDate = Form Dates, basically trying to cover all possibilities, but even then results weren't correct. *Unfortunately*, the totally *unintuitive* (for me), but *accurate* left join from Jochem worked. (Thanks, Jochem. :o) I've just always found the joins, especially left, outer, inner, etc., to be unintuitive when looking at the code. I guess I just haven't worked with them enough. I've always worked around them with subselects, which mySQL won't do. (Until 4.2) Or perhaps I'm too dense! :o) Thanks for your help. Rick -Original Message- From: Stephen Moretti [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 10:25 AM To: CF-Talk Subject: Re: How to do this without the subselect? > Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > > > Since the where statement is taken by the "#MultipleWaivers..." line, > > it doesn't leave room for another where statement such as > > where UW.ClientID is NULL... > > Shouldn't it be RW.ClientID IS NULL. > You're probably right Two possibles... (Neither tested, so expect typo's etc.) One is : SELECT UW.UtilityWaiverID FROM utilitywaivers AS UW RIGHT JOIN RentalWaiver AS RW ON UW.ClientID = RW.ClientID WHERE RW.ClientID IS NULL AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) The other is : SELECT UW.UtilityWaiverID FROM UtilityWaiver AS UW, RentalWaiver AS RW WHERE UW.ClientID <> RW.ClientID AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) I have a sneaky suspicious that the second query won't work, but give them a go and let us know Regards Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: How to do this without the subselect?
Quoting Jochem.. >Then maybe MySQL isn't the database for you :) You may be right...perhaps I should got back to Access... at least it was *advanced* enough to handle sub-selects... :o) Thanks for your help! You can pick up your tickets for your Hawaiian cruise at the front desk! Rick -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 10:21 AM To: CF-Talk Subject: RE: How to do this without the subselect? Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > Now, that's a good question. I've always used subselects to get > around having to spend time with those unfriendly (at least to me!) > joins. Then maybe MySQL isn't the database for you :) > Select UW.UtilityWaiverID > from utilitywaivers UW >where UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# > and Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in > (Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# > and Year(RW.RentalWaiverDate) = #Form.Year#) I think it should be: SELECT UW.UtilityWaiverID FROM utilitywaivers UW LEFT JOIN RentalWaivers RW ON (UW.ClientID = RW.ClientID AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year# ) WHERE UW.UtilityCompanyID = #MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND RW.ClientID IS NULL Don't forget to add the appropriate cfqueryparam tags. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: How to do this without the subselect?
Still don't know why Stas' idea didn't work with the two queries and a valuelist. Ideas? Kept getting the error that CF couldn't define ClientID in the cfset statement... Rick Select RW.ClientID from RentalWaivers RW where Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year#) Select UW.UtilityWaiverID from utilitywaivers UW where Month(UW.IssueDate) = #Form.Month# and Year(UW.IssueDate) = #Form.Year# and UW.ClientID not in (#filter#) -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 11:54 AM To: CF-Talk Subject: RE: How to do this without the subselect? These two didn't work out, Stephen. Looks like Jochem's solution did it. I think the two solutions you suggested didn't work because they both required that the RentalWaiverDate's had to match the Form dates. Because the only hits in the query would be those that did have matching RentalWaiver dates, that caused nothing to be returned even though there were 3 records that should have been returned for the test date. It would return the correct hits only when there was a RentalWaiver for the Form dates and the ClientID's didn't match. If there was not RentalWaiver for the Form dates, nothing was returned. I tried coding various scenarios within the query, i.e., UW.ClientID=RW.Client, but RW.WaiverIssueDate <> Form Dates or UW.ClientID<>RW.Client, but RW.WaiverIssueDate = Form Dates, basically trying to cover all possibilities, but even then results weren't correct. *Unfortunately*, the totally *unintuitive* (for me), but *accurate* left join from Jochem worked. (Thanks, Jochem. :o) I've just always found the joins, especially left, outer, inner, etc., to be unintuitive when looking at the code. I guess I just haven't worked with them enough. I've always worked around them with subselects, which mySQL won't do. (Until 4.2) Or perhaps I'm too dense! :o) Thanks for your help. Rick -Original Message- From: Stephen Moretti [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 10:25 AM To: CF-Talk Subject: Re: How to do this without the subselect? > Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > > > Since the where statement is taken by the "#MultipleWaivers..." line, > > it doesn't leave room for another where statement such as > > where UW.ClientID is NULL... > > Shouldn't it be RW.ClientID IS NULL. > You're probably right Two possibles... (Neither tested, so expect typo's etc.) One is : SELECT UW.UtilityWaiverID FROM utilitywaivers AS UW RIGHT JOIN RentalWaiver AS RW ON UW.ClientID = RW.ClientID WHERE RW.ClientID IS NULL AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) The other is : SELECT UW.UtilityWaiverID FROM UtilityWaiver AS UW, RentalWaiver AS RW WHERE UW.ClientID <> RW.ClientID AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) I have a sneaky suspicious that the second query won't work, but give them a go and let us know Regards Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: How to do this without the subselect?
> These two didn't work out, Stephen. > Never mind > Looks like Jochem's solution did it. > My first one should have been the same as Jochem's just wasn't paying attention and stuck the RW date checks in the wrong place... :o/ > I think the two solutions you suggested didn't work > because they both required that the > RentalWaiverDate's had to match the Form dates. > Because the only hits in the query would be those > that did have matching RentalWaiver dates, that caused nothing > to be returned even though there were 3 records that should > have been returned for the test date. > I had a feeling that nothing would be return from RentalWaiver table Sorry about that... > > I've just always found the joins, especially left, outer, inner, etc., to be > unintuitive when looking at the code. I guess I just haven't worked with > them enough. > Makes more sense when you match them up with Venn diagrams. > I've always worked around them with subselects, which mySQL won't do. (Until > 4.2) > All subqueries can be replaced with an appropriate JOIN, it just depends on how intuitive that query actually is Of course, when all else fails You could, ~shudder~, use query of queries > Or perhaps I'm too dense! :o) > ~lol~ yeah that's it Of course that makes the rest of us that didn't quite get it right dense too... ;o) > ^you've got a comma here It should be a stop. Regards Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: How to do this without the subselect?
>> ~lol~ yeah that's it Of course that makes the rest of us that didn't quite get it right dense too... ;o) No, the difference is, you hadn't already spent half a day and night trying to figure out how to code the query! I had! :o) Rick -Original Message- From: Stephen Moretti [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:06 PM To: CF-Talk Subject: Re: How to do this without the subselect? > These two didn't work out, Stephen. > Never mind > Looks like Jochem's solution did it. > My first one should have been the same as Jochem's just wasn't paying attention and stuck the RW date checks in the wrong place... :o/ > I think the two solutions you suggested didn't work > because they both required that the > RentalWaiverDate's had to match the Form dates. > Because the only hits in the query would be those > that did have matching RentalWaiver dates, that caused nothing > to be returned even though there were 3 records that should > have been returned for the test date. > I had a feeling that nothing would be return from RentalWaiver table Sorry about that... > > I've just always found the joins, especially left, outer, inner, etc., to be > unintuitive when looking at the code. I guess I just haven't worked with > them enough. > Makes more sense when you match them up with Venn diagrams. > I've always worked around them with subselects, which mySQL won't do. (Until > 4.2) > All subqueries can be replaced with an appropriate JOIN, it just depends on how intuitive that query actually is Of course, when all else fails You could, ~shudder~, use query of queries > Or perhaps I'm too dense! :o) > ~lol~ yeah that's it Of course that makes the rest of us that didn't quite get it right dense too... ;o) > ^you've got a comma here It should be a stop. Regards Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: How to do this without the subselect?
Hey, Jochem... Since you've got a good grip on how the joins work, would my explanation for my notes concerning why this query works be accurate? Notes: This query works to select UtilityWaivers that were issued within a given month and which were issued without an accompanying RentalWaiver. The part of the query before the "where" clause selects all UtilityWaivers which were issued within the specified month and which have accompanying RentalWaivers with matching ClientID's for the same month. The "where" clause then specifies that the joined group should be limited to those UtilityWaivers whose ClientID does match any RentalWaiver ClientID. Select UW.UtilityWaiverID from utilitywaivers UW left join RentalWaivers RW on (UW.ClientID = RW.ClientID and Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year# ) where UW.UtilityCompanyID = #MultipleWaivers[CurrentRow][1]# and Month(UW.IssueDate) = #Form.Month# and Year(UW.IssueDate) = #Form.Year# and RW.ClientID IS NULL Thanks, Rick -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 10:21 AM To: CF-Talk Subject: RE: How to do this without the subselect? Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > Now, that's a good question. I've always used subselects to get > around having to spend time with those unfriendly (at least to me!) > joins. Then maybe MySQL isn't the database for you :) > Select UW.UtilityWaiverID > from utilitywaivers UW >where UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# > and Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in > (Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# > and Year(RW.RentalWaiverDate) = #Form.Year#) I think it should be: SELECT UW.UtilityWaiverID FROM utilitywaivers UW LEFT JOIN RentalWaivers RW ON (UW.ClientID = RW.ClientID AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year# ) WHERE UW.UtilityCompanyID = #MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND RW.ClientID IS NULL Don't forget to add the appropriate cfqueryparam tags. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: How to do this without the subselect?
Rick Faircloth wrote: > > You may be right...perhaps I should got back to Access... > at least it was *advanced* enough to handle sub-selects... :o) There are other very nice free databases that have a HISTORY that reads like MySQL's TODO :-) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: How to do this without the subselect?
Rick Faircloth wrote: > > I've just always found the joins, especially left, outer, inner, etc., to be > unintuitive when looking at the code. I guess I just haven't worked with > them enough. > I've always worked around them with subselects, which mySQL won't do. (Until > 4.2) Some first form of subquery support was committed to CVS earlier this month, so that will be a while. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: How to do this without the subselect?
I dig PostgreSQL http://www14.us.postgresql.org/ (though I still can't pronounce it) (non us www.postgresql.org) Rob http://treebeard.sourceforge.net http://ruinworld.sourceforge.net Scientia Est Potentia -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:22 PM To: CF-Talk Subject: Re: How to do this without the subselect? Rick Faircloth wrote: > > You may be right...perhaps I should got back to Access... > at least it was *advanced* enough to handle sub-selects... :o) There are other very nice free databases that have a HISTORY that reads like MySQL's TODO :-) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: How to do this without the subselect?
Rick Faircloth wrote: > > Notes: > > This query works to select UtilityWaivers that were issued within a given > month and which were issued without an accompanying RentalWaiver. > > The part of the query before the "where" clause selects all UtilityWaivers > which were issued within the specified month and which have > accompanying RentalWaivers with matching ClientID's for the same month. I would say: The FROM clause selects all rows (LEFT JOIN) from UtilityWaivers, then checks if there is a row in RentalWaivers that matches the conditions , , and if so appends the fields from it to the rows just selected. If not, the fields are still appended to have the correct number of fields, but filled out with NULL's. > The "where" clause then specifies that the joined group should be limited > to those UtilityWaivers whose ClientID does match any RentalWaiver ClientID. Yes. General strategy for building queries which works best for *me*: - first FROM including all joins - then WHERE - then SELECT - then GROUP BY, ORDER BY Generalized strategy for flattening a query with subqueries: SELECT * FROM a WHERE a.field IN (SELECT b.field FROM b WHERE ) AND SELECT a.* FROM a JOIN b ON (a.field = b.field AND ) WHERE If IN is actually NOT IN, becomes LEFT and you add the NOT NULL to the predicates. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: How to do this without the subselect?
>(though I still can't pronounce it) Pronounce the first section as it looks then add Q-L on the end. Postgres-Q-L -Original Message- From: Rob Rohan [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 2:32 PM To: CF-Talk Subject: RE: How to do this without the subselect? I dig PostgreSQL http://www14.us.postgresql.org/ (though I still can't pronounce it) (non us www.postgresql.org) Rob http://treebeard.sourceforge.net http://ruinworld.sourceforge.net Scientia Est Potentia -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:22 PM To: CF-Talk Subject: Re: How to do this without the subselect? Rick Faircloth wrote: > > You may be right...perhaps I should got back to Access... > at least it was *advanced* enough to handle sub-selects... :o) There are other very nice free databases that have a HISTORY that reads like MySQL's TODO :-) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: How to do this without the subselect?
Thanks for the overview, Jochem. It's much appreciated. I'll file the info away for reference. >>The "where" clause then specifies that the joined group should be limited >>to those UtilityWaivers whose ClientID does match any RentalWaiver ClientID. >Yes. I got one part right! There's hope for me, yet! :o) Rick -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 4:03 PM To: CF-Talk Subject: Re: How to do this without the subselect? Rick Faircloth wrote: > > Notes: > > This query works to select UtilityWaivers that were issued within a given > month and which were issued without an accompanying RentalWaiver. > > The part of the query before the "where" clause selects all UtilityWaivers > which were issued within the specified month and which have > accompanying RentalWaivers with matching ClientID's for the same month. I would say: The FROM clause selects all rows (LEFT JOIN) from UtilityWaivers, then checks if there is a row in RentalWaivers that matches the conditions , , and if so appends the fields from it to the rows just selected. If not, the fields are still appended to have the correct number of fields, but filled out with NULL's. > The "where" clause then specifies that the joined group should be limited > to those UtilityWaivers whose ClientID does match any RentalWaiver ClientID. Yes. General strategy for building queries which works best for *me*: - first FROM including all joins - then WHERE - then SELECT - then GROUP BY, ORDER BY Generalized strategy for flattening a query with subqueries: SELECT * FROM a WHERE a.field IN (SELECT b.field FROM b WHERE ) AND SELECT a.* FROM a JOIN b ON (a.field = b.field AND ) WHERE If IN is actually NOT IN, becomes LEFT and you add the NOT NULL to the predicates. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: How to do this without the subselect?
Looks like you got the answer you needed with this problem, but I didn't a notice an answer to this question, why the following code didn't work, and was throwing the clientID error. If this has been solved already, pardon my repetition. There is a simple typo on the following code: should read as . A "." before clientID not a ",". ILS BloodSource Sacramento, CA -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:00 AM To: CF-Talk Subject: RE: How to do this without the subselect? Still don't know why Stas' idea didn't work with the two queries and a valuelist. Ideas? Kept getting the error that CF couldn't define ClientID in the cfset statement... Rick Select RW.ClientID from RentalWaivers RW where Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year#) Select UW.UtilityWaiverID from utilitywaivers UW where Month(UW.IssueDate) = #Form.Month# and Year(UW.IssueDate) = #Form.Year# and UW.ClientID not in (#filter#) -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 11:54 AM To: CF-Talk Subject: RE: How to do this without the subselect? These two didn't work out, Stephen. Looks like Jochem's solution did it. I think the two solutions you suggested didn't work because they both required that the RentalWaiverDate's had to match the Form dates. Because the only hits in the query would be those that did have matching RentalWaiver dates, that caused nothing to be returned even though there were 3 records that should have been returned for the test date. It would return the correct hits only when there was a RentalWaiver for the Form dates and the ClientID's didn't match. If there was not RentalWaiver for the Form dates, nothing was returned. I tried coding various scenarios within the query, i.e., UW.ClientID=RW.Client, but RW.WaiverIssueDate <> Form Dates or UW.ClientID<>RW.Client, but RW.WaiverIssueDate = Form Dates, basically trying to cover all possibilities, but even then results weren't correct. *Unfortunately*, the totally *unintuitive* (for me), but *accurate* left join from Jochem worked. (Thanks, Jochem. :o) I've just always found the joins, especially left, outer, inner, etc., to be unintuitive when looking at the code. I guess I just haven't worked with them enough. I've always worked around them with subselects, which mySQL won't do. (Until 4.2) Or perhaps I'm too dense! :o) Thanks for your help. Rick -Original Message----- From: Stephen Moretti [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 10:25 AM To: CF-Talk Subject: Re: How to do this without the subselect? > Quoting Rick Faircloth <[EMAIL PROTECTED]>: > > > > Since the where statement is taken by the "#MultipleWaivers..." line, > > it doesn't leave room for another where statement such as > > where UW.ClientID is NULL... > > Shouldn't it be RW.ClientID IS NULL. > You're probably right Two possibles... (Neither tested, so expect typo's etc.) One is : SELECT UW.UtilityWaiverID FROM utilitywaivers AS UW RIGHT JOIN RentalWaiver AS RW ON UW.ClientID = RW.ClientID WHERE RW.ClientID IS NULL AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) The other is : SELECT UW.UtilityWaiverID FROM UtilityWaiver AS UW, RentalWaiver AS RW WHERE UW.ClientID <> RW.ClientID AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]# AND Month(UW.IssueDate) = #Form.Month# AND Year(UW.IssueDate) = #Form.Year# AND Month(RW.RentalWaiverDate) = #Form.Month# AND Year(RW.RentalWaiverDate) = #Form.Year#) I have a sneaky suspicious that the second query won't work, but give them a go and let us know Regards Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: how to do a loop? ...or something.
Aimee, Here's one possible way: The form fields for the addresses, all named "address", will produce a comma-delimited list with as many elements as people. That is, even if some of the address fields are blank, the form will still pass those to the action template. I assume you're also using a unique ID number for each contact, so you could populate the initial form with hidden input fields, like this, to pass the ID numbers retrieved via your second query: Then you should be able to loop through the ContactIDs using LIST functions, something like this to save the info: UPDATE Contacts SET Name="#ListGetAt(Form.Name, Counter)#", Address="#ListGetAt(Form.Address, Counter)#" WHERE ContactID=#ThisContactID# ListGetAt combined with the Counter number will keep the information in sync... Gene Kraybill LPW & Associates Mansfield, PA On 21 Jun 2001, at 20:27, Aimee Abbott wrote: > > Hello everyone! > > I have a database of companys, names, contacts and addresses. Not all > companys have contact addresses. I am setting up a form that will allow > people to come and change their address. They will get to the form by > entering a url with a primary key at the end of it. This will query up the > company and then a second query will get the people. The second gets the > contact names and addresses -- there can be as many as a dozen > contacts. That is the one I am having trouble with. What I have done is > set it up so (in simple form) > > > > > > > > name: > address: > > > > Now, like I said there can be a lot of contact names. > > So, then on the next page where I save it in the database, what do I do? I > get the information in the form of a list. Where > #name#=bob, mary, alice, harry, john > but not everyone has an address so I might get > #address#=chicago, minneapolis, rome > > I need to some how match these up somehow and update the record. Any idea > how I can go about doing this? > > I have thought about the first page having some sort of number appended on > to the end of the variable name so the form would somehow have name1, > name2, name3, name4 but I don't actually know how to do that. > > Or from the second page, can I access individual list elements? And even > if I could, how could I know that bob is from chicago, mary is blank and > alice is from minneapolis? > > If I haven't explained this well enough please let me know! > > Thanks much! > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: how to do a loop? ...or something.
Gene, Thanks! Unfortunately not that simple. It doesn't pass a null value. If I have three of them and only two addresses I get a list with only two elements in it, not two and a null. My *current* idea is to put the default Aimee, > >Here's one possible way: > >The form fields for the addresses, all named "address", will produce >a comma-delimited list with as many elements as people. That is, >even if some of the address fields are blank, the form will still pass >those to the action template. > >I assume you're also using a unique ID number for each contact, >so you could populate the initial form with hidden input fields, like >this, to pass the ID numbers retrieved via your second query: > >VALUE="#ContactID#"> > >Then you should be able to loop through the ContactIDs using LIST >functions, something like this to save the info: > > > > > > UPDATE Contacts > SET Name="#ListGetAt(Form.Name, Counter)#", > Address="#ListGetAt(Form.Address, Counter)#" > WHERE ContactID=#ThisContactID# > > > >ListGetAt combined with the Counter number will keep the >information in sync... > >Gene Kraybill >LPW & Associates >Mansfield, PA > >On 21 Jun 2001, at 20:27, Aimee Abbott wrote: > > > > > Hello everyone! > > > > I have a database of companys, names, contacts and addresses. Not all > > companys have contact addresses. I am setting up a form that will allow > > people to come and change their address. They will get to the form by > > entering a url with a primary key at the end of it. This will query up > the > > company and then a second query will get the people. The second gets the > > contact names and addresses -- there can be as many as a dozen > > contacts. That is the one I am having trouble with. What I have done is > > set it up so (in simple form) > > > > > > > > > > > > > > > > name: > > address: > > > > > > > > Now, like I said there can be a lot of contact names. > > > > So, then on the next page where I save it in the database, what do I > do? I > > get the information in the form of a list. Where > > #name#=bob, mary, alice, harry, john > > but not everyone has an address so I might get > > #address#=chicago, minneapolis, rome > > > > I need to some how match these up somehow and update the record. Any idea > > how I can go about doing this? > > > > I have thought about the first page having some sort of number appended on > > to the end of the variable name so the form would somehow have name1, > > name2, name3, name4 but I don't actually know how to do that. > > > > Or from the second page, can I access individual list elements? And even > > if I could, how could I know that bob is from chicago, mary is blank and > > alice is from minneapolis? > > > > If I haven't explained this well enough please let me know! > > > > Thanks much! > > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: how to do a loop? ...or something.
Aimee You're right. Actually, now that I think of it, the way I've handled this situation is to append the ID number to the name of the field, e.g.: and do the same for the address, then just loop through all the fieldnames that start with "Name_", use the Eval function to get the name value in each case, parse out the ID number (using List functions with the underscore as the delimiter) and then use that same ID number with "Address_#ID#" to determine with the corresponding Address value. The logic would go something like this: If the field begins with "Name_", determine its value and parse out the ID number. Use the same ID number and plug it into "Address_#ID#" to determine the address value. Update that record. Others may have a better way... Gene Kraybill On 21 Jun 2001, at 23:20, Aimee Abbott wrote: > Gene, Thanks! > > Unfortunately not that simple. It doesn't pass a null value. If I have > three of them and only two addresses I get a list with only two elements in > it, not two and a null. My *current* idea is to put the default > for any field that does not have a value. Then use basically your script > to parse it out. This will break though if someone enters something and > then decides it was a mistake, deletes it, and types it on the next > line. I thought maybe I could count the list elements to make sure they > were correct but then what? > > Does CFinput work the same way I wonder? > > At 11:39 PM 6/21/2001 -0700, you wrote: > >Aimee, > > > >Here's one possible way: > > > >The form fields for the addresses, all named "address", will produce > >a comma-delimited list with as many elements as people. That is, > >even if some of the address fields are blank, the form will still pass > >those to the action template. > > > >I assume you're also using a unique ID number for each contact, > >so you could populate the initial form with hidden input fields, like > >this, to pass the ID numbers retrieved via your second query: > > > > >VALUE="#ContactID#"> > > > >Then you should be able to loop through the ContactIDs using LIST > >functions, something like this to save the info: > > > > > > > > > > > > UPDATE Contacts > > SET Name="#ListGetAt(Form.Name, Counter)#", > > Address="#ListGetAt(Form.Address, Counter)#" > > WHERE ContactID=#ThisContactID# > > > > > > > >ListGetAt combined with the Counter number will keep the > >information in sync... > > > >Gene Kraybill > >LPW & Associates > >Mansfield, PA > > > >On 21 Jun 2001, at 20:27, Aimee Abbott wrote: > > > > > > > > Hello everyone! > > > > > > I have a database of companys, names, contacts and addresses. Not all > > > companys have contact addresses. I am setting up a form that will allow > > > people to come and change their address. They will get to the form by > > > entering a url with a primary key at the end of it. This will query up > > the > > > company and then a second query will get the people. The second gets the > > > contact names and addresses -- there can be as many as a dozen > > > contacts. That is the one I am having trouble with. What I have done is > > > set it up so (in simple form) > > > > > > > > > > > > > > > > > > > > > > > > name: > > > address: > > > > > > > > > > > > Now, like I said there can be a lot of contact names. > > > > > > So, then on the next page where I save it in the database, what do I > > do? I > > > get the information in the form of a list. Where > > > #name#=bob, mary, alice, harry, john > > > but not everyone has an address so I might get > > > #address#=chicago, minneapolis, rome > > > > > > I need to some how match these up somehow and update the record. Any idea > > > how I can go about doing this? > > > > > > I have thought about the first page having some sort of number appended on > > > to the end of the variable name so the form would somehow have name1, > > > name2, name3, name4 but I don't actually know how to do that. > > > > > > Or from the second page, can I access individual list elements? And even > > > if I could, how could I know that bob is from chicago, mary is blank and > > > alice is from minneapolis? > > > > > > If I haven't explained this well enough please let me know! > > > > > > Thanks much! > > > > > > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: How to do you pull information from the PC?
I usually build this functionality into the database .. inserting dates and times and other info into certain fields as a user does something. I don't know if there is a 'built in' way to do this, though. .Todd - Original Message - From: "WHubbard" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, May 10, 2000 8:43 AM Subject: How to do you pull information from the PC? | Hi, I need to fill in a field with the last user logged in. This is an | intranet site and I need to keep track of who modifies the record. How do | you pull this kind of information from the machine? -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: How to do multiple updates in one submit click
You'll want to loop over form.fieldnames using the LIST mode of cfloop. Something like: cfloop list=form.fieldnames index=afield if left(afield,8) eq "quantity" if evaluate("form."&afield) neq 0 set itemno = mid(afield, 5, 1) set itemquantity = evaluate("form.item"&itemno&"quantity") set itemcode = evaluate("form.item"&itemno&"itemcode) cfquery... insert into ... hth, AE -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Kear Sent: Sunday, June 25, 2000 11:10 PM To: [EMAIL PROTECTED] Subject: How to do multiple updates in one submit click I'm building an application that's kind of like a menu. There might be a hundred items in this menu, and people will be ordering dozens of items in a single order. I'd like to have them enter the order in one go instead of having to click and wait for the page to update at each item, the way a regular shopping cart does. What 'd like to be able to do is have them go down the page, entering quantities against the items they want, and then process the order on teh one click. Each item will go in a orders table with the following fields: [invoicenumber] [itemcode] [quantity] and presumably a [transid] field to index this table. How can I code the CF action page to do these multiple inserts into the orders table? The form will look something like this: Item code Quantity Description Price per unit #Itemcode1# #Description1# #Priceperunit# more items - up to 100 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: How to do multiple updates in one submit click
This is a multi-part message in MIME format. --=_NextPart_000_0125_01BFDF10.CC8062E0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit > What 'd like to be able to do is have them go down the page, entering > quantities against the items they want, and then process the order on teh > one click. This is the way I normally build my shopping carts. You'll end up doing a loop of some sort, but I don't like using Form.FieldNames (as someone else suggested) because, especially with a large form, it adds up to a lot of wasted processing time. Here's what I do: 1) When displaying the front end, maintain a list of the item codes that are on the page. If you are displaying items from a query this is easy: each ouput iteration do the following: Also, you'll need to include a quantity field for each item. You can do this like so: This gives you a quantity field for each item on the form. You can access the qty of any item simply by appending the Item Code to the string "Qty_". In other words, Item Code 5 would have a qty field called "Form.Qty_5". You can set other item-specific params (such as item flags) in this same way. 2) When the form submits it will pass along a form field called "ItemsOnPage" that contains a nicely formatted, comma delimited list of item codes that the user had access to on the form. You can then do a loop over that list (thus ensuring you only loop as many times as you absolutely have to) to access each quantity field. For example: Hope that makes some sense. I'm REALLY tired, so I may be overstating the obvious... I'm also beginning to see things that aren't there. Anyways, if you need anything clarified just let me know. I've used this approach on a number of different applications, and it seems to work pretty darn good. Regards, Seth Petry-Johnson Argo Enterprise and Associates --=_NextPart_000_0125_01BFDF10.CC8062E0 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIII+DCCAqsw ggIUoAMCAQICAwK37jANBgkqhkiG9w0BAQQFADCBlDELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdl c3Rlcm4gQ2FwZTEUMBIGA1UEBxMLRHVyYmFudmlsbGUxDzANBgNVBAoTBlRoYXd0ZTEdMBsGA1UE CxMUQ2VydGlmaWNhdGUgU2VydmljZXMxKDAmBgNVBAMTH1BlcnNvbmFsIEZyZWVtYWlsIFJTQSAx OTk5LjkuMTYwHhcNMDAwNjA5MDQyMTIzWhcNMDEwNjA5MDQyMTIzWjBJMR8wHQYDVQQDExZUaGF3 dGUgRnJlZW1haWwgTWVtYmVyMSYwJAYJKoZIhvcNAQkBFhdzZXRoQGFyZ29lbnRlcnByaXNlLmNv bTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAxA83MlfgaZLW39mrcBRBfRdRa9/UPl6M26qE caUhQcMW66UyxV/Xi7WK0uKqPpYoCyo327ZN92tUFMZNI5kZC6VATN3reeWio2bffeC0bwm4vKzQ fPXnOz7dCOdcrzWZL+Dt/Qf+5VxoMsDtbiwUDg3dASSIWJFFuXNYfo1fyh0CAwEAAaNVMFMwIgYD VR0RBBswGYEXc2V0aEBhcmdvZW50ZXJwcmlzZS5jb20wDAYDVR0TAQH/BAIwADAfBgNVHSMEGDAW gBSIq/Fgg2ZV9ORYx0YdwGG9I9fDjDANBgkqhkiG9w0BAQQFAAOBgQAn4BFAYqTTdeMfQ3T8vPaW W+BLJ02KZX4QMU0iWjf7b2MYYqb0BxwZcr+r5UmT99nGRPVoVKS/OgikLXHm98wPEy8Ye9LZXTTN XF43kwMGPDG2G6urhJvpXI98FFK9biRJzZ28GBqLOPw/vHB57gk8J9YeyRSxWqzTgkwsJSFvEzCC AxQwggJ9oAMCAQICAQswDQYJKoZIhvcNAQEEBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxX ZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRp bmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1Ro YXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1h aWxAdGhhd3RlLmNvbTAeFw05OTA5MTYxNDAxNDBaFw0wMTA5MTUxNDAxNDBaMIGUMQswCQYDVQQG EwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRQwEgYDVQQHEwtEdXJiYW52aWxsZTEPMA0GA1UE ChMGVGhhd3RlMR0wGwYDVQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29u YWwgRnJlZW1haWwgUlNBIDE5OTkuOS4xNjCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAs2la l9TQFgt6tcVd6SGcI3LNEkxL937Px/vKciT0QlKsV5Xje2F6F4Tn/XI5OJS06u1lp5IGXr3gZfYZ u5R5dkw+uWhwdYQc9BF0ALwFLE8JAxcxzPRB1HLGpl3iiESwiy7ETfHw1oU+bPOVlHiRfkDpnNGN FVeOwnPlMN5G9U8CAwEAAaM3MDUwEgYDVR0TAQH/BAgwBgEB/wIBADAfBgNVHSMEGDAWgBRyScJz NMZV9At2coF+d/SH58ayDjANBgkqhkiG9w0BAQQFAAOBgQBrxlnpMfrptuyxA9jfcnL+kWBI6sZV 3XvwZ47GYXDnbcKlN9idtxcoVgWL3Vx1b8aRkMZsZnET0BB8a5FvhuAhNi3B1+qyCa3PLW3Gg1Kb +7v+nIed/LfpdJLkXJeu/H6syg1vcnpnLGtz9Yb5nfUAbvQdB86dnoJjKe+TCX5V3jCCAy0wggKW oAMCAQICAQAwDQYJKoZIhvcNAQEEBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJu IENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAm BgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhh d3RlLmNvbTAeFw05NjAxMDEwMDAwMDBaFw0yMDEyMzEyMzU5NTlaMIHRMQswCQYDVQQGEwJaQTEV MBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0 ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQw IgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNv bmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBANRp19Sw lGRbcelH2AxRtupykbCEXn0tDY97Et+FJXUodDpCLG
RE: How to do multiple updates in one submit click
You should look into using WDDX. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Kear Sent: Sunday, June 25, 2000 11:10 PM To: [EMAIL PROTECTED] Subject: How to do multiple updates in one submit click I'm building an application that's kind of like a menu. There might be a hundred items in this menu, and people will be ordering dozens of items in a single order. I'd like to have them enter the order in one go instead of having to click and wait for the page to update at each item, the way a regular shopping cart does. What 'd like to be able to do is have them go down the page, entering quantities against the items they want, and then process the order on teh one click. Each item will go in a orders table with the following fields: [invoicenumber] [itemcode] [quantity] and presumably a [transid] field to index this table. How can I code the CF action page to do these multiple inserts into the orders table? The form will look something like this: Item code Quantity Description Price per unit #Itemcode1# #Description1# #Priceperunit# more items - up to 100 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: How to do multiple updates in one submit click
Is WDDX going to be faster then one of the methods already mentioned here. I am just curious, as I am always looking for faster ways to do things, as load times are still a big factor on the internet. > -Original Message- > From: Steve Bernard [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 26, 2000 10:02 AM > To: [EMAIL PROTECTED] > Subject: RE: How to do multiple updates in one submit click > > > You should look into using WDDX. > > Steve > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Michael Kear > Sent: Sunday, June 25, 2000 11:10 PM > To: [EMAIL PROTECTED] > Subject: How to do multiple updates in one submit click > > > I'm building an application that's kind of like a menu. There > might be a hundred items in this menu, and people will be ordering dozens > of items in a single order. I'd like to have them enter the order in one > go instead of having to click and wait for the page to update at each > item, the way a regular shopping cart does. > > What 'd like to be able to do is have them go down the page, entering > quantities against the items they want, and then process the order on teh > one click. > > Each item will go in a orders table with the following fields: > [invoicenumber] [itemcode] [quantity] and presumably a [transid] field to > index this table. > > How can I code the CF action page to do these multiple inserts into > the orders table? > > > The form will look something like this: > > > > > > Item code > Quantity > Description > Price per unit > > > > > #Itemcode1# > > > #Description1# > #Priceperunit# > > > > more items - up to 100 > > > > > > > > > > -- > -- > -- > Archives: http://www.eGroups.com/list/cf-talk > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > > -- > > Archives: http://www.eGroups.com/list/cf-talk > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf _talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: How to do multiple updates in one submit click
Hmmm, faster? I'm not sure about that but, it would allow you to manipulate the data on the server side as a native query object or structure if you converted it to such. Either way you will have to write JS that allows editing of the various arrays that will be used on the client side but you can use WDDX to create the initial arrays that will hold the order information. This method will prevent you from having to parse the presumably large and unorganized data that would be returned if you just use the formfields list or pass a ton of FORM. data. WDDX will allow you to pass a "complex object" back to the server. Steve -Original Message- From: Bill Killillay [mailto:[EMAIL PROTECTED]] Sent: Monday, June 26, 2000 10:27 AM To: [EMAIL PROTECTED] Subject: RE: How to do multiple updates in one submit click Is WDDX going to be faster then one of the methods already mentioned here. I am just curious, as I am always looking for faster ways to do things, as load times are still a big factor on the internet. > -Original Message- > From: Steve Bernard [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 26, 2000 10:02 AM > To: [EMAIL PROTECTED] > Subject: RE: How to do multiple updates in one submit click > > > You should look into using WDDX. > > Steve > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Michael Kear > Sent: Sunday, June 25, 2000 11:10 PM > To: [EMAIL PROTECTED] > Subject: How to do multiple updates in one submit click > > > I'm building an application that's kind of like a menu. There > might be a hundred items in this menu, and people will be ordering dozens > of items in a single order. I'd like to have them enter the order in one > go instead of having to click and wait for the page to update at each > item, the way a regular shopping cart does. > > What 'd like to be able to do is have them go down the page, entering > quantities against the items they want, and then process the order on teh > one click. > > Each item will go in a orders table with the following fields: > [invoicenumber] [itemcode] [quantity] and presumably a [transid] field to > index this table. > > How can I code the CF action page to do these multiple inserts into > the orders table? > > > The form will look something like this: > > > > > > Item code > Quantity > Description > Price per unit > > > > > #Itemcode1# > > > #Description1# > #Priceperunit# > > > > more items - up to 100 > > > > > > > > > > -- > -- > -- > Archives: http://www.eGroups.com/list/cf-talk > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > > -- > > Archives: http://www.eGroups.com/list/cf-talk > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf _talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: How to do you pull information from the PC?
Hi anon, >I need to fill in a field with the last user logged in. This is an >intranet site and I need to keep track of who modifies the record. How do >you pull this kind of information from the machine? I am not quite sure what you are asking but there is an excellent custom tagset at: http://www.intrafoundation.com/shareware.html that queries the NT system for who is logged in and all sorts of things in that area. We cannot recommend them enuf, none of the reliablity problems you get with the inbuilt cfsecurity stuff. -- Yours, Kym -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: How to do you pull information from the PC?
Thanks for your responses. I am sorry I did not do a very good job of explaining my question. I saved this code(see below) from a few months back because I knew I would need to do something like this but I could not get it to work. I am sure it is something simple but I can not figure it out and have not found a reference that explained it. I assumed the datasource="#ActiveDSN#" would be the datasource to my SQL7 table using cf4.5 on an NT workstation but it did not work so I tried to use it exactly as it is written and that did not work. I am trying to find a reference to explain how to do this, can you help? >From Angelo McComas on 12/9/99 to another person on the list: When you authenticate a user, their user name will show up in the cgi scope variable "auth_user" -- you can pass that to the database like so: // // -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: How to do this join? (MySQL 4.0 can't do subqueries...)
> Hi, all. > > I've tried for hours to figure this out, but can't get it... > > 3 tables... > > Newsletter_Series, Newsletter_Subscribers, Newsletter_Subscriptions > > I need to run a query to get all Newsletter_Series > which don't have Subscriptions for a particular Subscriber... > > Primary Key Relationships: > > - Newsletter_Series.Series_ID / Newsletter_Subscriptions.Series_ID > - Newsletter_Subscriptions.Subscriber_ID / > Newsletter_Subscribers.Subscriber_ID > > > Can't do subqueries (aaargh!), so I'm left with a join. > > Something like, > > > > Select newsletter_series.Series_ID, > newsletter_series.Series_Title, > from newsletter_series > > left join > > newsletter_subscriptions, newsletter_subscribers > on (newsletter_subscriptions.Subscriber_ID <> > '#CurrentSubscriber.Subscriber_ID#' > and newsletter_subscription.Series_ID <> > newsletter_series.Series_ID) > where newsletter_series.Series_ID is null > > Try something like this (not tested): SLECT newsletter_series.Series_ID, Newsletter_Subscribers.Subscriber_ID FROM newsletter_series LEFT JOIN newsletter_subscriptions ON newsletter_series.series_id = Newsletter_Subscriptions.Series_ID LEFT JOIN Newsletter_Subscribers ON Newsletter_Subscribers.Subscriber_ID = Newsletter_Subscriptions.Subscriber_ID WHERE newsletter_subscriptions.Subscriber_ID IS NULL AND Newsletter_Subscribers.subscriber_name = 'mike' AND Newsletter_Series.Series_ID = 100 -- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: How to do this join? (MySQL 4.0 can't do subqueries...)
MySql now supports subqueries: http://dev.mysql.com/doc/mysql/en/Subqueries.html To see how to rewrite some subquery SQL as joins, go to http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html SELECT newsletter_series.Series_ID, newsletter_series.Series_Title FROM newsletter_series LEFT JOIN newsletter_subscriptions ON newsletter_series.Series_ID = newsletter_subscription.Series_ID AND newsletter_subscriptions.Subscriber_ID = '#CurrentSubscriber.Subscriber_ID#' WHERE newsletter_subscriptions.Subscriber_ID IS NULL --- Neculai Macarie <[EMAIL PROTECTED]> wrote: > > Hi, all. > > > > I've tried for hours to figure this out, but can't > get it... > > > > 3 tables... > > > > Newsletter_Series, Newsletter_Subscribers, > Newsletter_Subscriptions > > > > I need to run a query to get all Newsletter_Series > > which don't have Subscriptions for a particular > Subscriber... > > > > Primary Key Relationships: > > > > - Newsletter_Series.Series_ID / > Newsletter_Subscriptions.Series_ID > > - Newsletter_Subscriptions.Subscriber_ID / > > Newsletter_Subscribers.Subscriber_ID > > > > > > Can't do subqueries (aaargh!), so I'm left with a > join. > > > > Something like, > > > > > > > > Select newsletter_series.Series_ID, > > newsletter_series.Series_Title, > > from newsletter_series > > > > left join > > > > newsletter_subscriptions, > newsletter_subscribers > > on (newsletter_subscriptions.Subscriber_ID <> > > '#CurrentSubscriber.Subscriber_ID#' > > and newsletter_subscription.Series_ID <> > > newsletter_series.Series_ID) > > where newsletter_series.Series_ID is null > > > > > = I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: How to do this join? (MySQL 4.0 can't do subqueries...)
Newsletter_Series which don't have Subscriptions for a particular Subscriber... SELECT newsletter_series.Series_ID, newsletter_series.Series_Title FROM newsletter_series WHERE newsletter_subscriptions.Subscriber_ID = '#CurrentSubscriber.Subscriber_ID#' AND newsletter_subscription.Series_ID is null I believe this query will solve your problem. Can you send your table desctriptions? Also, If there is no subscription, I am assuming the newsletter_subscription.Series_ID would be null. Ryan Duckworth Macromedia Coldfusion Certified Professional Uhlig Communications 10983 Granada Lane Overland Park, KS 66207 (913) 754-4272 -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 9:40 PM To: CF-Talk Subject: How to do this join? (MySQL 4.0 can't do subqueries...) Hi, all. I've tried for hours to figure this out, but can't get it... 3 tables... Newsletter_Series, Newsletter_Subscribers, Newsletter_Subscriptions I need to run a query to get all Newsletter_Series which don't have Subscriptions for a particular Subscriber... Primary Key Relationships: - Newsletter_Series.Series_ID / Newsletter_Subscriptions.Series_ID - Newsletter_Subscriptions.Subscriber_ID / Newsletter_Subscribers.Subscriber_ID Can't do subqueries (aaargh!), so I'm left with a join. Something like, Select newsletter_series.Series_ID, newsletter_series.Series_Title, from newsletter_series left join newsletter_subscriptions, newsletter_subscribers on (newsletter_subscriptions.Subscriber_ID <> '#CurrentSubscriber.Subscriber_ID#' and newsletter_subscription.Series_ID <> newsletter_series.Series_ID) where newsletter_series.Series_ID is null I've tried a lot of variations and read the documentation, but can't get the results... Help, anyone? Rick Rick Faircloth WhiteStoneMedia.com -- Outgoing mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.253 / Virus Database: 263.3.8 - Release Date: 6/30/2004 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: How to do this join? (MySQL 4.0 can't do subqueries...)
Thanks for the help... Haven't been able to get it to work, yet...but I'm still trying. One problem, I believe, is the last line of your SQL: "and Newsletter_Series.Series_ID = 100" I know that 100 is not to be taken literally, but the problem is in defining the Series_ID at all. The query is supposed to identify *all* Series_ID's, not one specific Series_ID. With or without that line, however, I have haven't been able to get the left joins to work. I'll keep trying... Rick -Original Message- From: Neculai Macarie [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 6:21 AM To: CF-Talk Subject: Re: How to do this join? (MySQL 4.0 can't do subqueries...) > Hi, all. > > I've tried for hours to figure this out, but can't get it... > > 3 tables... > > Newsletter_Series, Newsletter_Subscribers, Newsletter_Subscriptions > > I need to run a query to get all Newsletter_Series > which don't have Subscriptions for a particular Subscriber... > > Primary Key Relationships: > > - Newsletter_Series.Series_ID / Newsletter_Subscriptions.Series_ID > - Newsletter_Subscriptions.Subscriber_ID / > Newsletter_Subscribers.Subscriber_ID > > > Can't do subqueries (aaargh!), so I'm left with a join. > > Something like, > > > > Select newsletter_series.Series_ID, > newsletter_series.Series_Title, > from newsletter_series > > left join > > newsletter_subscriptions, newsletter_subscribers > on (newsletter_subscriptions.Subscriber_ID <> > '#CurrentSubscriber.Subscriber_ID#' > and newsletter_subscription.Series_ID <> > newsletter_series.Series_ID) > where newsletter_series.Series_ID is null > > Try something like this (not tested): SLECT newsletter_series.Series_ID, Newsletter_Subscribers.Subscriber_ID FROM newsletter_series LEFT JOIN newsletter_subscriptions ON newsletter_series.series_id = Newsletter_Subscriptions.Series_ID LEFT JOIN Newsletter_Subscribers ON Newsletter_Subscribers.Subscriber_ID = Newsletter_Subscriptions.Subscriber_ID WHERE newsletter_subscriptions.Subscriber_ID IS NULL AND Newsletter_Subscribers.subscriber_name = 'mike' AND Newsletter_Series.Series_ID = 100 -- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: How to do this join? (MySQL 4.0 can't do subqueries...)
Thanks for the reply... Yes, MySQL does now support subqueries, but not the production version. I won't be going to 4.1 until it's in production, so I'm still using 4.0.x, which doesn't support subqueries. And, the first thing I did was to consult the MySQL documentation on the precise pages your refer to, but I couldn't make my query work from the examples given... Your query below worked...I tried almost the exact same version, but I don't think I tried the WHERE clause that you used...and it's the last line that gives trouble logically... "WHERE newsletter_subscriptions.Subscriber_ID IS NULL" I've got to just sit and ponder the logic of the that statement in relation to the join. I use joins very seldom, so they give me fits every time I have to use one... But that's enough rambling! Thanks for the solution! Rick -Original Message- From: I-Lin Kuo [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 9:20 AM To: CF-Talk Subject: Re: How to do this join? (MySQL 4.0 can't do subqueries...) MySql now supports subqueries: http://dev.mysql.com/doc/mysql/en/Subqueries.html To see how to rewrite some subquery SQL as joins, go to http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html SELECT newsletter_series.Series_ID, newsletter_series.Series_Title FROM newsletter_series LEFT JOIN newsletter_subscriptions ON newsletter_series.Series_ID = newsletter_subscription.Series_ID AND newsletter_subscriptions.Subscriber_ID = '#CurrentSubscriber.Subscriber_ID#' WHERE newsletter_subscriptions.Subscriber_ID IS NULL --- Neculai Macarie <[EMAIL PROTECTED]> wrote: > > Hi, all. > > > > I've tried for hours to figure this out, but can't > get it... > > > > 3 tables... > > > > Newsletter_Series, Newsletter_Subscribers, > Newsletter_Subscriptions > > > > I need to run a query to get all Newsletter_Series > > which don't have Subscriptions for a particular > Subscriber... > > > > Primary Key Relationships: > > > > - Newsletter_Series.Series_ID / > Newsletter_Subscriptions.Series_ID > > - Newsletter_Subscriptions.Subscriber_ID / > > Newsletter_Subscribers.Subscriber_ID > > > > > > Can't do subqueries (aaargh!), so I'm left with a > join. > > > > Something like, > > > > > > > > Select newsletter_series.Series_ID, > > newsletter_series.Series_Title, > > from newsletter_series > > > > left join > > > > newsletter_subscriptions, > newsletter_subscribers > > on (newsletter_subscriptions.Subscriber_ID <> > > '#CurrentSubscriber.Subscriber_ID#' > > and newsletter_subscription.Series_ID <> > > newsletter_series.Series_ID) > > where newsletter_series.Series_ID is null > > > > > = I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: How to do this join? (MySQL 4.0 can't do subqueries...)
Thanks for the reply, Ryan... I-Lin provided a query solution using a left join. I tried your query, but it didn't return the correct results. Tried changing the "AND newsletter_subscription.Series_ID is null" to "AND newsletter_subscription.Subscriber_ID is null", like was included as part of the join query that I-Lin proposed, but that didn't help. Thanks for your help, however... It's times like these that I am thankful for CF-Talk... I'm going to see if I can't make at least a small donation to Mike, every time I get a solution from this list that I couldn't solve. Imagine...if everyone did that, Mike could make a lot more... :o) Rick -Original Message- From: Ryan Duckworth [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:56 AM To: CF-Talk Subject: RE: How to do this join? (MySQL 4.0 can't do subqueries...) Newsletter_Series which don't have Subscriptions for a particular Subscriber... SELECT newsletter_series.Series_ID, newsletter_series.Series_Title FROM newsletter_series WHERE newsletter_subscriptions.Subscriber_ID = '#CurrentSubscriber.Subscriber_ID#' AND newsletter_subscription.Series_ID is null I believe this query will solve your problem. Can you send your table desctriptions? Also, If there is no subscription, I am assuming the newsletter_subscription.Series_ID would be null. Ryan Duckworth Macromedia Coldfusion Certified Professional Uhlig Communications 10983 Granada Lane Overland Park, KS 66207 (913) 754-4272 -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 9:40 PM To: CF-Talk Subject: How to do this join? (MySQL 4.0 can't do subqueries...) Hi, all. I've tried for hours to figure this out, but can't get it... 3 tables... Newsletter_Series, Newsletter_Subscribers, Newsletter_Subscriptions I need to run a query to get all Newsletter_Series which don't have Subscriptions for a particular Subscriber... Primary Key Relationships: - Newsletter_Series.Series_ID / Newsletter_Subscriptions.Series_ID - Newsletter_Subscriptions.Subscriber_ID / Newsletter_Subscribers.Subscriber_ID Can't do subqueries (aaargh!), so I'm left with a join. Something like, Select newsletter_series.Series_ID, newsletter_series.Series_Title, from newsletter_series left join newsletter_subscriptions, newsletter_subscribers on (newsletter_subscriptions.Subscriber_ID <> '#CurrentSubscriber.Subscriber_ID#' and newsletter_subscription.Series_ID <> newsletter_series.Series_ID) where newsletter_series.Series_ID is null I've tried a lot of variations and read the documentation, but can't get the results... Help, anyone? Rick Rick Faircloth WhiteStoneMedia.com -- Outgoing mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.253 / Virus Database: 263.3.8 - Release Date: 6/30/2004 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: How to do this join? (MySQL 4.0 can't do subqueries...)
--- Rick Faircloth <[EMAIL PROTECTED]> wrote: > Your query below worked...I tried almost the exact > same version, but I don't > think I tried the WHERE clause that you used...and > it's the last line that > gives trouble logically... > > "WHERE newsletter_subscriptions.Subscriber_ID IS > NULL" That's actually in the examples on the page I referred to. It functions as the logical equivalent of a NOT IN if you're using a subquery. > I've got to just sit and ponder the logic of the > that statement in relation > to the join. I use joins very seldom, so they give > me fits every time I > have > to use one... > > But that's enough rambling! > Thanks for the solution! > > Rick > -Original Message- > From: I-Lin Kuo [mailto:[EMAIL PROTECTED] > > > MySql now supports subqueries: > http://dev.mysql.com/doc/mysql/en/Subqueries.html > > To see how to rewrite some subquery SQL as joins, > go > to > > http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html > > SELECT newsletter_series.Series_ID, > newsletter_series.Series_Title > FROM newsletter_series > LEFT JOIN newsletter_subscriptions > ON newsletter_series.Series_ID = > newsletter_subscription.Series_ID > AND newsletter_subscriptions.Subscriber_ID = > '#CurrentSubscriber.Subscriber_ID#' > WHERE newsletter_subscriptions.Subscriber_ID IS > NULL > = I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]