Morphing from mysql to mysqli

I am a little perplexed by the mysqli interface in PHP. When I set out yesterday to change my old mysql code to mysqli, the docs steered me down the road of ::prepare, ::bind_params and ::execute.

In my efforts to deal with the results, I ended up writing my own fetch_assoc - and had problems which I discussed on SO. One commentator queried what I was doing not using ::fetch_assoc. I had not even noticed the mysqli_result class. When I morphed my mysqli code from ::preapre to ::query, the result handling became MUCH easier - with one exception: when previously I used ::bind_results to extract the columns, the PHP associative array that resulted would carry the correct data types; when instead I now use ::fetch_assoc etc, all I get is an array of strings.

I am coding up a converter now. I just thought that since I already committed one cardinal error in my understanding of the mysqli library, I better post here to see whether there's an "official" way of getting type-congruent associative result arrays.

Solution

If you're using mysqlnd as the transport client and set the option MYSQLI_OPT_INT_AND_FLOAT_NATIVE on the mysqli object you get in fact the native types.

echo 'phpversion: ', phpversion(), "\n";

$m = new mysqli('localhost', 'localonly', 'localonly', 'test');
$m->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
$m->query('CREATE TABLE soTest (x int)');
$m->query('INSERT INTO soTest (x) VALUES (1),(2)');

$r = $m->query('SELECT x from soTest');
var_dump($r->fetch_assoc());

prints

phpversion: 5.3.3
array(1) {
  ["x"]=>
  int(1)
}

This feature has been added in PHP 5.3.

To get you started with PDO (should you choose to use it):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// the pdo_mysql driver uses emulated prepared statements by default
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// afaik only the mysqlnd client supports native types, with libmysql you'll get only strings/null
echo 'client: ', $pdo->getAttribute(PDO::ATTR_CLIENT_VERSION), "\n";

// set up test environment
$pdo->exec('CREATE TEMPORARY TABLE soTest (x int, y varchar(16))');
$pdo->exec("INSERT INTO soTest (x,y) VALUES (1,'a'),(2,null)");

// statement with positional parameter
$stmt = $pdo->prepare('SELECT x,y FROM soTest WHERE x>?');
$stmt->setFetchMode(PDO::FETCH_ASSOC);

$stmt->execute( array(0) );
foreach( $stmt as $row ) {
  foreach( $row as $col ) {
    echo gettype($col), '(', $col, ') ';
  }
  echo "\n";
}

prints

client: mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $
integer(1) string(a) 
integer(2) NULL()