Strange PDO behavior

Question

I'm using the following code:

 try {
        $this->conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }    
 try {
        $stmt = $this->conn->prepare("SELECT id, name FROM images");
        if ($stmt->execute()) {
            if ($stmt->rowCount() > 0)
                return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        else
            return NULL;
    }
    catch(PDOException $e)
    {
        return $e->getMessage();
    }

The code perfectly works, but when I edit my query to:

$stmt = $this->conn->prepare("SELECT id, name, description FROM images");

I obtain an empty result.

Obviously the description field exists. Obviously I tested the new query with PHPMyAdmin.

I found that:

if ($stmt->rowCount() > 0)

is TRUE and I also tried to change the fetclAll to FETCH_BOTH.

Do you have any suggestions?

EDIT: OK, I FOUND THE PROBLEM:

In a record, in the column description there was a "è". If I delete the "è", all is perfectly working. Why this happening?

Have you looked at your error logs? What do the error logs say? — Michael, Jan 20 '16 at 23:39
The simplest way with PHP is to add the line `error_reporting(-1);` at the top of your page, directly after your opening php tag ` — Michael, Jan 20 '16 at 23:45
I just realized that the column `name` is using a reserved word in mysql. So when you are trying to query using that column you need to write the column like this: `table.name`. Try that to see if that helps. So your entire query will be: `"SELECT id, images.name, description FROM images"`. — Michael, Jan 20 '16 at 23:49
The problem is not the **name** column, but the description one. — helloimyourmind, Jan 20 '16 at 23:50
@Michael `name` is not reserved; https://dev.mysql.com/doc/refman/5.5/en/keywords.html. If it were reserved it would need to be in backticks. — chris85, Jan 20 '16 at 23:55
So anything addressing the description column fails even if not referenced directly? — chris85, Jan 20 '16 at 23:57
Yes @chris85. I also tried to rename the column or to destro ad re-create the table but still doesn't works. In PHP My Admin, everything is ok. — helloimyourmind, Jan 21 '16 at 00:00
@chris85 Funny enough, I referenced that link before typing what I did. Maybe it's case sensitive, then? Because the word `NAME` is in the table of Keyword and Reserved Words — Michael, Jan 21 '16 at 00:01
It's not about name or case sensitive, the name are all in lower case, and if I do SELECT name from images, it works. — helloimyourmind, Jan 21 '16 at 00:04
@Michael There's no `(R)` after `NAME` on that page. That means it's a keyword, but not a **reserved** word. — Barmar, Jan 21 '16 at 00:17
@helloimyourmind Is the code in the question a literal copy of your application? Make sure you only have one call to `fetchAll`; the first one reads all the results, the second one will return an empty remainder. — Barmar, Jan 21 '16 at 00:20
Guys, I repeat: if I only change the query, it's perfectly working. I think it's a fetchAll problem. — helloimyourmind, Jan 21 '16 at 00:31
I thought the `fetchAll` worked when the `description` wasnt there? — chris85, Jan 21 '16 at 01:01
Okay, so it wouldn't be the `fetchAll`. It sounds like something unique to your table/server.. — chris85, Jan 21 '16 at 01:18
The "è" is a value of a record, in the **description** column. — helloimyourmind, Jan 21 '16 at 01:21
See this thread, http://stackoverflow.com/questions/279170/utf-8-all-the-way-through and make sure you set the `charset` in the connection. — chris85, Jan 21 '16 at 01:23

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

The Problem was due to an accented letter in a record of the database.

The solution is the use of array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4") to set the character set of the connection to 'UTF-8 MultiByte 4' as follows:

  try {
        $this->conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"));
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      }
  catch(PDOException $e)
      {
        echo $e->getMessage();
      }

The reason this specific version of UTF-8 is used is explained here.

edited May 23 '17 at 12:15

Community

1
1

answered Jan 21 '16 at 01:24

helloimyourmind

874
2
13
30

You want to use `utf8mb4` ***not*** utf8 as MySQL utf8 is not true utf8. http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – Martin Jan 21 '16 at 01:32
@Martin thank you for the suggestion, I updated my answer. – helloimyourmind Jan 21 '16 at 02:30
You may also use `charset` in your dsn (first argument of PDO) to specify connection charset. – vfsoraki Jan 22 '16 at 19:13

Strange PDO behavior

1 Answers1