Solved

Coldfusion and MySQL Data Manipulation

Posted on 2007-11-26
4
234 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
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Php  POSTGRES  "  IN SIDE "  " 4 59
Pick the lowest value in between value from the array in PHP ? 14 56
Need help with a query 6 67
Problem with data displaying 2 22
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

21 Experts available now in Live!

Get 1:1 Help Now