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 stackoverflow.com/questions/6558593/is-pdoparam-int-redundant — Robert
– Robert, Commented Jun 20, 2013 at 11:58
The question says "returns" but there is no code here that returns anything. — Jon
– Jon, Commented Jun 20, 2013 at 11:58
I don't understand this question you write about fetching but you have insert statement, how did you check it? — Robert
– Robert, Commented Jun 20, 2013 at 12:04
Thanks, I just added a code that I use to fetch rows from a table. — CookieEater
– CookieEater, Commented Jun 20, 2013 at 12:05
Just curious - do you really add that header(500) to the every try catch manually? — Your Common Sense
– Your Common Sense, Commented Jun 20, 2013 at 12:18

Your Common Sense · Accepted Answer · 2016-07-04 11:45:24Z

5

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 4, 2016 at 11:45

answered Jun 20, 2013 at 12:17

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CookieEater Over a year ago

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 Over a year ago

I am not entirely familier with PHParta. I'll take a look.

CookieEater Over a year ago

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.

ken Over a year ago

Why do you think JSON encoding could be an issue? json_encode('1') === '1' and json_encode(1) === 1, nothing is lost of altered.

Nick Bedford Over a year ago

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?!

Collectives™ on Stack Overflow

PDO returns integer columns as String in PHP5.4

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related