1

I created an application whith UTF-8 database charset. When creating connection using php I am executing mysqli_query($link, 'SET CHARACTER SET utf8').

It was working fine on localhost with both SELECT, INSERT etc. I deployed the application on web with some sample data and quickly browsed the page I saw UTF-8 characters nicely.

The next day client called me data is showing like ?????? when I add some record through provided interface I was surprised he was right but On localhost it was working fine.

After some searching I found multiple answers like execute these queries

  • set character_set_client='utf8'
  • set character_set_results='utf8'
  • set collation_connection='utf8_general_ci'
  • SET NAMES 'utf8'
  • and setting then encoding in response etc

None of them worked but this mysqli_set_charset($link, 'utf8') one.

My Question is

  1. Why It was working on localhost, and only INSERT or UPDATE Query was creating problem after deployment but SELECT was working fine
  2. What is the difference between mysqli_set_charset($link, 'utf8') and mysqli_query($link, 'SET CHARACTER SET utf8')
Shahrzad
  • 1,062
  • 8
  • 26
  • 1
    Please read [this excellent answer](http://stackoverflow.com/a/279279/3536236) – Martin Aug 13 '16 at 15:57
  • Another [_excellent answer_](http://stackoverflow.com/questions/26596294/set-names-vs-mysqli-set-charset-besides-affecting-mysqli-escape-string-are/37947771#37947771) – Rick James Aug 14 '16 at 00:23
  • [_This_](http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored) says that "question marks" arise from failure to have utf8 text and `CHARACTER SET utf8`. – Rick James Aug 14 '16 at 00:25
  • What characters turned into question marks? There is still the question of utf8 vs utf8mb4. – Rick James Aug 14 '16 at 00:25

1 Answers1

0

Your localhost database might have been set up with utf8 collation but the remote web server's one was set up incorrectly.

An alternative to localhost based development is using a vagrant virtual machine. You can set this up as closely to production as possible.

mysqli_set_charset does more than set the client character set. See https://secure.php.net/manual/en/mysqlinfo.concepts.charset.php which was linked to from the manual page for mysqli_set_charset

jedifans
  • 2,287
  • 1
  • 13
  • 9