Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Isolating data within a recordset and replacing it with information matched from a query

Posted on 2005-03-28
11
Medium Priority
?
166 Views
Last Modified: 2013-12-24
I have a query that is outputting content that in some cases contains URLs.  These URLs, in many cases, are formated as index.cfm?id=75, for example.  I am trying to isolate the 75 and have it matched to a record in another query so that I can then replace the 75 with the record found in that query.  I have a vague idea of how this can be done but I'm not really sure how to execute it.  Any help with this would be greatly appreciated.

0
Comment
Question by:Goddess6942
  • 5
  • 4
  • 2
11 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 13646109
so you are trying to isolate any URL variable or only the variable named 'id' in the URL string?
0
 

Author Comment

by:Goddess6942
ID: 13646336
only the variable named 'id'
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13647729
Then you can use a regular expression find.....
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13650594
if your url is for example ....index.cfm?id=75

you can get that variable by doing this...

<cfif isdefined('URL.id')>
   <cfset myID = "#URL.id#">
</cfif>

<cfquery name="myquery" datasource="UrDSN">
SELECT * FROM YourTable
WHERE MyKeyID = '#myID#'
</cfquery>

or...

if your url is for example ....index.cfm?id=75

<cfquery name="myquery" datasource="UrDSN">
SELECT * FROM YourTable
WHERE MyKeyID = '#URL.ID#'
</cfquery>

If you are sending variables through your URL you need to use the URL.Variable, if you are posting from a form to an action page for example, you need to use the FORM.Variable
0
 

Author Comment

by:Goddess6942
ID: 13652075
Uh, I'm not pulling this through a URL variable.  I'm trying to isolate URLs that are being output from a recordset.  For example:

<cfoutput query="getInfo">
#getInfo.copy#
</cfoutput>

Would output something like

<p>This is a bunch of information output that happens to include this URL: http://www.test.com/index.cfm?id=75.  I would like to isolate the id=75 from this output.</p>
0
 

Author Comment

by:Goddess6942
ID: 13652088
mrichmon, I thought about using regular expressions but my experience with them is fairly limited. I know the find() function probably would do what I need, I'm just not certain how I would use it.
0
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 2000 total points
ID: 13653816
Here you go...

Sorry I didn't understand what you were looking for at first, this should be more what you're looking for....

I set "MyURL" as a demo, but you can modify this to your needs, you case also add a switch/case statement for mulitple variables in your URL...

<CFSET MyURL = "http://www.yourdomain.com/index.cfm?id=75&yourOtherValue=222&someOther=var">

<cfscript>
<!---u could add case statement here...--->
      endString = RemoveChars(MyURL,1,REFindNoCase("id=",thiscgi)+2);
                        
      SearchString = REfind("&",endString);
        
      If (SearchString IS 0 ){
            newString = RemoveChars(endString,1,SearchString);
      } Else {
            newString = removechars(endString,1,SearchString-1);
      }
            editString = ReplaceNoCase(endstring, newstring, "");
            finalString = REreplaceNoCase(editString,"[+]", " ","ALL");
      If (finalString IS ""){
            finalString = REreplaceNoCase(endString,"[+]", " ","ALL");
            break;
      }
      
</cfscript>
<cfoutput>#finalstring#</cfoutput>

Regards,
~trail
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13653988
This will return the value of "id" from your URL's, if you move the "id" in the URL it doesn't matter.

If you wanted you could set up a case statement for different variables....

Lets say for instance on one of your pages you wanted to use this script to pull a variable called "PageColor"

and in a different spot you wanted to get a different variable from your URL called "PageSort"

<cfset MyVariable2Use = "what ever variable you want to extract for this page.... example case's are "id", "PageColor", and "PageSort"">
<cfset MyURL = "http://www.test.com/index.cfm?id=75&PageColor=white&PageSort=Down">


<cfscript>
Switch (MyVariable2Use){
      Case "id":
            endString = RemoveChars(MyURL,1,REFindNoCase("id=",thiscgi)+2);
      break;
      Case "PageColor":
            endString = RemoveChars(MyURL,1,REFindNoCase("PageColor=",thiscgi)+9);
      break;
      Case "PageSort":
            endString = RemoveChars(MyURL,1,REFindNoCase("PageSort=",thiscgi)+8);
      break;
}
            SearchString = REfind("&",endString);
                  If (SearchString IS 0 ){
                        newString = RemoveChars(endString,1,SearchString);
                  } Else {
                        newString = removechars(endString,1,SearchString-1);
                  }
                        editString = ReplaceNoCase(endstring, newstring, "");
                        finalString = REreplaceNoCase(editString,"[+]", " ","ALL");
                  If (finalString IS ""){
                        finalString = REreplaceNoCase(endString,"[+]", " ","ALL");
                        break;
                        }
</cfscript>

The the result would be your final string you could use for whatever you'd like...

<cfoutput>#finalstring#</cfoutput>

Regards,
~trail

0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13654106
oops, need to change one thing... from thiscgi to MyURL

at the end of the case statements.... that should do it :o)

<cfscript>
Switch (MyVariable2Use){
     Case "id":
          endString = RemoveChars(MyURL,1,REFindNoCase("id=",MyURL)+2);
     break;
     Case "PageColor":
          endString = RemoveChars(MyURL,1,REFindNoCase("PageColor=",MyURL)+9);
     break;
     Case "PageSort":
          endString = RemoveChars(MyURL,1,REFindNoCase("PageSort=",MyURL)+8);
     break;
}
          SearchString = REfind("&",endString);
               If (SearchString IS 0 ){
                    newString = RemoveChars(endString,1,SearchString);
               } Else {
                    newString = removechars(endString,1,SearchString-1);
               }
                    editString = ReplaceNoCase(endstring, newstring, "");
                    finalString = REreplaceNoCase(editString,"[+]", " ","ALL");
               If (finalString IS ""){
                    finalString = REreplaceNoCase(endString,"[+]", " ","ALL");
                    break;
                    }
</cfscript>
0
 

Author Comment

by:Goddess6942
ID: 13664950
Thanks for the input trailblazzyr55.  I gave your suggestions a try but unfortunately I keep getting errors thrown.  Specifically, "Variable ENDSTRING is undefined."  This is the line that seems to be throwing the error: SearchString = REfind("&",endString);  Any thoughts?  Thanks for your help.
0
 

Author Comment

by:Goddess6942
ID: 13666391
trailblazzyr55, YOU FRIGGEN ROCK!  After doing some messing around with your original code, I was able to get it to do exactly what I wanted.  

My next trick is to take the keyword located in the database based on the information returned from your code and replace the "id=75" with that keyword (if that makes any sense).  I'm going to try and see if I can't figure it out on my own but any suggestions would most definitely be welcome.

Thanks again!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question