?
Solved

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

Posted on 2005-03-28
11
Medium Priority
?
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

800 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