updating trademark symbol in mysql db using CF 8

[tromanNMV]

Hello all,

I am using CF 8 to update records in a mySQL (ver. 5) database.

The form is a basic update form. The same thing worked fine when using MS Access.

But, the problem I am having is inserting (updating actually) a trademark symbol into the mySQL table.

It adds the symbol fine on the CF form to add a record. Using phpmyadmin, the trademark symbol show up fine. You can even edit the record one(1) time using either a web-based form or with phpmyadmin.  Save the change one time and the trademark symbol is still correct.  The minute you "update" the record a second time it changes to a double quotes symbol (") and truncates any text following the position where the symbol was.  This happens both when editing the form in phpmyadmin and a CF form on the site.

The cCF ode, in part, to save the record is :

-------------------------------------------------------------------

<cfquery  name="EditPromotion" datasource="toppsSQLDB" USERNAME="topps20105" PASSWORD="toppsmysql">   
UPDATE promotions

SET title = <cfif Form.title NEQ "">
  <cfqueryparam value="#form.title#" cfsqltype="CF_SQL_VARCHAR">,
<cfelse>
  <cfqueryparam null="true" cfsqltype="CF_SQL_VARCHAR">,
</cfif>

-------------------------------------------------------------------

The field in mysql is also set to utf-8 as are all the fields.

If you can help, that would be great.

I've spent a couple days on looking for a solution....

thanks!

Tim

[injun__576871_]

I think this is because in the text are quotes (") or other special character ('), and the DB thinks that it is end of the data. We need to escape those characters like we do it with # - adding one more #.

Maybe I am wrong.

[tromanNMV]

Thanks for the response, Alexei...

However, there are no quotes in the field. Am just inserting a product name... however it is a trademarked item... so they will be using the trademark symbol.

I'd like to be able to enter &trade; or &#8482; .... I woudn't mind having to use either one at this point but nothing seems to work. What's really odd is it will save it one time. And if you look on the site, it is correct. But, if you save the record again in any way... it does not save correctly but places a " instead. 

Just a bit frustrating...

Tim

[injun__576871_]

Another attempt.

Symbol trademark - &trade; or &#8482; Bur in SQL ";" - it is end of the command. It may be the reason?

[tromanNMV]

Hi Alexei,

I think you may be right about that...

Now, to figure out how to insert a trademark symbol into mysql without using a semicolon.... or how to insert the string without ending the statement...

Tim : - )

[injun__576871_ · Accepted solution]

You can use regular expressions, like this:

/\b(™|™)\b/ig,"&trade" 

And when you receive data from the database, replace &trade on &trade;

[tromanNMV]

Thanks..  : - )

I ended up just doing that regular expression...

They just add the trademake code minus the semicolon.... and wherever it is displayed .. is a search/replace for the correct code with the semicolon.  Seems to work... Someone I would think it would be a bit more efficient ....    : )

Right now, it works

Thanks Alexei!