Hey everyone, I am on a month long project on a coldfusion script I have to work on. Bare with my noobness here Basically here is the jist of it. -- I have a huge sql database filled with various random people information, such as their phone #"s, email addresses, name, etc...this database it might be going off from could be anywhere from 8,000-15,000 users on it that it will be going off from. --From time to time I will get a list of say 200-300 email addresses sometimes it can go up to 3000 email addresses from the database whose emails are bounced. --Now what I am trying to do here is ..making a script where when you input this large list of email addresses, whether it be .txt or just copying it and pasting it into a form with all the bad email addresses...you hit submit..and the system automatically would search the user from its main database to see if it finds the email address, and if it does it retrieves the phone # for that specific person that's stored on the database and outputs it back into the page so every email that's been submitted returns back with a email address and phone # right next to each other... How complicated would this be to make such a script? ANY help / suggestions / advice / or the jist of what I'm going to be dealing with would be MUCH appreciated. Thanks
If I understand you correctly then I think this will work for you... Here is what the query might look like: <cfquery datasource="YOURDATASOURCE" name="search"> SELECT EMAIL, PHONENUMBER FROM YOUR_DATA_TABLE_NAME WHERE EMAIL = '#FORM.EMAIL#' </cfquery> Code (markup): Simple form to collect the email address: <form action="actionpage.cfm" method="post" <input type="text" name="email"/> <input type="submit" name="Submit"/> </form> Code (markup): Heres how you display the output: <cfoutput query="search"> #email# | #phonenumber# </cfoutput> Code (markup): This is about as simple as it gets. You will need to add html
Hi, Thank you very much for your helpful response. I have been researching for hours, trying out different coding and so far here is where I am. I have been able to sucessfully create the form to output the results to the cfm page with the email and phone number. I can enter in one email address in the search bar and it returns the result fine. My question is, how can I make it so - I am able to enter multiple email addresses in the search bar ....say like maybe a couple hundred and all of em gets outputted, row by row.... I will post my coding in a bit
My problem right now is getting it to query multiple email addresses in the same text box and outputting that - search.cfm [this is where I would paste all say 1,000-2,000 email addresses <!--- Security Check ---> <cfif NOT parameterExists(cookie.enterflag)> <CFLOCATION URL="index.cfm"> </cfif> <HTML> <HEAD> <TITLE>Bad Emails / Phone Compiler v0.1</TITLE> <script language="JavaScript" > function checkText(Item, Value) { var next=Item.tabIndex; for(i=1; i < document.getElementById("searchForm101").length; i++) { if(i == next) { // leave textfield as is. } else { document.getElementById("searchForm101").elements[i].value = ""; // Erase other fields } } document.getElementById("searchForm101").elements[document.getElementById("searchForm101").length - 1].value = "Search"; // Update the entry } </script> <script language="JavaScript" > function validate() { var count=0; for(i=1; i < document.getElementById("searchForm101").length; i++) { if (document.getElementById("searchForm101").elements[i].value == "") { count++; } } if(count == (document.getElementById("searchForm101").length - 2) ) { var newHTML = "<span style='color:#ff0000'>" + 'Please enter text.' + "</span>"; document.getElementById('ErrorMessage').innerHTML = newHTML; return false; } else { return true; } } </script> </HEAD> <BODY bgcolor="ffffff"> <CENTER> <TABLE cellpadding="0" cellspacing="0" border="0" width="300" bgcolor="dddddd"> <TR bgcolor="008800"> <TD> <TABLE cellpadding="0" cellspacing="0" border="0" width="100%"> <TR> <TD align="left"><a href="<cfoutput>#blah_blah_com#</cfoutput>/"><font color="ffffff"><b>Main Menu</b></font></a></TD> <TD align="right"><font color="ffffff"><b>Bad Emails / Phone Compiler v0.1</b></font></TD> </TR> </TABLE> </TD> </TR> <tr> <div id="ErrorMessage" >  </div> </tr> <FORM method="post" name="searchform" action="getresults.cfm" id="searchForm101"> <INPUT type="hidden" name="searchflag" value="2" /> <TR> <TD><b>Email:</b><BR> <textarea name="email" cols="80" rows="30" tabindex="1" onChange="checkText(this, this.value)" onClick="checkText(this, this.value)"></textarea></TD> </TR> <TD align="center"><Input type="submit" name="submitbutton" value="Search" onClick="return validate()" /></TD> </TR> </FORM> <!--- <FORM method="post" action="getresults.cfm"> <INPUT type="hidden" name="searchflag" value="1"> <TR> <TD align="center"><br><br><hr><br><INpUT type="submit" name="SHOWALL" value="Show ALL"></TD> <TR> </FORM> ---> </TABLE> </CENTER> </BODY> </HTML> Code (markup): This is the getresults.cfm page, where whatever is queried from the email address submission section is outputted to. <cfparam name="searchflag" default="-1"> <!--- <cfparam name="email" default="0"> ---> <CFIF IsDefined("Form.email")and (Form.email NEQ "")> <cfoutput> <CFQUERY name="GetInfo" datasource="#ODBC_Datasource#"> Select Gaming_clients.email, Gaming_clients.phone from Gaming_clients WITH (NOLOCK) WHERE Gaming_clients.email IN ('#trim(email)#') ORDER by Gaming_clients.lastname </CFQUERY> </cfoutput> </cfif> <HTML> <HEAD> <TITLE>Bad Emails / Phone Compiler v0.1 Results -</TITLE> </HEAD> <CFOUTPUT> <INPUT type="hidden" name="searchflag" value="#searchflag#"> </CFOUTPUT> <BODY bgcolor="ffffff"> <CENTER> <TABLE bgcolor="dddddd" cellpadding="0" cellspacing="0" border="0" width="500"> <TR bgcolor="008800"> <TD colspan="3"> <TABLE cellpadding="0" cellspacing="0" border="0" width="100%"> <TR> <TD align="left"><a href="<cfoutput>#extranet_casinocareers_com#</cfoutput>/"><font color="ffffff"><b>Main Menu</b></font></a></TD> <TD align="right"><font color="ffffff"><b>Bad Emails v0.1</b></font></TD> </TR> <tr><td colspan="2" > </td></tr> </TABLE> </TD> </TR> <cfif isDefined("GetInfo.Recordcount")> <CFIF #GetInfo.Recordcount# IS 0> <TR> <TD colspan="3" align="center">No matches were found</TD> </TR> <TR> <TD colspan="3" align="center"><a href="search.cfm">New Search</a></TD> </TR> <CFELSE> <TR> <TD><b>Email</b></TD> <td><b>Phone</b></td> </TR> <CFOUTPUT query="GetInfo"> <TR> <TD>#email#</TD> <td>#phone#</td> </TR> </CFOUTPUT> <TR> <TD colspan="3"> <TABLE cellpadding="0" cellspacing="0" border="0" width="100%"> <TR> </FORM> <FORM method="post" action="search.cfm"> <TD align="center"><INPUT type="submit" value="Go Back"></TD> </FORM> </TR> </TABLE> </TD> </TR> </CFIF> </cfif> <!--- <cfelse> <TR> <TD colspan="3">You may have pushed the "enter" key on the previous page - please <a href="javascript:history.go(-1);">go back</a> and push one of the 2 search buttons.</TD> </TR> </cfif> ---> </TABLE> </CENTER> </BODY> </HTML> Code (markup):
You will nned to do a search on "array from textarea" and then you need to sreach on how to run an SQL query with an array, Basicly you need a way of using a textarea where you can enter either a comma separated list or even line by line, and when you push submit you get an array as a post variable. try a search on "coldfusion set array" Also you can search the livedocs for coldfusion. Sorry I can't be more help, I really havent had to use an array yet in either of my sites. I think I may have been avoiding them because I dont tottaly understand how to use them. But using an array can be very helpful and more efficient. Here a link to another post I created trying to learn how to get an array from a textarea. Hopefully someone helps us out. http://forums.digitalpoint.com/showthread.php?t=961898 Good luck,
Take your form input, loop through it as a list (your input is a comma delimited list), trim the listGetAt value of any unexpected data (spaces, and stuff like that), validate the data (you're expecting email addresses, so make sure they're email addresses), and then throw them into another, clean list that's comma delimited for your cfqueryparam to use in your sql query. Then, in your sql query, specify (in your where clause) where email in (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#variables.newEmailList#" list="true" separator=",">) and your query will return what you're expecting.