CODEWITHSUNDEEP
phpmysql
PhpNewbie
PhpNewbie

Reputation: 89

Record isn't displayed in MySQL but I can get it by ID in PHP

So I have a weird situation with my MySQL database and PHP.

When I try to get all records in MySQL I get result like this:

mysql> select * from adresatai;
+----+-------------+------------------+--------------+-------------------------

    -----------+
    | ID | name      | surname          | mobile    | email                              |
    +----+-------------+------------------+--------------+------------------------------------+
    |  6 | ex1    | ex2           | 123456789 | [email protected]     |
    |  7 | ex3  | ex4   | 987654321 | [email protected]     ||
    |  9 | ex5      | ex6        | +123456 | [email protected] |
 +----+-------------+------------------+--------------+------------------------------------+

As you see record with 8 ID is missing, and at the of 7 record there is a double | (||).

But when I try to retrieve by id I get something like this:

mysql> select * from adresatai WHERE ID=8;
+----+--------+------------------+--------------+--------------------------------+
| ID | name | surname          | mobile    | email                          |
+----+--------+-----------ius   | 123456789 | [email protected] |+
+----+--------+------------------+--------------+--------------------------------+
1 row in set (0.00 sec)

When I don't use UTF8 characters in the website where I updated record from it's fine, but when I try to add as example Š it becomes like this. But I have few more entries that has Ž in it, but they're good.

Table is set to utf8.

Upvotes: 1

Views: 48

Answers (3)

PhpNewbie
PhpNewbie

Reputation: 89

In my case the problem was with charset that I set in PHP while connecting to database. So the solution was to add

mysqli->set_charset("utf8");

in function where I connect to database.

http://php.net/manual/en/mysqli.set-charset.php

Upvotes: 1

rlanvin
rlanvin

Reputation: 6267

You need to make sure you client is in UTF8 too, otherwise you can get this type of weird display glitch.

To change the charset during a connection, execute SET NAMES utf8;

Upvotes: 0

Panos Kalatzantonakis
Panos Kalatzantonakis

Reputation: 12673

Try to start your client with this option --default-character-set=utf8.

You can check more about this here: 10.1.4 Connection Character Sets and Collations

You can also change this behavior in [mysqld] section in my.cnf and add two strings:

collation_server=utf8_unicode_ci
character_set_server=utf8

You can also add

skip-character-set-client-handshake

to enforce using of utf8 encoding in db.

Upvotes: 2

Related Questions