Php – MySQL Database has non escaped single quotes in entires … How to show them

character-encodingmysqlphp

The database has a ton of entries that were not escaped because they were inputted manually when they were inserted so they look like: Don't inside of the entry, but when I try to display them they have a weird characters when I output in PHP. Before I would put anything into the database I would usually use mysqli_real_escape_string and then do the same when I go to retrieve the data, but since the data is already stored without using real_escape how do I display it properly?

The character being displayed instead of the single quotes looks like this: �

If it helps the data is stored as 'text'.

Thanks!

For future users of the same problem here's the steps:

  • Check your website headers to see what the encoding is
  • Check your mysql table columns and make sure they match.
  • If they don't change them to match. utf8_general in mysql and utf8 in my HTML worked for me
  • You will have to go back through the old mysql tables and update them so the new encoding is set properly.
  • New entries should work fine
  • When you output your results in PHP (or I guess whatever language you use), depending on if you are using any validation, you may have to use mysqli_real_escape_string or a similar function, such as stripslashes()

Best Solution

You'll need to read up on text encoding.

The usual solution is to make sure everything (the content-type encoding on your pages, and your mysql) are set to UTF-8

Chances are your data is Latin1 and you're displaying UTF-8 or vise versa

Related Question