[FIXED] Different result in phpMyAdmin and in CakePHP 3

Issue

Actually i'm creating apps with CakePHP 3. The problems is that the result in phpMyAdmin and in CakePHP is not the same. When i retrieve table in phpMyAdmin i got something like this.

+-----+---------------+--------------+
| id  |      cin      |     nom      |
+-----+---------------+--------------+
| 17  | 123456789123  | TEST         |
| 16  | 102234566778  | MPANAOTEST   |
| 15  | 101256003054  | MIHARINTSOA  |
| 14  | 101253458255  | RAMAMONJISOA |
|  1  | 102256003063  | RAJAONAH     |
+-----+---------------+--------------+

And in my apps i got this

+-----+------------+--------------+
| id  |    cin     |     nom      |
+-----+------------+--------------+
| 17  | 2147483647 | TEST         |
| 15  | 2147483647 | MIHARINTSOA  |
| 14  | 2147483647 | RAMAMONJISOA |
|  1  | 2147483647 | RAJAONAH     |
+-----+------------+--------------+

My Controller looks like this

public function liste() {
    $this->set('employes', $this->Employes->find('all'));
    $this->set('_serialize', ['employes']);
}

If someone can explain or give a way to fix it. Thanks

Note: CIN row is unique.

Solution

You are most likely using a 32-Bit variant of PHP, the largest integer value it can handle is 2147483647. What you are seeing is the expected result, CakePHP applies casting to the retrieved data depending on the column type, so in this case integer casting.

See Source > \Cake\Database\Type\IntegerType::toPHP()

Either switch to a 64-Bit PHP, or if that's not possible (or doesn't help for some reason), change the column type. You wouldn't necessarily need to change the type in the DB, overriding the schema in your app would probably already help, something like

// ...

class EmployesTable extends Table
{
    protected function _initializeSchema(Schema $table)
    {
        $table = parent::_initializeSchema($table);
        $table->columnType('cin', 'string');
        return $table;
    }

    // ...
}

That way CakePHP would treat the column as a string type, and would be able to handle numbers larger than PHP_INT_MAX by treating them as strings. However this will also affect the marshalling process, the query builder, escaping, etc, you could for example end up with queries like

WHERE cin > '12345'

Even non numeric characters could slip in, so you might need to take additional measures to make sure that you are working with actual numbers.

I would suspect todays DBMS' query optimizers to be smart enough to handle (cast) this, however it's something one should be aware about.

Answered By - ndm