MySQL and Native Types

Using the MySQL Native Driver (mysqlnd), already introduced back in 2009 for PHP 5.3, to connect from PHP to a MySQL database server is now even more prudent than before. The binary communication protocol used by mysqlnd encodes data in binary form when server-side prepared statements are used. This allows both the mysqli and pdo_mysql extensions to use native PHP types such as float or integer to represent data coming from the MySQL server.

Do note, though, that both the mysqli and pdo_mysql extensions will represent data coming from the MySQL server as strings when direct queries are used. Data coming from the MySQL server will also be represented as strings when server-side prepared statements are used but the data cannot be represented using a native PHP type without data loss.

For the following code example we will use the database table shown below:

MariaDB [example]> DESCRIBE example;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
| b     | double  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


MariaDB [example]> SELECT * FROM example;
+------+-------+
| a    | b     |
+------+-------+
| 2204 | 3.141 |
+------+-------+
1 row in set (0.00 sec)

mysqli

First, we implement the best practice of configuring the mysqli extension’s error handling to use exceptions using its procedural API:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

The same can be achieved by setting the MySQLi_Driver->report_mode property:

$driver = new MySQLi_Driver;
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

Then we connect to our database server:

$connection = new MySQLi(
    'localhost',
    'example',
    'password',
    'example'
);

By setting MYSQLI_OPT_INT_AND_FLOAT_NATIVE to 1 we enable the mapping of MySQL’s integer and float types to PHP’s respective native types:

$connection->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

Now we prepare and execute our query:

$statement = $connection->prepare('SELECT a, b FROM example;');
$statement->execute();

Finally we retrieve the result set:

$result = $statement->get_result();
$row    = $result->fetch_assoc();

var_dump($row['a']);
var_dump($row['b']);

The code shown above will print the output shown below:

int(2204)
float(3.141)

pdo_mysql

First we connect to our database server:

$connection = new PDO(
    'mysql:host=localhost;dbname=example',
    'example',
    'password'
);

Then we disable PDO’s emulation of prepared statements and enable MySQL’s usage of server-side statements:

$connection->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 0);

Now we prepare and execute our query:

$statement = $connection->prepare('SELECT a, b FROM example;');
$statement->execute();

Finally, we retrieve the result set:

$row = $statement->fetch();

var_dump($row['a']);
var_dump($row['b']);

The code shown above will print the output shown below:

int(2204)
float(3.141)