bindParam not working with prepare($sql)

[kalster]

with php pdo, the bindParam is not working. The query outputs :words and instead of the $words value. How can i get this code working?

$sql = "SELECT count(*) FROM posts WHERE MATCH (comments) AGAINST (':words' IN BOOLEAN MODE)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':words', $words);
$stmt->execute();

which code example is more safe? The code below now has $words instead of :words but is missing bindParam.

$stmt = $db->prepare(SELECT count(*) FROM posts WHERE MATCH (comments) AGAINST ('$words' IN BOOLEAN MODE)");
$stmt->execute();

$words is passed through both php $_get and $_post

[mac_gyver]

the place-holder in the sql statement should not have single-quotes around it -

$sql = "SELECT count(*) FROM posts WHERE MATCH (comments) AGAINST (:words IN BOOLEAN MODE)";

[kalster]

I already tried that but it's still not working.

[mac_gyver]

define 'not working'? what symptom or error are you getting the leads you to believe it doesn't work? 

 

also, there may be a mysql version dependency that will prevent using any bound parameter in this type of comparison in a query statement and it may even be related to using emulated prepares (which is the default, unless you have specifically turned them off.)

[kalster]

PDO::ATTR_EMULATE_PREPARES, i tried 'true'  and still the same error.

SQLSTATE[HY093]: Invalid parameter number

$sql = "SELECT count(*) FROM posts WHERE MATCH (comments) AGAINST (:words IN BOOLEAN MODE)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':words', $words);
$stmt->execute();

Edited January 3, 2015 by kalster

[mac_gyver]

i tried your query/code on my development system and it doesn't throw that error. are you sure the error isn't coming from some other query? if you are using exceptions and are not echoing the $sql statement and/or the line and file where the exception was thrown at, it could be from a completely different query statement.

 

the error is typically when you have a different number of place-holders in the query than what you have bound.

 

also, there's a slight chance that the actual value in $words could be causing such an error. what value are you testing with in $words?

[kalster]

the variable $words could contain any one word and without numbers in it. Also, the full query has more than one :words in it. the query with many $words in it gives no errors

Edited January 3, 2015 by kalster

[mac_gyver]

Also, the full query has more than one :words in it.

 

 

a) you cannot do that. each place-holder can only appear once in a query, with a bind statement for each. you would need to use something like :words1, :words2 but bind the same variable to each.

 

b) when you don't post the actual code/query that is causing a problem, you just waste everyone's time.

[kalster]

thank you. and also, at first i thought that was the actual code. I made a mistake and copied the wrong $sql. i have four $sql, each going to the same pdo code and based on a condition.

[mac_gyver]

that would be why your error handling should tell you everything possible about the error -

 

if you are using exceptions and are not echoing the $sql statement and/or the line and file where the exception was thrown at, it could be from a completely different query statement.