PHP- Mysql database encoding and question-marks in the text (and how to get rid of them)

For my developing project i use Mysql and Dreamweaver set up with Wamp. I would say that i very familiar with databases and how to handle data etc..

Prerequisites to this project was that i could not modify either my php.ini or mysql.ini, since i am on a shared hosting plan.

One issue I had and could not fix though was that i got question-marks  for special characters like quotes or Cyrillic or French characters using ascent. I did work on this many hours but with no luck. Today i finally have managed to solve it. Here is how:

Since all my databases already have data and are online, i created copies of them using the correct charset. So:

1. I Exported all the tables in my databases using mysqldump and recreated them using utf8  character set and utf8_unicode_ci table/column collation. This is essential to make sure all data are saved in the correct format

2. I made sure that all my pages had the following two meta tags in the head tag:

<meta charset="UTF-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

3. Finally, and that is what was missing all this time, is to make sure that all communication with the database is done through UTF. Apparently this does not happen automatically when you set the php encoding and the database collation/encoding. To do so, every time you query your database,  you need to specify the communication  encoding format.

mysql_select_db($database_localhost, $localhost);

$query_collections = sprintf("Select  * from table WHERE packbrand=%s and serial='1' AND VISIBLE='1' order by collection asc", GetSQLValueString($colname_collections, "text"));

mysql_query("set names 'utf8'");

This  last part basically fixed it.

What did not work for me:

  • Trying to figure our whether your text editor (like notepad++) is wrongly converting your query to a different encoding.
  •  Pass the set names command in mysql workbench. While typing this i am just now realizing that this would not make any difference on way or an other, because it is the retrieval of the data in PHP that caused issues and not the displaying or the typing in..

One down many to go. Move to next project.

Leave a Reply