PostgreSQL PDO binding error

Question

I found that when I try to run an update request with parameters, I get an error

inconsistent types output for parameter

Maybe because the type of the target field (the character changes), everything works fine with the type of the text column. But I do not want to change the type of the column just because of this. Then I was told that I should pass the parameters directly (using bindValue or bindParam, determining the type of each value) instead of sending an array of params to execute the method.

But when I do this, I get an error

ERROR: bind messages to sources of 0 parameters, but prepared statement "pdo_stmt_00000001" requires 1

Security Code

$Stmt = $DB->prepare("SELECT * FROM test_table WHERE test_field=:test_field"); $Stmt->bindValue(':test_field', 'test', PDO::PARAM_STR); $Stmt->execute(); var_dump($DB->errorInfo()); 

So, as far as I understand, binding doesn't work at all. Or am I doing it wrong. But maybe there is a way to solve it?

I am running PHP 5.4.12 with PostgreSQL 9.2.3, libpq 8.4.16.

Answer 1

Well, it seems that the only solution is to output all the text values ​​to the text as follows:

 update test_table set test_field = :test_field::text 

Otherwise, an error occurs about inconsistent types.

Answer 2

I do like this:

 $stmt = $DB->prepare("SELECT * FROM test_table WHERE test_field=:test_field"); $stmt->execute(array(":test_field" => 'test')); $result = $stmt->fetch(PDO::FETCH_ASSOC); if($result) { return $result['id']; // Or whatever you're trying to get. } return null; 

You just need to insert an array of parameters into the execute function, you do not need to add a special line for binding.

Tell me if this works for you (or not).