Mysqli and PDO query out the type conversions

1. There has always been a problem, that is, PHP with Mysqli or PDO, the result of the query is the int type of data will automatically become a string type, PHP itself weak type. But now write the API, directly from the database to find out the results, and then json_encode back to the client is not. Both Java and OC are strongly typed numbers, which are numbers, and strings are strings. 123! = "123"

This is the program code, with the Mysqli class

This is the table structure.

This is the result of the query.

1. Google has a lot to say, PDO has a solution to the problem of PDO query int converted to string. But Mysqli did not find it, and did not know what the problem was. Or there are some ready-made class libraries that can be converted automatically.

Reply content:

1. There has always been a problem, that is, PHP with Mysqli or PDO, the result of the query is the int type of data will automatically become a string type, PHP itself weak type. But now write the API, directly from the database to find out the results, and then json_encode back to the client is not. Both Java and OC are strongly typed numbers, which are numbers, and strings are strings. 123! = "123"

This is the program code, with the Mysqli class

This is the table structure.

This is the result of the query.

1. Google has a lot to say, PDO has a solution to the problem of PDO query int converted to string. But Mysqli did not find it, and did not know what the problem was. Or there are some ready-made class libraries that can be converted automatically.

When you turn off impersonation preprocessing, you can persist data types, but some types, the output data is inconsistent with the values in the database:
For example, a float (9,2) type of field with a field content of 32.10,pdo closed when simulated preprocessing reads a value of 32.099998474121 and the type is float.
This means that PDO simulation preprocessing (default) is turned on, and the data type returned is string, and the value is consistent with the contents of the database store.
When the MySQL server does not use preprocessing, it returns data of type String.

Mysqli the field type returned by not using the Prepare query is all string, and the value is consistent with the contents of the database store.
When using prepare, the same type of data is returned (the decimal type is still a string), and the value of the float type is inconsistent, just as pdo_mysql turns off impersonation preprocessing.

Therefore, data with a decimal point in the database is recommended to be saved with a string type or decimal type.

Type conversions:
Settype ($foo, "array");
Settype ($foo, "bool");
Settype ($foo, "boolean");
Settype ($foo, "float");
Settype ($foo, "int");
Settype ($foo, "integer");
Settype ($foo, "null");
Settype ($foo, "object");
Settype ($foo, "string");
$foo = (array) $foo;
$foo = (b) $foo; From PHP 5.2.1
$foo = (binary) $foo; From PHP 5.2.1
$foo = (bool) $foo;
$foo = (Boolean) $foo;
$foo = (double) $foo;
$foo = (float) $foo;
$foo = (int) $foo;
$foo = (integer) $foo;
$foo = (object) $foo;
$foo = (real) $foo;
$foo = (string) $foo;

You can use mysqli_result the object's fetch_field methods to get the metadata for the field. See also: Php:mysqli_result::fetch_field-manual.

The relevant methods also have fetch_fields , fetch_field_direct .

Let's give an example:

//获取到$result之后$types = array();while ( $finfo = $result->fetch_field() ) {    if ( in_array( $finfo->type, array(        MYSQLI_TYPE_BIT,        MYSQLI_TYPE_TINY,        MYSQLI_TYPE_SHORT,        MYSQLI_TYPE_LONG,        MYSQLI_TYPE_LONGLONG,        MYSQLI_TYPE_INT24    ) ) {        $type = 'int';    } else if ( in_array( $finfo->type, array(         MYSQLI_TYPE_FLOAT,        MYSQLI_TYPE_DOUBLE,        MYSQLI_TYPE_DECIMAL,        MYSQLI_TYPE_NEWDECIMAL     ) ) {         $type = 'float';     } else {         //其他需要转换的类型请自行研究         //类型常量请参见http://php.net/manual/zh/mysqli.constants.php，MYSQLI_TYPE开头的那些就是         $type = 'string';     }     $types[$finfo->name] = $type;}$result = $result->fetch_assoc();foreach ( $result as $name => &$value ) {    settype($value, $types[$name]);}//现在$result里的数据类型已经符合要求了

