[gentoo-user] php database character set

[gentoo-user] php database character set

[Joseph]

My php database, address book is set to (according to phpmyadmin): 
Collation: utf8_general_ci

but some French customers have a problem entering their accented characters eg:
Bâtiment f22

I was even try to edit the database and enter it by cut and paste and it will not save it.

-- 
Joseph

Re: [gentoo-user] php database character set

[Michael Orlitzky]

On 05/25/2014 05:38 PM, Joseph wrote:
> My php database, address book is set to (according to phpmyadmin): 
> Collation: utf8_general_ci
> 

The collation determines e.g. how strings are sorted, but that doesn't
mean the database supports utf-8 (although MySQL should by default these
days).

Try adding,

  [client]
  default-character-set = utf8

  [mysqld]
  character-set-server = utf8

to your /etc/mysql/my.cnf.

Then, in mysql, run,

  show create database $your_database;

This will show you the default character set, like,

   /*!40100 DEFAULT CHARACTER SET utf8 */

If it says utf8 and you've got those lines in my.cnf, you're in good
shape, and your console test (at least) should work. Getting PHP to play
along is another adventure, but take it one step at a time.

Re: [gentoo-user] php database character set

[Joseph]

On 05/25/14 19:32, Michael Orlitzky wrote:
>On 05/25/2014 05:38 PM, Joseph wrote:
>> My php database, address book is set to (according to phpmyadmin):
>> Collation: utf8_general_ci
>>
>
>The collation determines e.g. how strings are sorted, but that doesn't
>mean the database supports utf-8 (although MySQL should by default these
>days).
>
>Try adding,
>
>  [client]
>  default-character-set = utf8
>
>  [mysqld]
>  character-set-server = utf8
>
>to your /etc/mysql/my.cnf.
>
>Then, in mysql, run,
>
>  show create database $your_database;
>
>This will show you the default character set, like,
>
>   /*!40100 DEFAULT CHARACTER SET utf8 */
>
>If it says utf8 and you've got those lines in my.cnf, you're in good
>shape, and your console test (at least) should work. Getting PHP to play
>along is another adventure, but take it one step at a time.

In  /etc/mysql/my.cnf
I have them as default

[client]
default-character-set = utf8

[mysqld]
character-set-server = utf8


-- 
Joseph

Re: [gentoo-user] php database character set

[Michael Orlitzky]

On 05/25/2014 08:06 PM, Joseph wrote:
> On 05/25/14 19:32, Michael Orlitzky wrote:
>>
>> Then, in mysql, run,
>>
>>  show create database $your_database;
>>
>> This will show you the default character set, like,
>>
>>   /*!40100 DEFAULT CHARACTER SET utf8 */
>>
>> If it says utf8 and you've got those lines in my.cnf, you're in good
>> shape, and your console test (at least) should work. Getting PHP to play
>> along is another adventure, but take it one step at a time.
> 
> In  /etc/mysql/my.cnf
> I have them as default
> 
> [client]
> default-character-set = utf8
> 
> [mysqld]
> character-set-server = utf8
> 

What about the output of "show create database $your_database;"?
Depending on how the database was created, it could still have another
character set.

Re: [gentoo-user] php database character set

[Joseph]

On 05/25/14 21:04, Michael Orlitzky wrote:
>On 05/25/2014 08:06 PM, Joseph wrote:
>> On 05/25/14 19:32, Michael Orlitzky wrote:
>>>
>>> Then, in mysql, run,
>>>
>>>  show create database $your_database;
>>>
>>> This will show you the default character set, like,
>>>
>>>   /*!40100 DEFAULT CHARACTER SET utf8 */
>>>
>>> If it says utf8 and you've got those lines in my.cnf, you're in good
>>> shape, and your console test (at least) should work. Getting PHP to play
>>> along is another adventure, but take it one step at a time.
>>
>> In  /etc/mysql/my.cnf
>> I have them as default
>>
>> [client]
>> default-character-set = utf8
>>
>> [mysqld]
>> character-set-server = utf8
>>
>
>What about the output of "show create database $your_database;"?
>Depending on how the database was created, it could still have another
>character set.

I was trying to run this command in phpmyadmin:
show create database $temp;

I'm getting an error message.
-- 
Joseph

Re: [gentoo-user] php database character set

[Michael Orlitzky]

On 05/25/2014 10:43 PM, Joseph wrote:
>>
>> What about the output of "show create database $your_database;"?
>> Depending on how the database was created, it could still have another
>> character set.
> 
> I was trying to run this command in phpmyadmin:
> show create database $temp;
> 
> I'm getting an error message.
> 

Oh, lose the dollar sign. It's common to prepend a dollar sign to a name
to signify that it's a variable. It borrows from shell, perl, PHP, etc.
which all do something similar for variables. It's ugly and redundant
there, but email is actually one place where I think it makes sense.

Re: [gentoo-user] php database character set

[Joseph]

On 05/25/14 23:00, Michael Orlitzky wrote:
>On 05/25/2014 10:43 PM, Joseph wrote:
>>>
>>> What about the output of "show create database $your_database;"?
>>> Depending on how the database was created, it could still have another
>>> character set.
>>
>> I was trying to run this command in phpmyadmin:
>> show create database $temp;
>>
>> I'm getting an error message.
>>
>
>Oh, lose the dollar sign. It's common to prepend a dollar sign to a name
>to signify that it's a variable. It borrows from shell, perl, PHP, etc.
>which all do something similar for variables. It's ugly and redundant
>there, but email is actually one place where I think it makes sense.

Yes, it worked, and I get:
CREATE DATABASE `catalog_sys` /*!40100 DEFAULT CHARACTER SET utf8 */

so it looks OK I think.

-- 
Joseph

Re: [gentoo-user] php database character set

[Michael Orlitzky]

On 05/25/2014 11:36 PM, Joseph wrote:
> 
> Yes, it worked, and I get:
> CREATE DATABASE `catalog_sys` /*!40100 DEFAULT CHARACTER SET utf8 */
> 
> so it looks OK I think.
> 

Yeah, looks good. Here's what I'm using as a test case. Your columns
aren't regular 'char' types, are they?

  mysql> CREATE DATABASE utf8_test;
  Query OK, 1 row affected (0.02 sec)

  mysql> CREATE TABLE dummy ( name varchar(255) NOT NULL );
  Query OK, 0 rows affected (0.05 sec)

  mysql> INSERT INTO dummy ( name ) VALUES ( 'Bâtiment' );
  Query OK, 1 row affected (0.00 sec)

  mysql> SELECT * FROM dummy;
  +-----------+
  | name      |
  +-----------+
  | Bâtiment  |
  +-----------+
  1 row in set (0.00 sec)

Are you running your tests from the command line, or through phpMyAdmin?
If it's the latter, it could be PHP's fault and not MySQL's.

Related: I don't think this has anything to do with your current issue,
but just as a PSA, "utf8" in mysql doesn't support all of unicode:

  http://mathiasbynens.be/notes/mysql-utf8mb4