PHP - PDO Prepared statment, "Warning: PDOStatement::bindParam() expects at least 2 parameters"

Question

i am trying to use a prepared statement to set a placeholder value using a class object property that is passed as an argument to the __construct function. However i seam to be getting an error specifying the need for 2 parameters when i only have one parameter for the place holder value.

CODE:

<?php include ('connection.inc.php');

class Team {

    // Remember to switch back to private!
    private $database;
    public $statement = 'SELECT * FROM members ORDER BY :full_name';
    public $order;
    public $query;

    private $result;    

    public function __construct($database, $order) {
        $this->database = $database;
        $this->order = $order;
        $this->query = $this->database->prepare($this->statement);
        $this->query->bindParam(array('full_name', $this->order));
        $this->query->execute();                
    }

    public function getMember() {        
        $this->result = $this->query->fetch(PDO::FETCH_ASSOC);
        return $this->result;                        
    }
    public function id() {
        echo $this->result['id'];
    }

    public function fullName() {
        echo $this->result['full_name'];
    }
    public function alias() {
        echo $this->result['alias'];
    }
    public function abilities() {
        echo $this->result['abilities'];
    }    

};

$test = new Team($database, 'full_name');

?>

ERRORS:

Warning: PDOStatement::bindParam() expects at least 2 parameters, 1 given in

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: no parameters were bound'

Solution

Thanks to @Daerik, i changed my bindParam() statements to:

$this->query->bindParam(':full_name', $this->order));

This removed the errors.

score 1 · Accepted Answer · answered Dec 15 '16 at 18:05

1

PDOStatement::bindParam ( mixed $parameter , mixed &$variable )

$parameter: Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

$variable: Name of the PHP variable to bind to the SQL statement parameter.

You'll want to use:

$this->query->bindParam(':full_name', $this->order);

For more information read PDOStatement::bindParam.

answered Dec 15 '16 at 18:05

Daerik

4,167
2
20
33

Thanks, that removed both errors and. However when iterating of the sql query using the `gettMember()` method it seams to ignore my `ORDER BY ':full_name'` statement and used the `id` column to sort the entries. Is this a fault in my prepared statement? – Frederick M. Rogers Dec 15 '16 at 18:24
1

@FrederickM.Rogers You'll want to use a column in the `ORDER BY` statement. Assigning a string will compare them all to the same value and default to the index. Read http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html for more information. – Daerik Dec 15 '16 at 18:26
thank you will read through it and make the necessary adjustments! – Frederick M. Rogers Dec 15 '16 at 18:28
after some reading it occurs to me that the string i am passing to the placeholder is in fact the name of the column. Is this wrong? – Frederick M. Rogers Dec 15 '16 at 18:58
1

@FrederickM.Rogers That is correct. The placeholder is used for values and not columns. – Daerik Dec 15 '16 at 19:18
thanks! I did some research found that can simply store the string value of the column in question as a property of my class and build the query string using that property. Worked beautifully. Now i can pass any column name as a string value to the class construct and it does exactly what i want! – Frederick M. Rogers Dec 15 '16 at 20:25

score 1 · Answer 2 · answered Dec 15 '16 at 18:05

1

Don't use bindParam() to pass multiple parameters, just use:

$this->query->execute(array(':full_name' => $this->order));

Note: You need to include the : in the parameter name and you need to pass ':key' => $value pairs.

Or for just one, don't pass an array, bindParam() requires two arguments:

$this->query->bindParam(':full_name', $this->order);

answered Dec 15 '16 at 18:05

AbraCadaver

78,200
7
66
87

1

You don't have to include `:` when using `PDOStatement::execute`. – Daerik Dec 15 '16 at 18:09
1

@Daerik: Never tried it and all manual examples show `:parameter`, also _The keys from input_parameters must match the ones declared in the SQL._, but it may be possible. – AbraCadaver Dec 15 '16 at 18:12
1

Colons are required in the SQL statement, to indicate which identifiers are placeholders. Colons in the `execute()` calls are optional. The documentation specifies them, but the implementation is clever enough to figure out what you mean if you leave them out. – Daerik Dec 15 '16 at 18:15
2

@Daerik You wrote *"Colons in the execute() calls are optional."* - In some very rare instances, colons are required in the bind. However, in your answer, you seem to be contradicting your comment. I originally saw that question earlier and was going to suggest they include the colon but I did not since I didn't think that was the issue, since and again; is very rare that the colon needs to be used and it's even documented https://github.com/php/php-src/blob/PHP-5.3.24/ext/pdo/pdo_stmt.c#L363 as per this answer http://stackoverflow.com/a/9778890/1415724 the real problem was the use of `array`. – Funk Forty Niner Dec 15 '16 at 18:44
1

@Fred-ii- Thank you for the documentation. At the time I was answering the question, I was solely basing it via the manual, as in my experience I prefer `execute()`. It was not until later, after looking for resources to support my claim, that I came to understanding that it works for `bindParam()` as well. No point editing an answer to reflect a comment made on another answer. I wonder if the behavior is not officially supported, how come it adds the `:` when it's missing. – Daerik Dec 15 '16 at 19:24
1

@Daerik You're welcome. Well, it is rather strange that their manual doesn't make any mention of it and I agree with you. In regards as to why the colon is required in some rare cases and if it's documented, I have yet to find that out; might be "Goblins" hehe. But yeah, it is rare that the colon is required and could be based on their version of php and/or db collation, hard to say really. And no, no point in editing. – Funk Forty Niner Dec 15 '16 at 19:30
@AbraCadaver, thank you for replying to my question i ended up giving Daerik the answer but your answer as well as the comments below were educational. – Frederick M. Rogers Dec 15 '16 at 20:27

PHP - PDO Prepared statment, "Warning: PDOStatement::bindParam() expects at least 2 parameters"

2 Answers2