MySQL Integer Field Is Returned as String in PHP

MySQL integer field is returned as string in PHP

When you select data from a MySQL database using PHP the datatype will always be converted to a string. You can convert it back to an integer using the following code:

$id = (int) $row['userid'];

Or by using the function intval():

$id = intval($row['userid']);

MySQL integer field returned as an integer in PHP

It does not always return as string. Maybe you are using PHP version upper than 5.3( the version of PHP 5.3 is compiled with mysqlnd (and not old libmysql)), uses mysqlnd as the native driver and the native driver returns integer types appropriately.
Please check which version you are currently using.
For more details you can go through this link.
http://blog.ulf-wendel.de/2008/pdo_mysqlnd-the-new-features-of-pdo_mysql/

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

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)

Why PHP considers MySQL INT columns as strings?

The issue sounds more like that $categories is already a Collection, not a Builder. Both the Collection and the Builder have a where() method, but their logic is not the same.

The where() method on the Builder will add a parameterized where clause to the query run against the database. In this case, the type of variable doesn't matter.

However, the where() method on the Collection will loop through the collection and return those results where the field in the first parameter is strictly equal (===) to the value passed in the second parameter. To change this, you can pass false as the third parameter, and it will use a loose comparison (==) instead of strict. Additionally, you could use whereLoose(), which is a shortcut for where() with the third parameter as false.

$array[] = $categories->where('pk_i_id', $category_id, false)->first();
// or
$array[] = $categories->whereLoose('pk_i_id', $category_id)->first();

---

If the incorrect field types are causing more issues that what you've described, then you may want to work on fixing the underlying issue. As has been pointed out in the linked posts, on your LAMP stack you need to replace the mysqld driver with the mysqlnd driver.

PostgreSQL integer field is returned as string in PHP

I searched a lot i found this problem is because of php does not support bigint in 32bit machine if your machine is x64 so you should check is your php x64 too or not:
you can check it by following script:

echo (PHP_INT_SIZE === 8) ? "64 bit " : "32 bit ";

this problem is not PDO issue and you cann't solve it with changing PDO options it's because of pgsql driver. pgsql return bigint as string if the used language doesn't have any type for bigint.

---

Related Topics