How do I return integer and numeric columns from MySQL as integers and numerics in PHP?

by

The solution is to ensure that you are using the mysqlnd driver for php.

How do you know that you’re not using mysqlnd?

When viewing php -i, there will be no mention of “mysqlnd”. The pdo_mysql section will have something like this:

pdo_mysql

PDO Driver for MySQL => enabled Client API version => 5.1.72

How do you install it?

Most installation guides for L/A/M/P suggest apt-get install php5-mysql but the native driver for MySQL is installed by a different package: php5-mysqlnd. I found that this was available with the ppa:ondrej/php5-oldstable.

To switch to the new driver (on Ubuntu):

  • Remove the old driver:
    apt-get remove php5-mysql
  • Install the new driver:
    apt-get install php5-mysqlnd
  • Restart apache2:
    service apache2 restart

How do I check that the driver is being used?

Now php -i will mention “mysqlnd” explicitly in the pdo_mysql section:

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.10 - 20111026 - $Id:      e707c415db32080b3752b232487a435ee0372157 $

PDO settings

Ensure that PDO::ATTR_EMULATE_PREPARES is false (check your defaults or set it):
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Ensure that PDO::ATTR_STRINGIFY_FETCHES is false (check your defaults or set it):
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

Returned values

  • Floating-point types (FLOAT, DOUBLE) are returned as PHP floats.
  • Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT †) are returned as PHP integers.
  • Fixed-Point types (DECIMAL, NUMERIC) are returned as strings.

† BIGINTs with a value greater than a 64 bit signed int (9223372036854775807) will return as a string (or 32 bits on a 32 bit system)

    object(stdClass)[915]
      public 'integer_col' => int 1
      public 'double_col' => float 1.55
      public 'float_col' => float 1.5
      public 'decimal_col' => string '1.20' (length=4)
      public 'bigint_col' => string '18446744073709551615' (length=20)

More Related Contents:

  1. PHP / PDO echo data from database into a table
  2. Can I mix MySQL APIs in PHP?
  3. How to apply bindValue method in LIMIT clause?
  4. PDOException “could not find driver”
  5. Row count with PDO
  6. PDO get the last ID inserted
  7. How do I use password hashing with PDO to make my code more secure? [closed]
  8. Error when preparing a multiple insert query
  9. How to view query error in PDO PHP
  10. PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
  11. Insert/update helper function using PDO
  12. pdo prepared statements with wildcards
  13. Insert multiple rows with PDO prepared statements
  14. Do SQL connections opened with PDO in PHP have to be closed
  15. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  16. php code to test pdo is available?
  17. PDO fetchAll group key-value pairs into assoc array
  18. What is the advantage of using try {} catch {} versus if {} else {}
  19. Alternative for mysql_num_rows using PDO
  20. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  21. Unknown database error in PHP but it exists in PHPMyAdmin
  22. How do detect that transaction has already been started?
  23. Binding parameters for WHERE IN clause with PDO [duplicate]
  24. return one value from database with mysql php pdo
  25. Trying to access array offset on value of type bool
  26. return multiple response data in one response
  27. How to get a query error from prepare() in PDO PHP?
  28. How can I pass an array of PDO parameters yet still specify their types?
  29. PHP PDO how to run a multiple query request?
  30. Using PDO Prepared Statement and Incrementing a column value

Leave a Comment