Geek Answers Handbook

Morphing from mysql to mysqli

I am a little puzzled by the mysqli interface in PHP. When yesterday I decided to change my old mysql code to mysqli, the documents sent me along the path ::prepare , ::bind_params and ::execute .

In my efforts to work with results, I ended up writing my own fetch_assoc - and had problems that I talked about in SO. One commenter asked that I did not use ::fetch_assoc . I did not even notice the mysqli_result class. When I changed my mysqli code from ::preapre to ::query , processing the results became much simpler - with one exception: when I used ::bind_results to retrieve columns earlier, the resulting PHP associative array would lead to the correct data types; when instead I now use ::fetch_assoc etc., all I get is an array of strings.

Now I am encoding the converter. I just thought that since I had already made one cardinal mistake in my understanding of the mysqli library, I better post here to find out if there is an “official” way to get congruent associative arrays of results.

Thanks.

+2
source share
1 answer

http://docs.php.net/mysqli-result.fetch-assoc says:

Returns an associative array of strings representing the selected row in the result set

update: "Back to :: prepare and :: execute. I just don't believe it ... ;-)" - And you're right!
If you use mysqlnd as a transport client and set the MYSQLI_OPT_INT_AND_FLOAT_NATIVE option on the mysqli object, you are actually getting 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)
 }

Did not find it in the manual, so you can check when this parameter was added to ext / mysqli / mysqli.c
edit: he first appeared here: http://svn.php.net/viewvc/php/php-src/trunk/ext/mysqli/mysqli.c?r1=263494&r2=266352

To get started with PDO (if you decide 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()
+4
source

More articles:


All Articles