How to Get Numeric Types from MySQL Using Pdo

How to get numeric types from MySQL using PDO?

I don't think having "numbers" 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.

Well, 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 old libmysql)), and seems to only be the case for prepared statements :-(

Sorry...

A solution would be to have, on the PHP-side, a mapping-system (like an ORM -- see Doctrine ; just as an example of ORM : I don't know if it does what you're asking) 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...

Inserting numeric data in mySQL database using PDO (PHP)

The problem here is in PHP POST data is string so you need to convert it before insert.

<--PDO code-->
<?php
$pdo = new PDO('mysql:host=localhost;port=8888;dbname=xxxx', 
   'xxx', 'xxx');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 //SQL 
 $sql = "INSERT INTO cars (make, model, year, mileage)
                  VALUES (:make, :model, :year, :mileage)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array(
            ':make' => $_POST['make'],
            ':model' => $_POST['model'],
            ':year' => intval($_POST['year']),
            ':mileage' => intval($_POST['mileage'])));
        $_SESSION['success'] = 'Record Added';
        header( 'Location: index.php' ) ;
        return;
?>

<form method="post">
<p>Make:
<input type="text" name="make"></p>
<p>Model:
<input type="text" name="model"></p>
<p>Year:
<input type="number" name="year"></p>
<p>Mileage:
<input type="number" name="mileage"></p>
<p><input type="submit" value="Add New"/>

How do I return integer and numeric columns from MySQL as integers and numerics in PHP?

The solution is to ensure that you are using the mysqlnd driver for php.

How do you know that you're not using mysqlnd?

When viewing php -i, there will be no mention of "mysqlnd". The pdo_mysql section will have something like this:

pdo_mysql

PDO Driver for MySQL => enabled Client API version => 5.1.72

How do you install it?

Most installation guides for L/A/M/P suggest apt-get install php5-mysql but the native driver for MySQL is installed by a different package: php5-mysqlnd. I found that this was available with the ppa:ondrej/php5-oldstable.

To switch to the new driver (on Ubuntu):

• Remove the old driver:
  
  apt-get remove php5-mysql
• Install the new driver:
  
  apt-get install php5-mysqlnd
• Restart apache2:
  
  service apache2 restart

How do I check that the driver is being used?

Now php -i will mention "mysqlnd" explicitly in the pdo_mysql section:

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.10 - 20111026 - $Id:      e707c415db32080b3752b232487a435ee0372157 $

PDO settings

Ensure that PDO::ATTR_EMULATE_PREPARES is false (check your defaults or set it):

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Ensure that PDO::ATTR_STRINGIFY_FETCHES is false (check your defaults or set it):

$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

Returned values

• Floating-point types (FLOAT, DOUBLE) are returned as PHP floats.
• Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT †) are returned as PHP integers.
• Fixed-Point types (DECIMAL, NUMERIC) are returned as strings.

† BIGINTs with a value greater than a 64 bit signed int (9223372036854775807) will return as a string (or 32 bits on a 32 bit system)

    object(stdClass)[915]
      public 'integer_col' => int 1
      public 'double_col' => float 1.55
      public 'float_col' => float 1.5
      public 'decimal_col' => string '1.20' (length=4)
      public 'bigint_col' => string '18446744073709551615' (length=20)

How can I fetch correct datatypes from MySQL with PDO?

You could use a different fetch_style. If you have a User class, you could force PDO to return a instance of the User class with

$statement->fetchAll(PDO::FETCH_CLASS, "User");

You can now take care of properties validations and casting inside the User class, where it belongs.

Related:

• PHP PDO: Fetching data as objects - properties assigned BEFORE __construct is called. Is this correct?
• PDO PHP Fetch Class

Why not PDO_MySQL return integer?

Set PDO::ATTR_EMULATE_PREPARES to false, if you really need it with loosely-typed PHP

If mysql_fetch_row returns you int for SUM either (I never cared to check) - then it does some magic like if (ctype_digit($val)) $row[$key] = (int)$val; - so you can do in your DBAL

As far as I understand the way prepared statements works, it uses the same packet structure for either sending and retrieving data, and this packet contains data type.

It looks like that server can return data in 2 formats - native and mysqlnd, depends on the request type. A latter one can be interpreted by client library to cast resulting value.

How to get the type of a query statement in PDO?

I was looking for the same answer and stumbled across this article. It was last updated in August. In it, there is a section: "Determining the Type of a Statement" You basically can make the following assumptions: (copied from the article)

• If columnCount() is zero, the statement did not produce a result set. Instead, it modified rows and you can invoke rowCount() to determine the number of affected rows.
• If columnCount() is greater than zero, the statement produced a result set and you can fetch the rows. To determine how many rows there are, count them as you fetch them.

I'll save you the trouble and just paste the code sample here

$sth = $dbh->prepare ($stmt);
$sth->execute ();
if ($sth->columnCount () == 0)
{
    # there is no result set, so the statement modifies rows
     printf ("Number of rows affected: %d\n", $sth->rowCount ());
}
else
{
    # there is a result set
    printf ("Number of columns in result set: %d\n", $sth->columnCount ());
    $count = 0;
    while ($row = $sth->fetch (PDO::FETCH_NUM))
    {
    # display column values separated by commas
       print (join (", ", $row) . "\n");
       $count++;
    }
}

---

Related Topics