Solved

Coldfusion and MySQL Data Manipulation

Posted on 2007-11-26
4
243 Views
Last Modified: 2012-06-21
I have 1 table with a field called "product_name" and another field called "product_Name_Display"

Field (product_Name_Display) is for HTML formatted text using subscripts and superscripts

Field (Product_Name) is suppose to be just the text without html tags so it's searchable text.

There is no info/data in Product_Name right now but I would like to use attached script to update "Product_Name" field with the same data as in "product_Name_Display" but clear out the html tags.

Just need help with the MySQL command using the coldfusion script to update the "product_Name" field.

Thanks



<cfscript>
		/**
		 * Removes HTML from the string.
		 * v2 - Mod by Steve Bryant to find trailing, half done HTML.
		 *
		 * @param string      String to be modified. (Required)
		 * @return Returns a string.
		 * @author Raymond Camden (ray@camdenfamily.com)
		 * @version 2, August 10, 2007
		 */
		function stripHTML(str) {
			return REReplaceNoCase (str, "(<|^)[^>]*(>|$)" , "", "ALL");
		}
		</cfscript>
		<cfset product_Name_Display = StripHTML(product_Name)>

Open in new window

0
Comment
Question by:brihol44
[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
  • 3
4 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20355862
Because its a CF function you'll have to run a query to extract all of the records first

<cfquery name="getData" datsource="yourDSN">
     SELECT  TheUniqueRecordID, product_Name_Display
     FROM YourTable
</cfquery>

Then loop through the query, and update each record with the updated value.  Change the cfsqltypes below as needed.

If your table contains a very large number of records you might want to try a different approach. (ie looping through 50,000 records might take a while)

<cfloop query="getData">
   <cfquery name="updateData" datsource="yourDSN">
        UPDATE YourTable
        SET product_Name = <cfqueryparam value="#StripHTML(product_Name_Display)#"
                        cfsqltype="cf_sql_varchar">
        WHERE  TheUniqueRecordID = <cfqueryparam value="#TheUniqueRecordID#"
                        cfsqltype="your column type">
   </cfquery>
</cfloop>
0
 

Author Comment

by:brihol44
ID: 20356033
Thanks, That works but I can't seem to figure out why the script is deleting everything outside the <sub></sub> scripts.

So I had a variable of 9-Acridinecarboxylic acid, succinimidyl este<sub>r</sub> but when the script was added it left a variable of just "r" deleting everything except the "r" character.

0
 

Author Comment

by:brihol44
ID: 20356051
I tried the attached script. It looks like it strips everything before any tags. I need to find something will strip just tags. Unless you can see how I can configure the current script.

Thanks agx!!
<cfset product_Name = " 7777<sub>44444</sub>">
 
<cfscript>
                /**
                 * Removes HTML from the string.
                 * v2 - Mod by Steve Bryant to find trailing, half done HTML.
                 *
                 * @param string      String to be modified. (Required)
                 * @return Returns a string.
                 * @author Raymond Camden (ray@camdenfamily.com)
                 * @version 2, August 10, 2007
                 */
                function stripHTML(str) {
                        return REReplaceNoCase (str, "(<|^)[^>]*(>|$)" , "", "ALL");
                }
                </cfscript>
                <cfset product_Name_Display = StripHTML(product_Name)>
                
                <cfoutput>#product_Name_Display#</cfoutput>

Open in new window

0
 

Author Comment

by:brihol44
ID: 20356129
This works for striping out just html tags. Thanks once again AGX!
<cfscript>
/**
 * Removes All HTML from a string removing tags, script blocks, style blocks, and replacing special character code.
 * 
 * @param source 	 String to format. (Required)
 * @return Returns a string. 
 * @author Scott Bennett (scott@coldfusionguy.com) 
 * @version 1, November 14, 2007 
 */
function removeHTML(source){
	
	// Remove all spaces becuase browsers ignore them
	var result = ReReplace(trim(source), "[[:space:]]{2,}", " ","ALL");
	
	// Remove the header
	result = ReReplace(result, "<[[:space:]]*head.*?>.*?</head>","", "ALL");
	
	// remove all scripts
	result = ReReplace(result, "<[[:space:]]*script.*?>.*?</script>","", "ALL");
	
	// remove all styles
	result = ReReplace(result, "<[[:space:]]*style.*?>.*?</style>","", "ALL");
	
	// insert tabs in spaces of <td> tags
	result = ReReplace(result, "<[[:space:]]*td.*?>","	", "ALL");
	
	// insert line breaks in places of <BR> and <LI> tags
	result = ReReplace(result, "<[[:space:]]*br[[:space:]]*>",chr(13), "ALL");
	result = ReReplace(result, "<[[:space:]]*li[[:space:]]*>",chr(13), "ALL");
	
	// insert line paragraphs (double line breaks) in place
	// if <P>, <DIV> and <TR> tags
	result = ReReplace(result, "<[[:space:]]*div.*?>",chr(13), "ALL");
	result = ReReplace(result, "<[[:space:]]*tr.*?>",chr(13), "ALL");
	result = ReReplace(result, "<[[:space:]]*p.*?>",chr(13), "ALL");
	
	// Remove remaining tags like <a>, links, images,
	// comments etc - anything thats enclosed inside < >
	result = ReReplace(result, "<.*?>","", "ALL");
	
	// replace special characters:
	result = ReReplace(result, "&nbsp;"," ", "ALL");
	result = ReReplace(result, "&bull;"," * ", "ALL");    
	result = ReReplace(result, "&lsaquo;","<", "ALL");        
	result = ReReplace(result, "&rsaquo;",">", "ALL");        
	result = ReReplace(result, "&trade;","(tm)", "ALL");        
	result = ReReplace(result, "&frasl;","/", "ALL");        
	result = ReReplace(result, "&lt;","<", "ALL");        
	result = ReReplace(result, "&gt;",">", "ALL");        
	result = ReReplace(result, "&copy;","(c)", "ALL");        
	result = ReReplace(result, "&reg;","(r)", "ALL");    
	
	// Remove all others. More special character conversions
	// can be added above if needed
	result = ReReplace(result, "&(.{2,6});", "", "ALL");    
	
	// Thats it.
	return result;
 
}
</cfscript>

Open in new window

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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