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

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.

Goddess6942Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrichmonCommented:
so you are trying to isolate any URL variable or only the variable named 'id' in the URL string?
0
Goddess6942Author Commented:
only the variable named 'id'
0
mrichmonCommented:
Then you can use a regular expression find.....
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

trailblazzyr55Commented:
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
Goddess6942Author Commented:
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
Goddess6942Author Commented:
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
trailblazzyr55Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trailblazzyr55Commented:
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
trailblazzyr55Commented:
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
Goddess6942Author Commented:
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
Goddess6942Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.