Sending a MySQL SET command via PHP from ZC code

[nkostaki]

Hi,

I'd like to send a SET NAMES command to MySQL prior to the mysql_connect and mysql_select_db commands in the ZC code at includes/classes/db/mysql/query_factory.php. Is that possible? I could find no mention in the PHP cocumentation I searched.

The MySQL SET statement (of which the syntax I copy below) is one of the approaches we're working on to get full iso8859-7 compatibility for our soon-to-be Greek

Thanks,

Nikolas



-----------------------------------
SET
#
NAMES {'charset_name' [COLLATE 'collation_name'} | DEFAULT}

SET NAMES sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name. The optional COLLATE clause may be used to specify a collation explicitly. See Section 10.4, “Connection Character Sets and Collations”.

The default mapping can be restored by using a value of DEFAULT. The default depends on the server configuration.

Note that the syntax for SET NAMES differs from that for setting most other options. This statement is available as of MySQL 4.1.0.
------------------------------------------------

I'd like to locate the point(s) in the ZC code where the php command to connect to the store db is given. I unserstood that the relevant php commands I should look for would be

mysql_connect
mysql_select_db

Usind Developer's Tool Kit in Admin I searched foe these two but came up with nothing.

[Merlinpa1969]

I believe that the connection is made in the application_top.php

[nkostaki]

The mysql_connect & mysql_select_db commands only appear in query_factory.php. The issue appears to be IF PHP can accomodate the passing of the SET statement to MySQL.

You're never too far away, are you Tony? :)
What will the wife say? :))

[Merlinpa1969]

Shes working a Double today she wont be home till about 11PM.

I am still trying to figure this out, I cant for the life of me get the character set for the server to be utf8.
If I get this set then you will be OK.

[nkostaki]

everything i've heard shows that utf8 is not the answer, at least in my case. iso8859-7 is what I'd want to have all around

[Merlinpa1969]

the information is stored in the database correctly,
however the character set for server is Latin,
I have verified that if I set that to utf8 that it will display your language correctly,

[nkostaki]

We've found the way to send the command to mysql. I'm copying the modified code in includes/classes/db/mysql/query_factory.php, in case this helps other working with greek (or other languages for that matter):

if ($zf_pconnect != 'false') {

$this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);
mysql_select_db("database", $this->link);
// change our character set
mysql_query ('SET NAMES GREEK',$this->link);


} else {

// pconnect disabled ... leaving it as "connect" here instead of "pconnect"

$this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);
mysql_select_db("database", $this->link);
mysql_query ('SET NAMES GREEK',$this->link);


the underlined lines force the client-side connection to mysql to translate to greek. store/admin now display greek ok, phpmyadmin was displaying ok to begin with, editing with php or Admin does not corrupt data.

As this might help I'll recap our approach to having Greek display and edit correctlty. We've used two methods so far that have been successful and are evaluating the merits/possible problem of each. Both enable the store to display greek and the db to be viewable/editable through either phpmyadmin or navicat:

1. create db, ALTER it to latin1/latin1_swedish_ci. import data from old backup through phpmyadmin specifying source file as latin. store/admin display greek if treated with iso override (had that in effect to enable the greek ZC translation to display greek correctly). phpmyadmin won't display correctly (as set up in our hosting environment) but navicat does. editing either via navicat or Admin does not corrupt data.

2. create db, ALTER it to greek/greek_general_ci. import data from old backup through phpmyadmin specifying source file as greek. modify the code in includes/classes/db/mysql/query_factory.php (see above)

[nkostaki]

P.S. Imagine going through all the troble only to find the solution was in your lap all the time. I just (re-)discovered that the ZC code approach mentioned has been suggested to me in late March by dwno here at the forum. Thanks again dwno!