utf8 username/password

[sabyasachi]

Hi,
If I am having my mysql username/password in utf-8 encoded strings which includes non latin characters (includes characters from languages like Arabic, Hindi). Then how should I connect to mysql server through PHP?
In C API i can do this by calling mysql_option() and setting MYSQL_SET_CHARSET_NAME to "utf8".
But I could not find anything in PHP which will set the option before connecting to the server.
Is it possible to set even? If yes then how?

Thanks in advance.
Sabyasachi Ruj

[effigy]

See if [url=http://www.phpfreaks.com/forums/index.php/topic,124162.msg514242.html#msg514242]this[/url] helps.

[sabyasachi]

Hi effigy,
That way we can set the mysql charset/names to 'utf8', but only after making the connection to mysql server. But what if I have my MySQL username and password in Arabic or Hindi?
Then Will PHP be able to connect then, with that user?
Now, as I said in the starting post of this topic, this can be done easily in C API. By just calling the function mysql_option() .
Is there anything similar in PHP?

Sabyasachi Ruj.

[anatak]

read this http://adviesenzo.nl/examples/php_mysql_charset_fix/

[sabyasachi]

Not yet solved...
ok.. let me give the details.
1. My MySQL server username is in hindi, and is : सब
2. My Password: 'root'
3. I can not configure my.cnf/my.ini to change default characterset to use utf-8.

Now [b]HOW SHOULD I CONNECT[/b] to the server through PHP?

[anatak]

I am sorry but I think (not sure) you have to have your columns setup as a utf-8 field in mysql.
If you can't do that I have no real idea how to solve your problem.
you will also note that utf8 has some strange behaviour from time to time when you submit utf8 characters through a form.

anatak

[effigy]

You should be able to use mysqli_init() and mysqli_options(), where the character set is set in mysqli_options()' MYSQLI_INIT_COMMAND.

[sabyasachi]

Hi..
Thanks..So MySQLI extension solves that problem. I have to check this and let you know.
Is there anything similar for "mysql" extension?

[effigy]

I don't think so. According to the [url=http://us2.php.net/manual/en/ref.mysqli.php]PHP docs[/url], the mysqli extension is for MySQL 4.1+, and according to the [url=http://dev.mysql.com/doc/refman/4.1/en/charset-unicode.html]MySQL docs[/url], UTF-8 was added in version 4.1.

[sabyasachi]

Hi effigy,

I tried to set CHARACTER SET/ NAMES to utf8 by calling:-

[code]mysqli_options($link, MYSQLI_INIT_COMMAND, "SET NAMES utf8");[/code]

But i found that these init commands will be executed [b]ONLY AFTER[/b] connecting to MySQL server.
So I was unable to connect with a Hindi username, as this is not setting the CHARACTER SET/ NAMES before making connection.

I have given the mysql server query log, this shows that it has been executed after connecting.

I am giving the code I have used. This code is using a normal user name(ascii chractes only) to be able to connect, so the queries can be logged.

[b]The MySQL query log:-[/b]

[i]
E:\MySQLEnt\bin\mysqld-nt, Version: 5.0.28-enterprise-nt-log (MySQL Enterprise Server (Commercial)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                Id Command    Argument
070131 11:26:53       1 Connect    root@localhost on
      1 Query      SET NAMES utf8
      1 Query      show databases
      1 Quit     
[/i]




[b]The PHP code:-[/b]

[code]
<?php

/* create a connection object which is not connected */
$link = mysqli_init();

/* set connection options */
mysqli_options($link, MYSQLI_INIT_COMMAND, "SET NAMES utf8");

/* connect to server */
$user="root";
$password="root";
mysqli_real_connect($link, 'localhost', "$user", "$password", '');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$result=mysqli_query($link, "show databases") or die(mysqli_error($link));

while ($row=mysqli_fetch_array($result)) {
    echo ($row[0]."<br>");
}

mysqli_close($link);
?>
[/code]

[sabyasachi]

This is documented here:-
[url=http://in2.php.net/manual/en/function.mysqli-options.php]http://in2.php.net/manual/en/function.mysqli-options.php[/url]

that,
MYSQLI_INIT_COMMAND    command to execute after when connecting to MySQL server

[effigy]

Just to be sure, try both:

[code]
mysqli_options($link, MYSQLI_INIT_COMMAND, "SET NAMES utf8");
mysqli_options($link, MYSQLI_INIT_COMMAND, "SET CHARACTER SET utf8");
[/code]

I'm not sure which direction to take after this. I tried creating a user name in UTF-8, and it seems I cannot do so using CHAR with INSERT until I've patched or upgraded ([url=http://bugs.mysql.com/bug.php?id=18908]source[/url]).

[sabyasachi]

I tried. But it is not working. And it should not.
All the sql commands you give in 'mysqli_options' with 'MYSQLI_INIT_COMMAND' will be executed immediately, but [b]"ONLY AND ONLY AFTER"[/b] the connection has been made.
So this will not tell the MySQL server that my username is in utf8.
No problem with INSERTing characters in my server. Since the C API is connecting with an utf8 user name, and displaying the characters properly.