We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Saving ints in a mysql database are coming out as strings

I have a really simple model. This model contains a few bools, ints and a few strings. When I save the model to a MySQL table and then retrieve the data, the ints and bools are returned as strings. Why? Do I need to manually cast them back to ints in my model?

Some deft searching revealed this SO question and answer, but doesn't really help with bools:

https://stackoverflow.com/questions/21316518/sql-data-type-to-php-data-type-in-model

"if you are using Phalcon\Db\Adapter\Pdo\Mysql as your adapter, Integers are returned as strings in PDO by default

to return the right types you need to setup the appropriate options for your adapter, you need to set ATTR_EMULATE_PREPARES and ATTR_STRINGIFY_FETCHES to false

you need to change your Adapter code in your config or service file as your setup like this"

return new Phalcon\Db\Adapter\Pdo\Mysql([
  'host' => 'DATABASE_HOST',
  'username' => 'USERNAME',
  'password' => 'PASSWORD',
  'dbname' => 'DATABASE_NAME',
  'options' => array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_STRINGIFY_FETCHES => false,
  )
]);
edited Jul '17

Since PHP is typeless, there's no real drawback having the variables as strings. If you really want to typecast them you can do it like so:

class MyModel extends \Phalcon\Mvc\Model {
    public function afterFetch() {
        foreach($this->getModelsMetaData()->getDataTypes($this) as $field=>$type) {
            if(is_null($this->$field)) {
                continue;
            }
            switch($type) {
                case Column::TYPE_BOOLEAN:
                    $this->$field = boolval($this->$field);
                    break;
                case Column::TYPE_BIGINTEGER:
                case Column::TYPE_INTEGER:
                    $this->$field = intval($this->$field);
                    break;
                case Column::TYPE_DECIMAL:
                case Column::TYPE_FLOAT:
                    $this->$field = floatval($this->$field);
                    break;
                case Column::TYPE_DOUBLE:
                    $this->$field = doubleval($this->$field);
                    break;
            }
        }
    }
}

You could create this as an abstract, and then inherit every model file from this

Just install mysqlnd driver - not sure what is this Lajos Bencz stuff propose, i never did something like this and i have int values returned from database.

Thanks. php7.0-mysqlnd is already installed. And even with the code from Lajos Bencz, it's still showing booleans as ints for some weird reason.

edited Jul '17

Oh booleans will be always showed as ints in mysql, because there is no really boolean type in database, it's stored as int anyway. From mysql docs:

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

Ah ok, so I'll guess the best way will be to have separate getters/setters for these properties then! Thanks

@Jurigag there's no real benefit to that code, it just typecasts the vars :) var_dump will show them as bool/int/float type instead of string