CODEWITHSUNDEEP
phpmysqltypesintgettype
luca
luca

Reputation: 37136

MySQL integer field is returned as string in PHP

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?

Upvotes: 154

Views: 145131

Answers (17)

Rooskie
Rooskie

Reputation: 57

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.

Upvotes: 2

Stefano Rutishauser
Stefano Rutishauser

Reputation: 39

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

Upvotes: 2

Daniel Vidal
Daniel Vidal

Reputation: 19

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.

Upvotes: 0

advait
advait

Reputation: 6525

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

Upvotes: 93

tim
tim

Reputation: 2724

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;
    }
  }

Upvotes: 3

Charlie
Charlie

Reputation: 9108

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.

Upvotes: 6

ToolmakerSteve
ToolmakerSteve

Reputation: 21213

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.

Upvotes: 2

Anton
Anton

Reputation: 21

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.

Upvotes: 2

Larney
Larney

Reputation: 1684

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

Upvotes: 26

Didax
Didax

Reputation: 43

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".

Upvotes: 2

mastermind202
mastermind202

Reputation: 427

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

Upvotes: 19

wolfgang
wolfgang

Reputation: 7789

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

Upvotes: -1

d&#39;Artagnan Evergreen Barbosa
d&#39;Artagnan Evergreen Barbosa

Reputation: 875

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.

Upvotes: 5

Fredrik
Fredrik

Reputation: 597

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;
}

Upvotes: 2

Chad Hedgcock
Chad Hedgcock

Reputation: 11765

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.

Upvotes: 1

BoltClock
BoltClock

Reputation: 723498

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

Upvotes: 11

Michiel Pater
Michiel Pater

Reputation: 23023

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

Upvotes: 145

Related Questions