Weird PDO behavior when reusing variable for bindParam

Question

I am perplexed by this unexpected behavior from pdo:

Consider this simple query i have written:

    $username = "vidhu";
    $numResults = 10;

    $db_vc = new PDO(DB_ADDRESS, DB_USER, DB_PASS);
    $stmt = $db_vc->prepare("SELECT username, email FROM users WHERE username = :username LIMIT :numResults");
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':numResults', $numResults, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

This gives me the expected output:

Array ( [0] => Array ( [username] => vidhu [email] => someone@gmail.com) )

Now here is what baffles me. When i copy and paste the query EXACTLY like so

    $username = "vidhu";
    $numResults = 10;       
    $db_vc = new PDO(DB_ADDRESS, DB_USER, DB_PASS);
    $stmt = $db_vc->prepare("SELECT username, email FROM users WHERE username = :username LIMIT :numResults");
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':numResults', $numResults, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

    echo "<br />";        

    $username = "vidhu";
    $numResults = 10;   
    $db_vc = new PDO(DB_ADDRESS, DB_USER, DB_PASS);
    $stmt = $db_vc->prepare("SELECT username, email FROM users WHERE username = :username LIMIT :numResults");
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':numResults', $numResults, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

I expect the output also to be duplicated right? so like:

Array ( [0] => Array ( [username] => vidhu [email] => someone@gmail.com ) )
Array ( [0] => Array ( [username] => vidhu [email] => someone@gmail.com) )

but it doesnt produce that output!. The seond query doesnt return anything it shows up like this:

Array ( [0] => Array ( [username] => vidhu [email] => xx.vidhuxx@gmail.com ) ) 
Array ( )

Why is this? Can someone explain?

Edit

If i remove the :numResults parameter in both the original and the copy and hardcode 10 in the query it works perfectly!

"If i remove the :numResults parameter in both the original and the copy and hardcode 10 in the query it works perfectly" => A classic case of EMULATE_PREPARES and the LIMIT clause. Lemme pull up a dupe... — Charles, Dec 15 '12 at 23:21
possible duplicate of [How do I use pdo's prepared statement for order by and limit clauses?](http://stackoverflow.com/questions/2683576/how-do-i-use-pdos-prepared-statement-for-order-by-and-limit-clauses) -- in particular, you're probably not seeing the warnings being thrown here... — Charles, Dec 15 '12 at 23:22
@Charles Assuming by removing reconnect you mean removing this line `$db_vc = new PDO(DB_ADDRESS, DB_USER, DB_PASS);` in the copied parameter. it doesn't work. Same results — Krimson, Dec 15 '12 at 23:22

score 0 · Accepted Answer · answered Dec 15 '12 at 23:39

Ok So i think i found out the problem.

between the first copy and second copy i added var_dump($numResults). When the page is loaded is shows that the variable has been converted to a string after the first copy of the query is executed. I really don't know why that happens.

Array ( [0] => Array ( [username] => vidhu [email] => someone@gmail.com ) ) 
string(2) "10" 
Array ( )

So the way i fixed it was instead of using $stmt->bindParam i used $stmt->bindValue Again, i don't know the mechanics or what is going on but it works.

It would be nice if someone could explain why this type of behavior occurs i.e Why the variable is changed from a integer to a string.

Weird PDO behavior when reusing variable for bindParam

1 Answers1