Different result in phpMyAdmin and in CakePHP 3

Question

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.

@AD7six The values in the DB could indeed be different to what the OP expects, however seeing all the values ending up as `2147483647` when being read sounds more like a 32-Bit PHP that can't handle larger INTs. — ndm, Oct 07 '15 at 12:28

score 3 · Accepted Answer · edited May 23 '17 at 10:26

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.

Different result in phpMyAdmin and in CakePHP 3

1 Answers1