19

I have tried fetching MySQL query results using mysql_fetch_row() and mysql_result() and numeric values are being returned as strings.

Is there any way to fetch the data as its datatype stored in the table?

The application will be querying many different queries so I will be unable to cast the values as the intended datatype on a 1 by 1 basis.

Evan Carslake
  • 2,267
  • 15
  • 38
  • 56

5 Answers5

19

I don't think getting data in their native datatypes (i.e. anything else that strings) can be done in PHP 5.2...

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

After more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

It says this (quote) :

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not the old libmysql)), and seems to only be the case for prepared statements :-(

Which doesn't quite help, in your situation, I guess...


And here's another one, still about the new features of mysqlnd, which talks about this for not only prepared statements : PHP: New network traffic, CPU and memory savings with mysqlnd.

Not sure this has been merged into the official mysqlnd driver, though -- best way would be to try ; but it'll still be PHP >= 5.3 only, anyway...


Another solution would be to have, on the PHP-side, some kind of a mapping-system (like an ORM) to convert results coming from the DB to PHP datatypes...

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

hakre
  • 193,403
  • 52
  • 435
  • 836
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • Thanks Pascal MARTIN! I've been googling for several hours and haven't found anything I could use. I'm relatively new to PHP, but it sounds like I'll need to go with PDO if I want the native support. Unfortunately my host is running 5.2.something... Also difficult, is that I'm trying to create an xml report file input that houses different query info. The PHP script reads the requested xml file and sends the queries off to the database, so I really won't ever know what data is being queried and how it should be returned. time to get creative I guess :) –  Mar 12 '10 at 05:26
  • 1
    PDO or not PDO should not change anything, in this case : what matters is the drivers used underneath *(i.e. libmysql vs mysqlnd)* -- and I'm not even sure that mysqlnd in PHP 5.3.2 does return native types for standard queries ;;; in your case, anyway, with PHP 5.2, the only solution you'll have would be some mapping layer, that does type conversions when needed... – Pascal MARTIN Mar 12 '10 at 05:35
  • Note that you will need to [turn off prepared statement emulation](http://stackoverflow.com/a/15592818/441684) if using PDO. – Alexander Garden Mar 24 '13 at 10:08
11

try this if using mysqli instead of PDO

$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
alexl
  • 1,842
  • 1
  • 14
  • 15
3

I've implemented this the manual way. It's actually not too bad, just a few lines.

As suggested, call mysqli_fetch_fields() on the resource resulting from your query.

Then from a mapping of the PHP field type numbers to MySQL data types (see industrious work here http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php) you can convert your values from the wide range of database types returned as strings by MySQLi, into the appropriate type in PHP.

How much of a slowdown it is I'm not sure tho.

poop-deck
  • 250
  • 1
  • 9
1

In addition to Pascal MARTIN's answer, if you use MySQLi prepared statements you will get the data back using native types. Try this:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>

The above example will output:

id = 1 (integer)
label = a (string)

You can get more info here: https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.quickstart.prepared-statements.html

Dharman
  • 30,962
  • 25
  • 85
  • 135
Marcos
  • 276
  • 6
  • 14
0

I wrote a function to circuvent this (for PDO):

/**
 * Converts columns from strings to types according to 
 * PDOStatement::columnMeta
 * 
 * @param PDOStatement $st
 * @param array $assoc returned by PDOStatement::fetch with PDO::FETCH_ASSOC
 * @return copy of $assoc with matching type fields
 */
function convertTypes(PDOStatement $statement, $assoc)
{
    for ($i = 0; $columnMeta = $statement->getColumnMeta($i); $i++)
    {
        $type = $columnMeta['native_type'];

        switch($type)
        {
            case 'DECIMAL':
            case 'TINY':
            case 'SHORT':
            case 'LONG':
            case 'LONGLONG':
            case 'INT24':
                $assoc[$columnMeta['name']] = (int) $assoc[$columnMeta['name']];
                break;
            case 'DATETIME':
            case 'DATE':
            case 'TIMESTAMP':
                $assoc[$columnMeta['name']] = strtotime($assoc[$columnMeta['name']]);
                break;
            // default: keep as string
        }
    }

    return $assoc;
}

Of course the type list are not complete and the conversion is oversimplified, but can be useful for start.

sevcsik
  • 1,210
  • 12
  • 12