MySQL integer field returned as string in PHP

Question

I have a table field in a MySQL database:

userid INT(11) 

Therefore, I am calling to my page with this request:

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

Then, to process the result, I do:

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

Now if I do:

 echo gettype($id); 

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

Answer 1

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

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

Or using the intval() function:

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

Answer 2

Use mysqlnd (native driver) for php.

If you are on Ubuntu:

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

If you are on Centos:

 sudo yum install php-mysqlnd sudo service httpd restart 

The native driver returns integer types accordingly.

Edit:

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

Example:

 $mysqli = mysqli_init(); $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE); 

Answer 3

My solution is to pass the result of the $rs query and get the data receiver array as the return value:

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

Usage example:

 $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 

Answer 4

The simplest solution:

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

 json_encode($data, JSON_NUMERIC_CHECK) 

(since PHP 5.3.3).

Or you can just specify your id in int.

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

Answer 5

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

You need to specify your identifier in int.

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

Answer 6

I like Chad's answer, especially when the query results are passed javascript in the browser. Javascript treats purely numerical objects as numbers, but requires additional work to process numerical objects such as strings. that is, should use parseInt or parseFloat on them.

Based on the Chad solution, I use this, and this is often exactly what I need, and creates structures that can be JSON encoded for the convenience of working with 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 number string to 0 creates a number type in PHP and correctly identifies the type, so floating point numbers will not be truncated to integers.

Answer 7

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

You can rename fields in your table so that it is clear what type of data is stored.

For example, you can add a special suffix for each number field: if userid is INT(11) , you can rename it userid_i or if it is UNSIGNED INT(11) , you can rename userid_u . At this point, you can write a simple PHP function that takes an associative array as input (obtained using mysql_fetch_assoc ) and mysql_fetch_assoc to the “value” stored with these special “keys”.

Answer 8

You can do it with ...

• mysql_fetch_field ()
• mysqli_result :: fetch_field_direct or
• 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 on these hyperlinks explain well how to set your type from a plain old string to the original type in the database.

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

You can also make guesses - for example, scroll through the resulting result lines and use is_numeric () for each. Something like:

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

That would turn everything that looks like a number into one ... definitely not perfect.

Answer 9

If prepared statements are used, the type will be where it is needed. This code returns an array of strings, where each row is an associative array. For example, if fetch_assoc() was called for all rows, but with stored type information.

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

Answer 10

MySQL has drivers for many other languages, converting data into string "standardized" data and leaving it to the user to enter values ​​of type int or other

Answer 11

In my case, the mysqlnd.so extension was installed. BUT I did not have pdo_mysqlnd.so . So, the problem was solved by replacing pdo_mysql.so with pdo_mysqlnd.so .

Answer 12

This happens when PDO::ATTR_EMULATE_PREPARES is true for the connection.

Answer 13

Putting a plus in front of a variable also does the trick,

 $a = "120"; $b = +$a; echo gettype($b); 

Obviously, this is not the best way, but it works when you do not have access to the environment to install the best driver.

Answer 14

I like the skill technique , but 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 a query check returning false, not a result set.
And checking the line with the field having a null value.
And if the desired type is a string, I do not spend time on it - it is already a string.

---

I am not worried about using this in most PHP code; I just rely on php automatic type conversion. But if you request a lot of data, and then perform arithmetic calculations, it is reasonable to lead to optimal types in advance.