PHP PDO bindValue() without Explicit Data Type

Question

I am working on an application using PHP object oriented with PDO. I have been struggling for hours finding the problem of why my query execution failed. It turned out that I have supplied a value as parameter in String format to be binded in an array. I have a general loop that binds all of the parameters to the question mark placeholder for my query. That of course will consider the parameters are all in String format. However I have columns in integer format. Even when I cast the variable to integer using (int) or intval(), it did not work. So I believe I have to specify the explicit data type to PDO::PDO_PARAM_INT . But I have code something like this for all queries:

Example of query:

$this->db->query("INSERT INTO users (name, numberOfCar") VALUES (?,?) ,
                  array('Jake', 5));

The bindValue() part:

$x = 1;
if( count($params) ){
    foreach($params as $param){
        $this->_query->bindValue($x, $param);
        $x++;
    }
}

How do I go with this? I mean any idea how can I modify the code to catter this issue? Not sure whether I have to modify which part of code but I am puzzled to think the workaround of this. Thank you in advance.

EDIT: PHP version 5.3.10, PDO connect to Sybase

I just throw it here without testing, but this should work: `$query = $this->db->prepare("INSERT INTO users (name, numberOfCar") VALUES (:values)")`, then `$query->execute(array('values' => array('Jake', 5)));` — Cyril N., Feb 02 '15 at 08:51
I cannot use this kind of solution as there are some columns that have a fixed value. — UserProg, Feb 04 '15 at 02:29

score 0 · Answer 1 · answered Feb 02 '15 at 08:48

I cannot reproduce the problem using php 5.6.3, mysql5.6, win32

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');  
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);

$stmt = $pdo->prepare("INSERT INTO soFoo_users (name, numberOfCar) VALUES (?,?)");
foreach( array('Jake', 5) as $i=>$v) {
    $stmt->bindValue($i+1,  $v);
}
$stmt->execute();
$stmt = null;

foreach($pdo->query('SELECT * FROM soFoo_users', PDO::FETCH_ASSOC) as $r ) {
    echo join(', ', $r), "\r\n";
}

function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo_users (
            name varchar(32),
            numberOfCar int
        )
    ');
}

prints

Jake, 5

as expected

sorry for late reply. I don't try your proposed solution as yet. But I am beginning to believe it is related to version (could it be?). Because I remember that last time I was having this problem as well on the same server. My PHP version is 5.3.10 and I am using PDO to connect to Sybase database. — UserProg, Feb 04 '15 at 02:25
Yes, could be a version problem and/or a driver problem. By default I use a toy mysql server for demos and with little effort could switch to oci or sqlserver ...but sybase? Which pdo driver do you use? DBLIB, ODBC, ...? — VolkerK, Feb 04 '15 at 08:59

score 0 · Answer 2 · answered Feb 04 '15 at 02:44

It seems that I still need to supply the explicit data type. So I come up with my own solution. Here is how I did it:

$this->db->query("INSERT INTO users (name, numberOfCar") VALUES (?,?) ,
              array('Jake', 5),
              array(PDO::PARAM_STR, PDO::PARAM_INT));

For value binding:

$x = 1;
if( count($params) ){
    foreach($params as $param){
        $this->_query->bindValue($x, $param, $datatypes[$x-1]);
        $x++;
    }
}

I hope this could help somebody out there if anybody has this kind of problem. Thank you so much to those who helped me on this.

PHP PDO bindValue() without Explicit Data Type

2 Answers2