[Charset Utf8 (Mysql & Php)]

Hello

 

First of all, thanks for the great book!

 

I'm trying to apply utf8 in my database following the example on page 431-433. Mainly I'll use german characters as ä,ö,ü,ß,€ to insert and select into/from the databse.

 

What I did so far:

 

- I connect and then do "CHARSET utf8".

- I created a DB with a collation of latin1_german1_ci.

- I then created a table with a column encoded (CHARSET) to utf8.

 

When I query that table via php in a browser I need to do a

mysqli_query($dbc, "SET NAMES 'utf8'");

before the actual query otherwise the special characters are not printed correctly.

 

So my question simply is, whether that is a correct way to do it?

 

// Database access information as constants:

if (!defined ('DB_USER')) DEFINE ('DB_USER', 'XXX');
if (!defined ('DB_PASSWORD')) DEFINE ('DB_PASSWORD', 'XXX');
if (!defined ('DB_HOST')) DEFINE ('DB_HOST', 'XXX');
if (!defined ('DB_NAME')) DEFINE ('DB_NAME', 'XXX');

// Make the connection:

$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );

// Make utf8 related stuff:

mysqli_query($dbc, "CHARSET utf8");
mysqli_query($dbc, "SET NAMES 'utf8'");

// Make the query:

$q = "SELECT * FROM test_utf8 ORDER BY word";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r)) {
echo $row['word'] . "<br />";
}

 

It appears to me that I actually need the query "CHARSET utf8" in order to INSERT data,

and the "SET NAMES 'utf8'" in order to SELECT data.

 

---------------------------------------------

 

Second and more general question to this topic:

 

Do you consider it a better way to switch a DB entirely to utf8 (if I need/want it in some columns), or to chose a collation like latin1_german1_ci in general and utf8 on specific columns?

 

I myself would say: use utf8 wherever possible because it is the future, isn't it??

 

--------------------------------------------

 

EDIT:

 

Simply writing this post helped me understand better ... (as so very often) So I'm giving some answers by myself:

 

CHARSET vs. SET NAMES:

It's actually said by Larry on that same page 431: CHARSET sets the encoding for the communication within the client and SET NAME between mysql & php.

So I'll need the following line of code directly after connecting to the db:

mysqli_query($dbc, "SET NAMES 'utf8'");
OR
mysqli_set_charset($dbc, 'utf8');

 

remains my second more general question ...

 

and PS: Why do you (Larry) use a column "pass CHAR(40) NOT NULL" in the users table which is utf8 encoded (page 446 in chapter 15: Message Board)? Didn't you mention that using CHAR() wasn't a good idea in utf8 encoded columns?