Many pots of coffee later...Here is an update to my issue, maybe this will help so folk...:
I am not sure if this just on my host only or what but:
On MySQL 5 - PHP 5 the default connection is utf8. Since ZC does not specify what the connection should be in (latin1) the data transmitted from the db is utf8.
If I add this code:```php
mysql_set_charset('latin1');
includes/classes/db/mysql/query_factory.php
That fixes that issue.
-----------
My other issue was moving from MySQL 4 to 5:
Since MySQL 4 has no collations for utf8/latin1.
I exported my db on the command line dump and added the /*40101's below to the header of the db:
-- MySQL dump 9.11
-- Host: mysql.domain.com Database: domainuser-db-test
-- Server version 4.0.27-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES latin1 */;
-- Table structure for table address_book
then I created a new db and set the mysql collation in PHPMYADMIN to latin1_swedish_ci for that db and imported the old db into the new MySQL 5 db
on the import of the db, I did add --default-character-set=latin1 to one db test but the above but PHPMYADMIN seem to do that so I left it out on the last go, also removed the /*!40101's except for /*!40101 SET NAMES latin1 */; that seem to work too (should I leave them out?)
--------
So with the ZC query_factory and the import in/as latin1 my ZC looks ok in browsers auto-detect : (Western) ISO latin1/(Western ISO-8859-1)
When viewing ZC db data in PHPMYADMIN 3.1.1 the browser is set to ```html
<meta http-equiv="Content-Type"
content="text/html; charset=utf-8" />
```so I set the connection to utf8_general_ci for PHPMYADMIN and all data is displayed correctly.
When dumping the db from PHPMYADMIN if the PHPMYADMIN collation is utf8 the dump will be in Utf-8, no BOM if you change it to swedish they will be mac os roman (in my text editor BBE edit for MAC which is the default for files that have no clear encoding), switching the view to Western (ISO latin 1) all the characters in the db look ok.
This is my first dive into anything related to a db or mysql, so I do not know if this the best way to go or not.:beta:
Please post there is a better way!
Because I do not know this will create future db issues if you take this route (if you had to restore your site from a mysql dump or ran a future ZC Uprade.sql script for 1.3.9 etc)
anyway here is some of what my last dump looked like :
-- Host: MySQL.domain.net
-- Generation Time: Jan 11, 2009 at 11:43 AM
-- Server version: 5.0.70
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 */;
--
-- Database: db-setlatin1inheaders-setlatin1collationCHARSET
--
-- Table structure for table address_book
DROP TABLE IF EXISTS address_book;
CREATE TABLE IF NOT EXISTS address_book (
address_book_id int(11) NOT NULL auto_increment,
customers_id int(11) NOT NULL default '0',
entry_gender char(1) NOT NULL default '',
entry_company varchar(32) default NULL,
entry_firstname varchar(32) NOT NULL default '',
entry_lastname varchar(32) NOT NULL default '',
entry_street_address varchar(64) NOT NULL default '',
entry_suburb varchar(32) default NULL,
entry_postcode varchar(10) NOT NULL default '',
entry_city varchar(32) NOT NULL default '',
entry_state varchar(32) default NULL,
entry_country_id int(11) NOT NULL default '0',
entry_zone_id int(11) NOT NULL default '0',
PRIMARY KEY (address_book_id),
KEY idx_address_book_customers_id_zen (customers_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=135 ;
