MySQL integer field is returned as string in PHP

Question

I have a table field in a MySQL database:

userid INT(11)

So I am calling it to my page with this query:

"SELECT userid FROM DB WHERE name='john'"

Then for handling the result I do:

$row=$result->fetch_assoc();

$id=$row['userid'];

Now if I do:

echo gettype($id);

I get a string. Shouldn't this be an integer?

Axum

the MySQL query gives row values not row types or any other associated information. Just the raw data that's in each table cell. You'll have to account for this in PHP – Sheol

If you need the column types, see here forums.whirlpool.net.au/archive/526795 – Stingo

For readers, selected answer involves iterating through the results. But there is a better solution. https://mcmap.net/q/102431/-how-to-get-numeric-types-from-mysql-using-pdo – Scagliola

@DanHanly - technically, the MYSQL query (in PHP) returns a string representation of the cell value - the actual cell value stored in the database for a 32-bit integer is ... a 32-bit integer, not a string of digits. – Obstetric

for me, when i used $conn->query("your query") i got the integer fields as string but when i used $conn->prepare("your query") i got the parameters as they were in the database – Landahl

Answer 1

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']);

Monicamonie

Having come to MySQL from a Postgres background I feel the need to say that this is a huge pain in the arse. When you fetched a row in Postgres you coudl be sure that the elements in the row array had appropriate data types. Now I'm having to write code to manually cast every element to the data type I'm expecting. – Curator

I just did some tests on Windows with Laravel and Mysql on the very same schema and database server. On Windows the primary key is returned as an Integer and on Linux it's a String. – Reproductive

Ok it's a good idea but what if you have thousands of fields to retrieve ? This process take a while.. In my exemple I retrieve number of views for each post, then I print all my post in a table, I allow user to sort by View asc and desc, but it sort by "1" to "9" or "9" to "1" so in first place you could have "9999", "91", "8111", "7", etc... – Lingle

@zehelvion have you ever found a solution to this? – Asserted

@FelipeFrancisco Did not find a solution. Knowing what to expect, allows to adapt your code for the production environment. – Reproductive

Every field returned will be a string OR NULL. – Dreiser

So why am I getting int values for int columns (I'm using prepared statements and tested on windows) ? ! – Odell

Fix all the results with this is not affordable for big application. Solution should be something in the Mysqli connection option – Comstock

@Curator Not true. For example booleans are returned as the string "t" or "f" and an integer column with the value 3 will be returned as the string "3". Perhaps you were using a different library in PHP (such as PDO) rather than the builtin pg_ functions? – Crandale

@Crandale Given that it was 7 years ago I don't remember! – Curator

This is not a correct answer. And with modern PHP installations (which use mysqlnd by default) there is a setting for this. $mysqlidb->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true); – Workbag

This is best answer: https://mcmap.net/q/156241/-mysql-integer-field-is-returned-as-string-in-php – Caffey

This is apparently very different than bind_result and fetch... because the row returned from my mysqli is returning mixed (string), (int), (float), and NULL. Which is wrecking havoc on code that is explicitly looking for === '0' and the like. Need to find a way to FORCE (string) always (opposite of OP). – Ledger

@Reproductive you have made my day! windows/MacOS you will get int in integer but when I deploy my code to Cpanel it shows me casting error that may be by linux hosting. – Grams

Answer 2

Use the mysqlnd (native driver) for php.

If you're on Ubuntu:

sudo apt-get install php5-mysqlnd
sudo service apache2 restart

If you're on Centos:

sudo yum install php-mysqlnd
sudo service httpd restart

The native driver returns integer types appropriately.

As @Jeroen has pointed out, this method will only work out-of-the-box for PDO.
As @LarsMoelleken has pointed out, this method will work with mysqli if you also set the MYSQLI_OPT_INT_AND_FLOAT_NATIVE option to true.

Example:

$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);

Lasalle

I do not see any proof of this stament. Running with mysqlnd and mysqli, clearly string values are returned. – Ginnifer

@Ginnifer are you sure? There's plenty of documentation available. #1197505 – Lasalle

yes. Also look at the comments below. Did you test this yourself? as it looks now, even mysqlnd only returns appropriate types if you use prepared statements. example: $link = mysqli_connect('localhost', 'root', ''); mysqli_set_charset($link,'utf8'); $result = mysqli_query($link,'SELECT CAST(\'3.51\' AS DECIMAL(3,2))'); $row = mysqli_fetch_assoc($result); var_dump($row); returns: array(1) { ["CAST('3.51' AS DECIMAL(3,2))"]=> string(4) "3.51" } – Ginnifer

Ah, I didn't realize you were using mysqli. The native driver will only give you types for PDO. – Lasalle

Not how I understand it, it should work equally with PDO and Mysqli. Even on the link you post above in the comment it is clearly stated that it ONLY WORKS WITH PREPARED STATEMENTS and not inline queries as per the OP. quote from that page: But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not old libmysql)), and seems to only be the case for prepared statements :-( – Ginnifer

This solution will work but if you are using the phpmyadmin in linux it will not work. To use both mysqlnd and phpmyadmin check here. – Treen

I just test it out, after install php5-mysqlnd really works on Ubuntu. – Tamis

I was looking for a solution that wasn't involving me getting in the middle of the framework's ORM, and my application code, and this is exactly what I've been looking for. Thanks a lot! – Delija

Its fixed the issue. – Dianetics

Had this issue when comparing my local Laravel environment to my Ubuntu Laravel environment. My "active" field was a tinyint but was returned on the server as a string. So when the if($active==1) {//pass} worked on my local environment and not the server, I was stumped. Used the answer marked correct just to be sure it worked, then installed this and tried it again and it worked great. Thanks to @Lasalle and @Ginnifer for figuring this out! – Agro

you can also use "MYSQLI_OPT_INT_AND_FLOAT_NATIVE" for mysqli – Borderline

I have confirmed that INTs and FLOATs are returned as PHP numbers with mysqli when php-mysqlnd is installed and active, AND MYSQLI_OPT_INT_AND_FLOAT_NATIVE is set to TRUE (ex: $mysqli = mysqli_init(); $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE); – Bamberg

@TimothyZorn - on what OS? – Obstetric

Procedural Method: mysqli_options($conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);. Thanks, it really works – Schutt

Answer 3

Easiest Solution I found:

You can force json_encode to use actual numbers for values that look like numbers:

json_encode($data, JSON_NUMERIC_CHECK) 

(since PHP 5.3.3).

Or you could just cast your ID to an int.

$row = $result->fetch_assoc();
$id = (int) $row['userid'];

Vexed

Using a json_encode to cast string to int if possible is like using microscope to choke nails. – Moriahmoriarty

This will break all the postalcodes and telephone number. Good way to introduce Bugs in your App. The solution MUST respect the mysql column datatype, not try to guess from the value. – Comstock

Max Cuttins, your point is valid. However, if you can account for all the data types expected from the DB, this would then be a perfect route to take. – Steal

This will hurt you when you sometime have ids of some kind like '06' . This flag will convert it to 6. Which is wrong because those ids should kept trailing zeros. – Drayton

This is a great solution for values created by UNIX_TIMESTAMP(), for example. – Sorrow

Loved it because I need it for ID - Primary Key only. – Berlyn

This is perfect for my issue since I only care about some Booleans I'm passing to Javascript. Excellent solution, when used judiciously. – Orthogenic

Answer 4

My solution is to pass the query result $rs and get a assoc array of the casted data as the return:

function cast_query_results($rs) {
    $fields = mysqli_fetch_fields($rs);
    $data = array();
    $types = array();
    foreach($fields as $field) {
        switch($field->type) {
            case 3:
                $types[$field->name] = 'int';
                break;
            case 4:
                $types[$field->name] = 'float';
                break;
            default:
                $types[$field->name] = 'string';
                break;
        }
    }
    while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
    for($i=0;$i<count($data);$i++) {
        foreach($types as $name => $type) {
            settype($data[$i][$name], $type);
        }
    }
    return $data;
}

Example usage:

$dbconn = mysqli_connect('localhost','user','passwd','tablename');
$rs = mysqli_query($dbconn, "SELECT * FROM Matches");
$matches = cast_query_results($rs);
// $matches is now a assoc array of rows properly casted to ints/floats/strings

Submissive

Great solution except 'NULL' is also a variable type used by the settype() function. So your code changes all NULL values returned by the database (values where gettype() == 'NULL') into 'string' type with '' value, 'integer' type with 0 value, or 'float' type with 0.0 value. You need to not call settype if is_null($data[$i][$name]) is true. – Victorie

I like mastermind's technique, but the coding can be simpler. – Obstetric

Answer 5

No. Regardless of the data type defined in your tables, PHP's MySQL driver always serves row values as strings.

You need to cast your ID to an int.

$row = $result->fetch_assoc();
$id = (int) $row['userid'];

Baese

It's not PHP that's responsible (directly) for this behaviour, it's the database driver. If you're interfacing with a Postgres database from PHP you'll get appropriate data types back. – Curator

Answer 6

This happens when PDO::ATTR_EMULATE_PREPARES is set to true on the connection.

Careful though, setting it to false disallows the use of parameters more than once. I believe it also affects the quality of the error messages coming back.

Allege

Answer 7

I like Chad's answer, especially when the query results will be passed on to javascript in a browser. Javascript deals cleanly with numeric like entities as numbers but requires extra work to deal with numeric like entities as strings. i.e. must use parseInt or parseFloat on them.

Building on Chad's solution I use this and it is often exactly what I need and creates structures that can be JSON encoded for easy dealing with in javascript.

while ($row = $result->fetch_assoc()) {
    // convert numeric looking things to numbers for javascript
    foreach ($row as &$val) {
        if (is_numeric($val))
            $val = $val + 0;
    }
}

Adding a numeric string to 0 produces a numeric type in PHP and correctly identifies the type so floating point numbers will not be truncated into integers.

Rambunctious

Answer 8

For mysqlnd only:

 mysqli_options($conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);

Otherwise:

  $row = $result->fetch_assoc();

  while ($field = $result->fetch_field()) {
    switch (true) {
      case (preg_match('#^(float|double|decimal)#', $field->type)):
        $row[$field->name] = (float)$row[$field->name];
        break;
      case (preg_match('#^(bit|(tiny|small|medium|big)?int)#', $field->type)):
        $row[$field->name] = (int)$row[$field->name];
        break;
      default:
        $row[$field->name] = $row[$field->name];
        break;
    }
  }

Convince

Answer 9

In my project I usually use an external function that "filters" data retrieved with mysql_fetch_assoc.

You can rename fields in your table so that is intuitive to understand which data type is stored.

For example, you can add a special suffix to each numeric field: if userid is an INT(11) you can rename it userid_i or if it is an UNSIGNED INT(11) you can rename userid_u. At this point, you can write a simple PHP function that receive as input the associative array (retrieved with mysql_fetch_assoc), and apply casting to the "value" stored with those special "keys".

Ethno

Answer 10

If prepared statements are used, the type will be int where appropriate. This code returns an array of rows, where each row is an associative array. Like if fetch_assoc() was called for all rows, but with preserved type info.

function dbQuery($sql) {
    global $mysqli;

    $stmt = $mysqli->prepare($sql);
    $stmt->execute();
    $stmt->store_result();

    $meta = $stmt->result_metadata();
    $params = array();
    $row = array();

    while ($field = $meta->fetch_field()) {
      $params[] = &$row[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $params);

    while ($stmt->fetch()) {
      $tmp = array();
      foreach ($row as $key => $val) {
        $tmp[$key] = $val;
      }
      $ret[] = $tmp;
    }

    $meta->free();
    $stmt->close();

    return $ret;
}

Teratoid

Answer 11

In my case mysqlnd.so extension had been installed. BUT i hadn't pdo_mysqlnd.so. So, the problem had been solved by replacing pdo_mysql.so with pdo_mysqlnd.so.

Unleavened

Answer 12

I like mastermind's technique, but the coding can be simpler:

function cast_query_results($result): array
{
    if ($result === false)
      return null;

    $data = array();
    $fields = $result->fetch_fields();
    while ($row = $result->fetch_assoc()) {
      foreach ($fields as $field) {
        $fieldName = $field->name;
        $fieldValue = $row[$fieldName];
        if (!is_null($fieldValue))
            switch ($field->type) {
              case 3:
                $row[$fieldName] = (int)$fieldValue;
                break;
              case 4:
                $row[$fieldName] = (float)$fieldValue;
                break;
              // Add other type conversions as desired.
              // Strings are already strings, so don't need to be touched.
            }
      }
      array_push($data, $row);
    }

    return $data;
}

I also added checking for query returning false rather than a result-set.
And checking for a row with a field that has a null value.
And if the desired type is a string, I don't waste any time on it - its already a string.

---

I don't bother using this in most php code; I just rely on php's automatic type conversion. But if querying a lot of data, to then perform arithmetic computations, it is sensible to cast to the optimal types up front.

Obstetric

Answer 13

You can do this with...

1. mysql_fetch_field()
2. mysqli_result::fetch_field_direct or
3. PDOStatement::getColumnMeta()

...depending on the extension you want to use. The first is not recommended because the mysql extension is deprecated. The third is still experimental.

The comments at these hyperlinks do a good job of explaining how to set your type from a plain old string to its original type in the database.

Some frameworks also abstract this (CodeIgniter provides $this->db->field_data()).

You could also do guesswork--like looping through your resulting rows and using is_numeric() on each. Something like:

foreach($result as &$row){
 foreach($row as &$value){
  if(is_numeric($value)){
   $value = (int) $value;
  }       
 }       
}

This would turn anything that looks like a number into one...definitely not perfect.

Blockbuster

Doesn't work for floats. They pass the is_numeric test. You should test for floats first. – Braca

@MartinvanDriel or any string that contains only numbers, but should be a string – Bohunk

@MartinvanDriel Try adding: if (is_float()) { $value = (float)$value; } – Hooghly

Answer 14

Since PHP 8.1 you're able to retrieve integers and floats with the according datatype instead of beeing converted to a string.

Integers and floats in result sets will now be returned using native PHP types instead of strings when using emulated prepared statements. This matches the behavior of native prepared statements. The previous behaviour can be restored by enabling the PDO::ATTR_STRINGIFY_FETCHES option.

https://www.php.net/manual/en/migration81.incompatible.php#migration81.incompatible.pdo.mysql

Nonparticipating

Answer 15

Like advait's answer above, the PDO driver can perform the behavior as expected (Integer database columns get retrieved as integer data types in PHP) with the mysqlnd driver. I was having this issue on a managed server where apparently mysqlnd wasn't being used by default. I didn't have root access to install the package via the package manager. Instead the PHP selector on cPanel allowed me to install the driver by enabling nd_pdo_mysql. (Looks like there was an equivalent driver for mysqli: nd_mysqli).

In my opinion, Michiel Pater's answer (currently the top answer), about how to cast values back to integers is a work-around and not a solution.

Flemish

Answer 16

If you are using classes/objects to store your db data, you can type cast its attributes, so it would be converted to the right type:

<?php
  class User
  {
    public int $id; // use type casting in class definition
  }
  
  $user1 = new User();
  $user1->id = $row['userid'];
  echo gettype($user1->id); // integer
?>

note: I would like to point out that Charlie solution above worked in my windows environment but changing the PDO::ATTR_EMULATE_PREPARES to false did not work when I tried to deploy my project on a linux server. I still got strings instead of numbers.

Orson

Answer 17

MySQL has drivers for many other languages, converting data to string "standardizes" data and leaves it up to the user to type-cast values to int or others

Circumspect

I believe primitive types are pretty much a "standard"? If a language-specific driver couldn't fill the needs for that language, I would say it is very disappointing – Sophisticated