PDO returns integer columns as String in PHP5.4

Question

First of all, I am aware that there are various similar questions on SO such as this and this. However, when I fetch values from a table, integers are always fetched as string.

I am using PHP5.4 (5.4.16-1~dotdeb.1) and MYSQL5.5 (5.5.31+dfsg-0+wheezy1). It is written here that MySQL Native Driver is enabled by default in PHP5.4.0. But I still get string values.

I initialize a PDO object as follows.

try {
        $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8';

        $db = new PDO($dsn,DB_USER,DB_PASS);

        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    } catch (PDOException $e) {
        header('HTTP/1.1 500');
        exit;
    } catch (Exception $e) {
        header('HTTP/1.1 500');
        exit;
    }

When I insert, I tried to use execute(array(...)) format and also used bindValue(...,PDO::PARAM_INT), but they did not make a difference.

For example, here is how I insert a new row.

public function insertList ($db,$account_id,$list_name) {
    $sql = $db->prepare('INSERT INTO lists VALUES (?,?,?,?,?)');

    try {
        // $sql->execute(array($list_name,0,0,0,$account_id));

        $sql->bindValue(1,$list_name,PDO::PARAM_STR);
        $sql->bindValue(2,0,PDO::PARAM_INT);
        $sql->bindValue(3,0,PDO::PARAM_INT);
        $sql->bindValue(4,0,PDO::PARAM_INT);
        $sql->bindValue(5,$account_id,PDO::PARAM_INT);
        $sql->execute();
    } catch (PDOException $e) {
        header('HTTP/1.1 500');
        exit;
    } catch (Exception $e) {
        header('HTTP/1.1 500');
        exit;
    }
}

Here is how I fetch rows from a table

public function fetchLists ($db,$account_id) {
    $sql = $db->prepare('SELECT * FROM lists WHERE account_id=?');

    try {
        $sql->execute(array($account_id));

        $result = $sql->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        header('HTTP/1.1 500');
        exit;
    } catch (Exception $e) {
        header('HTTP/1.1 500');
        exit;
    }

    return $result;
}

This did not occur when I tested on XAMPP for Linux 1.8.1 which uses PHP5.4.7. I currently use nginx instead of Apache.

What is wrong?

I think it may help you http://stackoverflow.com/questions/6558593/is-pdoparam-int-redundant — Robert, Jun 20 '13 at 11:58
The question says "returns" but there is no code here that returns anything. — Jon, Jun 20 '13 at 11:58
I don't understand this question you write about fetching but you have insert statement, how did you check it? — Robert, Jun 20 '13 at 12:04
Thanks, I just added a code that I use to fetch rows from a table. — CookieEater, Jun 20 '13 at 12:05
Just curious - do you really add that header(500) to the every try catch manually? — Your Common Sense, Jun 20 '13 at 12:18
Also it makes me wonder why do you use a PHP manual to check details of *your* particular installation. — Your Common Sense, Jun 20 '13 at 12:21
@YourCommonSense, Not really. I was just putting there for testing purpose. Also, since I am dealing with PHP, wouldn't it make sense to consult the PHP manual? — CookieEater, Jun 20 '13 at 13:01
Depends on what you want to consult for. Regarding these headers - the very code you posted but without this try..catch stuff will behave WAY better and correct way. Just give it a try — Your Common Sense, Jun 20 '13 at 13:14
Manual tells of *default* setting. The word "default" doesnt mean "guaranteed ever". It's just a suggestion that might be overridden. That's why it's always better to check the *actual* value, not documentation. — Your Common Sense, Jun 20 '13 at 14:18

Your Common Sense · Accepted Answer · 2016-07-04T11:45:24.027

4

To get integers and floats with respective types from mysql with PDO, you need both mysqlnd-based PDO-mysql and emulation mode turned off.

edited Jul 04 '16 at 11:45

answered Jun 20 '13 at 12:17

Your Common Sense

156,878
40
214
345

6

Why would this be an odd feature? If I can fetch integer columns as integer, I wouldn't need to convert them into integer manually. – CookieEater Jun 20 '13 at 13:04
2

I am not entirely familier with PHParta. I'll take a look. – CookieEater Jun 20 '13 at 13:37
10

The main reason why I want to convert them into integer is that when I send these variables back to a client side, I want them to be integers. In JavaScript, `"11" + 9` does not equal 20, so I always need to convert it to integer manually. – CookieEater Jun 20 '13 at 14:13
Why do you think JSON encoding could be an issue? `json_encode('1') === '1'` and `json_encode(1) === 1`, nothing is lost of altered. – ken Jun 05 '14 at 17:00
6

Why is this an odd feature? I just started changing my database code to PDO from `mysql_` and my `$row['active'] == 1` code failed because it's a damn string. Who even though that returning values entirely as strings is even close to a good idea?! – Nick Bedford Dec 08 '14 at 04:16

PDO returns integer columns as String in PHP5.4

1 Answers1

Linked

Related