Thanks for your reply Mark.
In summary: following everything below, since I can directly query the database and get the correct results from a mysql prompt, from php at the command line and from a php page displayed in the browser, I'm really puzzled why I can't get the correct results from the openaudit web interface. I know Mark can't give me any more pointers, but I can't afford a support contract as we are an NGO serving the people of Afghanistan and rely entirely on donations to operate and since the foreign troops have reduced here the world is forgetting about Afghanistan and it is becoming harder and harder to get donations to support development work here, so if anyone else can give me some pointers I'd really appreciate it. Thanks!
The php extensions are all present (except LDAP and SNMP which we aren't using).
I've done a lot more digging and I've tried the various things in the article you linked to related to fonts and browser issues and in another article linked from that one. None of them made any difference. I even tried changing my own query (that works below) to use verdana (which is what openaudit is using) to force it to have the same problem as openaudit, but it doesn't. So it appears not to be a fonts and browser issue.
I've also created my own php query direct to the database and discovered that if you run the query direct from the command line using php it displays the UTF8 characters, but if you run the query using a browser it doesn't display it correctly, until you include these two commands with the select statement SET NAMES utf8; SET CHARACTER SET utf8; and then I get the UTF8 characters properly displayed in the browser (the same browser I'm using for testing with the openaudit web pages).
This led me to the /etc/my.cnf file but I haven't been able to find a set of instructions to add to this file yet that will actually make openaudit give the correct outputs.
So far I've added the following to my my.cnf file:
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
I have checked the mysql configuration and was surprised to see that in response to both of the queries below the database is still listed as latin although everything else is in utf8. I'm not sure of the implications of that or why it would be, particularly as openaudit's install script was what set up mysql on this server (at least that is my understanding of how mysql was setup as we weren't previously using it on this server before installing openaudit).
mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
So I did an ALTER DATABASE openaudit CHARACTER SET utf8 COLLATE utf8_general_ci;
and now it looks like this:
mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
But it doesn't fix the problem. Reading more I learnt that mysql utf8 is incomplete and we should use utf8mb4 instead. So I changed everything to that and then altered the database and tables concerned to be utf8mb4 as well. (
http://stackoverflow.com/questions/3513 ... -in-my-cnf and
https://mathiasbynens.be/notes/mysql-utf8mb4) But that didn't help. I also checked that it wasn't a problem caused by utf8 truncating the data on entry (unlikely since it displays properly from php at the command prompt) by re-entering the data into the database. But that didn't help.
I also tried adding skip-character-set-client-handshake to my.cnf and that didn't help.
So since I can directly query the database and get the correct results from a mysql prompt, from php at the command line and from a php page displayed in the browser, I'm really puzzled why I can't get the correct results from the openaudit web interface.