• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Coldfusion and MySQL Data Manipulation

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
brihol44
Asked:
brihol44
  • 3
1 Solution
 
_agx_Commented:
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
 
brihol44Author Commented:
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
 
brihol44Author Commented:
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
 
brihol44Author Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now