1

I have a PDO prepared statement in which the bound variables are prepared dynamically (they can vary from call to call) in an advanced search function on our site.

I know the actual SQL call is correct but for some reason I am getting the following error when trying to pass my string variable into the prepared statement:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I have had this error before and am very familiar with the normal resolution steps. However, my circumstances are quite strange. With the following sample code:

$columns = "FirstName, LastName, ID, City, State";
$sWhere = "WHERE (FirstName LIKE ? AND LastName
    LIKE ? AND ID LIKE ? AND City
    LIKE ? AND State LIKE ?)";
$sVal = "'tom', 'lastname', '12345', 'Diego', 'CA'";

$sql = "SELECT ".$columns." FROM table ".$sWhere;
$stmt = $db->prepare($sql);

$stmt->execute(array($sVal));

where $sVal can range from 'firstname', 'lastname'.... to over 12 variables. Changing the number of variables has the same result. The complete statement is:

SELECT FirstName, LastName, ID, City, State 
  FROM table
  WHERE (FirstName LIKE ? AND LastName
    LIKE ? AND ID LIKE ? AND City
    LIKE ? AND State LIKE ?)

When I run my query as is, the error above is returned. When I thought I did in fact have an incorrect number of variables, I ran an ECHO on my $value statement and found they did match.

As a secondary test, I took the output from the echo of $value and plugged directly back into the execute array:

$stmt->execute(array('tom', 'lastname', '12345', 'Diego', 'CA'));

This works with any issue at all.

It does not affect my question but I also placed % symbols within my $sVal variable for correctness:

$sVal="'%tom%', '%lastname%', '%12345%', '%Diego%', '%CA%'";

It makes ZERO sense to me that the echo'd output of the SAME variable would work but the variable itself would not. Any ideas?

Improve this question
1
  • 1
    As it stands, there's not enough information to answer your question. Can you provide the query and the contents of $value? Commented Dec 7, 2010 at 20:16

2 Answers 2

Reset to default
3

Your $sVal is not an array, it's just a simple string, so when you write array($sVal), the execute() sees only one value. You need to explode() your $sVal string to become an array:

// clean up the unnecessary single quotes and spaces
$value = str_replace(array("'", ", "), array("", ","), $value);
// make the array of the values
$value = explode(',', $value);
$stmt->execute($value);
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

@JM4 No, you need the array to be the proper number of elements in it, not only a concatenated string element inside.
styu - tell me what the difference is in these two statements: $foo->execute(array('1','2','3','4')); and $foo->execute($bar); where $bar = array('1','2','3','4');
Your $bar is not an array (if it is, then you can not echo it (because echo only outputs Array as text when you try it with an array), it is a string, so it means $foo->execute(array("'1','2','3','4'")); as a string in it, and what we try to tell you with netcoder is $foo->execute(array('1','2','3','4')); with elements of the array.
@JM4 Maybe because you are inputting integer values as strings. I think PDO needs integer values for integer columns.
however - your last comment does highlight the probable issue which is the double quotes within the array element. I will toy with your initial code as the double quoted string makes more sense in theory but netcoder's suggestion below ignores the basis of the question which is unknown variables
|
1

The problem is that execute accepts an array of parameters, with each parameter having its own key. Passing a SQL-like, comma-separated string will not work, and even if it did, it would render PDO useless.

This is wrong: 

$sVal = "'tom', 'lastname', '12345', 'Diego', 'CA'";

This is how it is supposed to be done:

$sVal = array('tom', 'lastname', '12345', 'Diego', 'CA');

Per example, if you are receiving data from a form in POST, it would be:

$sVal = array(
    $_POST['firstname'],
    $_POST['lastname'],
    $_POST['zipcode'],
    $_POST['city'],
    $_POST['state'],
);
$stmt->execute($sVal);
Improve this answer

5 Comments

it is not truly an array, i will fix in an edit, it is a statement created with commas and apostrophes as necessary
an echo $value would show array also if it were an array. It is a string
You are wrong. You don't seem to understand at all what PDO does and how it works. Also, your comment makes no sense... $sVal = array() being treated as a string?? As for the POST variables, this is an example. I assume they exists and that you validated them beforehand. I am not going to write the whole script for you. I'm answering your question. If you know PDO so well, why are you asking?
netcoder - the variables do exist but there are other post variables being used for other things. Similarly, and so as to not take up 490 lines of code, there are other things going on before hand which I cannot post for security purposes. I very much understand HOW pdo works/what it does. IF the answer is that passing a string into an array function will throw an error in PHP then that has NOTHING to do with PDO.
@netcoder - I understand your answer now but as I mentioned in my last comment, this issue is really not about PDO rather than myself not realizing array($variable) is counted as a one element array rather than multiple. Thank you for your answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.