Jump to content
Larry Ullman's Book Forums

Recommended Posts

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?

Link to comment
Share on other sites

First of all, thanks for the great book!

Thanks!

 

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??

 

It sounds like you're confusing the topics of character sets and collations. I would generally use UTF8 as my character set for all tables and columns. I would also set the proper collation for all tables and columns. You can then adjust the collation (but less likely the character) set on a column by column basis.

 

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

Kudos and thanks for adding your own solution.

 

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?

Yeah, that's a minor point. For performance reasons, the recommendation is not to use fixed text columns with UTF8. But for performance reasons, something like an encrypted password, that will always be a set length, should be a CHAR. And, of course, MySQL will change the column's definition to what it thinks it should be. So the answer is...maybe, maybe not!

Link to comment
Share on other sites

 Share

×
×
  • Create New...