Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Coldfusion and MySQL Data Manipulation

Posted on 2007-11-26
4
Medium Priority
?
255 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
  • 3
4 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month7 days, 11 hours left to enroll

824 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