tags:

views:

60

answers:

4
Q: 

mysql result returns ?(question mark) instead of Ö etc

When i return a row from my mySQL database, I get a ? instead of some characters eg:ò, à etc. My mysql row and table are set to utf8_unicode_ci, so I think the database is storing it correctly but php isnt returning it correctly.

Think it has something to do with mysql_set_charset but cant get it to work properly. Any help would be greatly appreciated!!

  <?php 
if($row = mysql_fetch_assoc(queryDb("SELECT * FROM customer WHERE uuid='".$_COOKIE['uuid']."'")))
 {
  $first_name = $row['first_name'];
  $last_name = $row['last_name'];
  $gender = $row['gender'];


  $ileach_first_name = $row['ileach_first'];
  $ileach_last_name = $row['ileach_last'];

}

 //If Ileach Name is blank

  if($ileach_last_name == "" || $ileach_first_name == ""){


  // Get ileach last name
  $row = mysql_fetch_assoc(queryDb("SELECT * FROM ileach_last_names WHERE eng_name='$last_name'"));

  $ileach_last_name = $row['gae_name']; 


  if($ileach_last_name == "") {
     $row = mysql_fetch_assoc(queryDb("SELECT * FROM ileach_last_names order by rand() limit 1"));
    $ileach_last_name = $row['gae_name'];}  



  //Get ileach First Name
   //If Male
    if($gender == 'M') {

     $row = mysql_fetch_assoc(queryDb("SELECT * FROM ileach_first_names_m WHERE eng_name='$first_name'"));

     $ileach_first_name = $row['gae_name']; 

     //If no name is selected, get one randomly 
      if($ileach_first_name == "") {
       $row = mysql_fetch_assoc(queryDb("SELECT * FROM ileach_first_names_m order by rand() limit 1"));
     $ileach_first_name = $row['gae_name']; }
    }
   //If Female 


    else{

     $row = mysql_fetch_assoc(queryDb("SELECT * FROM ileach_first_names_f WHERE eng_name='$first_name'"));

     $ileach_first_name = $row['gae_name']; 

     //If no name is selected, get one randomly 
     if($ileach_first_name == "") {
       $row = mysql_fetch_assoc(queryDb("SELECT * FROM ileach_first_names_f order by rand() limit 1"));
     $ileach_first_name = $row['gae_name'];}
    }




  //Save ileach name into db

   mysql_query("UPDATE customer SET ileach_first = '$ileach_first_name'
   WHERE uuid='".$_COOKIE['uuid']."' ");

   mysql_query("UPDATE customer SET ileach_last = '$ileach_last_name'
   WHERE uuid='".$_COOKIE['uuid']."' ");
  }


  //Stitch name together.

  $full_ileach_name .=$ileach_first_name;
  $full_ileach_name .= " ";
  $full_ileach_name .= $ileach_last_name;





?>
A: 

try to put this after mysql_select_db:

mysql_query ( "set character_set_client='utf8'" );
mysql_query ( "set character_set_results='utf8'" );
mysql_query ( "set collation_connection='utf8_unicode_ci'" );
Gatman  2010-08-23 12:07:03
This let's e.g. mysql_real_escape() in the dark about the changed charset. see http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html which was apparently written before mysql_set_charset was introduced in php 5.2.3
VolkerK  2010-08-23 12:34:51
+4  A: 

"Think it has something to do with mysql_set_charset" - yes, very likely.

$mysql = mysql_connect(...); // TODO: error handling
mysql_select_db('...', $mysql);  // TODO: error handling
mysql_set_charset('utf8', $mysql);  // TODO: error handling

For mor detailed information we need to know more about the function queryDB() and related stuff (like e.g. where the database connection is established)

see also:

VolkerK  2010-08-23 12:08:30
The connection was stored in another file, slotted in mysql_set_charset and it worked like a dream! Cheers!
Alastair Taylor  2010-08-23 13:01:20
was running this locally on php 5.2.6, and it worked fine, but once id deployed it to my server running on 5.1.6 it didnt work. However, I stumbled uppon this line: mysql_query('SET NAMES utf8');which worked a treat!
Alastair Taylor  2010-08-24 12:47:11
A: 

Send as a first query this:

SET NAMES 'utf8'

Should work ;)

  • MySQL stores text correctly, and PHP is correctly formatting, but problem is laying in connection charset :)

  • Like @VolkerK suggested, it may be a better idea to use mysql_set_charset func

canni  2010-08-23 12:10:06
This let's e.g. mysql_real_escape() in the dark about the changed charset. http://docs.php.net/mysql_set_charset says: "Using mysql_query() to execute SET NAMES .. is not recommended."
VolkerK  2010-08-23 12:16:38
I'm using PDO and this is working great :)So i wrote that this "Should work" when using mysql/mysqli driver
canni  2010-08-23 12:17:39
But you're probably also using parametrized queries with PDO, don't you?
VolkerK  2010-08-23 12:20:05
Yep :) i'm not standing after that "like a stone", just it works for me, (for polish set of additional chars), but i may be wrong :)PS and it passes escaping in pena-tests, so i'm using this query to set conn charset :)
canni  2010-08-23 12:24:02
If you use parametrized queries/server-side prepared statements there's nothing wrong with SET NAMES (as far as I know). But if you rely on escaping with e.g. mysql_real_escape_string() like you have to with the old php-mysql module it opens a rare but unnecessary hole. Unnecessary because there is a function (mysql_set_charset) that specifically deals with the problem.
VolkerK  2010-08-23 12:31:35
A: 

Is the encoding of the file itself correct?

Have you tried using utf8_encode or utf8_decode? see: http://php.net/manual/en/function.utf8-encode.php

If utf8_encode works, I would assume that the results from the database are in the wrong charset.

If utf8_decode works, I would assume that the file has the wrong charset or maybe the browser is parsing it in the wrong charset. This can be fixed with a header:

header('Content-Type: text/html; charset=utf-8');

or in <head>:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

see: http://tlt.its.psu.edu/suggestions/international/web/tips/declare.html

Arnar Yngvason  2010-08-23 12:17:31

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL