0

Anytime there's an accent mark (é or ') or symbol (&) in my database it displays as HTML code. The table CHARACTER SET is UTF-8 Unicode and the COLLATION is utf8mb4_general_ci, which should display those characters correctly. Any ideas why this is happening?

(MySQL version 5.7)

Current data

`company`| `firstname`| `lastname`
----------------------------------
A&T  | Patrick    | O'Brien

Expected

`company`| `firstname`| `lastname`
----------------------------------
A&T      | Patrick    | O'Brien

Thank you in advance!

6
  • this is html encode use a function to convert it see stackoverflow.com/questions/3678980/…
    – nbk
    Apr 4, 2020 at 18:05
  • Thanks, it works. However, I'm currently using another function to capitalize these cell values. So my query would now look like proper(HTML_UnEncode('company')),proper(HTML_UnEncode('firstname')),proper(HTML_UnEncode('lastname')), which nearly doubles the query time. Any tips?
    – msklut
    Apr 4, 2020 at 18:25
  • Sorry i meant you update all rows '**once ** and change the code that inserts the html encoded text . if that is not prossible, do a before insert and update, which converts the the text before inserting
    – nbk
    Apr 4, 2020 at 18:35
  • so with a BEFORE INSERT trigger?
    – msklut
    Apr 4, 2020 at 18:46
  • when you have code that insert and updates your database produces this html encoded text of course both
    – nbk
    Apr 4, 2020 at 18:48

1 Answer 1

1

Fix the data source to store actual characters into MySQL, not "HTML entities".

Where necessary, use output routines such as PHP's html_entities() and/or urlencode()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.