php mysqli query returns null if field value contains "ñ"

Question

I have a database table named users and one of the user there has a lastname that contains "ñ" and when I query from the users table it returned null for the lastname that contains "ñ" but for others it is just fine.

@footy - databases have absolutely nothing to do with fonts. Unless you're storing fonts in a database. — Sam Dufel, Jul 22 '11 at 06:21
Check if your table supports the font type. OR Check if your php script is saved in the proper font type. **EDIT** its tabel and not database.. sry :P. While creating tabel you have to give `CHARACTER SET=utf8;`. — footy, Jul 22 '11 at 06:23
well in that case, the tables engine is InnoDB and the collation is latin1_swedish_ci. I think it does support "ñ". — ThomasP, Jul 22 '11 at 06:26
@footy> I tried changing latin1 to utf8 but it still returns null. — ThomasP, Jul 22 '11 at 06:29
Hmm... Please provide the code you are using so that its easy for people to track your bug. — footy, Jul 22 '11 at 06:33
aha! it turn's out that it's not the mysqli but the json_encode() turns it to null. grrr! I should have known. Any solutions to this? — ThomasP, Jul 22 '11 at 06:34
I can't believe there are two elaborate answers so far when the question only has a vague description and no code. — Álvaro González, Jul 22 '11 at 07:32
Does this answer your question? [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Dharman, Dec 16 '20 at 14:25

score 2 · Answer 1 · answered Jul 22 '11 at 07:27

2

As per the documentation of json_encode:

This function only works with UTF-8 encoded data.

If your data is in latin1_swedish_ci, it will not accept the ñ. My advice is to either change your database and use utf-8 everywhere (which works well for me), or trying to use the multibyte functions to change the input data to utf-8. Something like this should work:

<?php
$input = mb_convert_encoding( $yourstring, 'UTF-8', mb_detect_encoding( $yourstring ) );

Again; I think using UTF-8 for everything is the smarter move and will save you from troubles later on.

answered Jul 22 '11 at 07:27

Berry Langerak

18,561
4
45
58

Thanks! I'll try the mb_convert_encoding() because I'm not the one who maintains the DB. – ThomasP Jul 22 '11 at 07:46
it somehow works but it only omitted the "ñ", instead I tried using htmlentities() and it worked. – ThomasP Jul 22 '11 at 07:53
@ThomasP Then tell the one who maintains the database and yourself to read about [the absolute minimum every programmer should know about unicode](http://www.joelonsoftware.com/articles/Unicode.html). It's a good article, and it explains why it's so important to get it right. – Berry Langerak Jul 22 '11 at 07:54
> Thanks for the article. I would really send this to him. haha! – ThomasP Jul 22 '11 at 07:57

score 1 · Answer 2 · answered Jul 22 '11 at 07:19

I cannot reproduce the error with

<?php
ini_set('default_mimetype', 'text/html');
ini_set('default_charset', 'utf-8');

echo 'phpversion: ', phpversion(), "<br />\n";

$mysqli = connect();
echo 'mysqli server version: ', mysqli_get_server_version($mysqli), "<br />\n";
echo 'mysqli client version: ', mysqli_get_client_version($mysqli), "<br />\n";
echo 'mysqli proto info: ', mysqli_get_proto_info($mysqli), "<br />\n";
echo '<pre>mysqli charset: '; var_dump(mysqli_get_charset($mysqli)); echo "</pre>\n";


setup($mysqli);
$result = mysqli_query($mysqli, 'SELECT x FROM foo') or die(__LINE__.mysqli_error($mysqli));
while ( $row=mysqli_fetch_assoc($result) ) {
    echo '<pre>', $row['x'], "<pre>\n";
}

function connect() {
    $mysqli = mysqli_connect('localhost', 'localonly', 'localonly', 'test') or die(__LINE__.mysqli_connect_error());
    mysqli_set_charset($mysqli, 'utf8') or die(__LINE__.mysqli_error($mysqli));
    return $mysqli;
}

function setup($mysqli) {
    mysqli_query($mysqli, 'CREATE TEMPORARY TABLE foo (id int auto_increment, x varchar(32), primary key(id))') or die(__LINE__.mysqli_error($mysqli));
    $v = chr(0xC3). chr(0x91);
    mysqli_query($mysqli, "INSERT INTO foo (x) VALUES ('abc'),('$v')") or die(__LINE__.mysqli_error($mysqli));
}

which prints on my computer

phpversion: 5.3.5
mysqli server version: 50508
mysqli client version: 50007
mysqli proto info: 10

mysqli charset: 

object(stdClass)[2]
  public 'charset' => string 'utf8' (length=4)
  public 'collation' => string 'utf8_general_ci' (length=15)
  public 'dir' => string '' (length=0)
  public 'min_length' => int 1
  public 'max_length' => int 3
  public 'number' => int 33
  public 'state' => int 1
  public 'comment' => string 'UTF-8 Unicode' (length=13)

abc

Ñ

thanks for the effort but I dicovered that the problem was not in the mysqli but in the json_encode(). hehe! sorry I didn't provide a code sample. my bad. — ThomasP, Jul 22 '11 at 07:48

php mysqli query returns null if field value contains "ñ"

2 Answers2