Solved

Coldfusion and MySQL Data Manipulation

Posted on 2007-11-26
4
232 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now