0

I have this query:

INSERT INTO user ( username , password , role )
SELECT
  CONCAT( :username1 , "-" , ABS( REPLACE( (
    SELECT username FROM user
    WHERE username LIKE :username2
    ORDER BY ID DESC LIMIT 1
  ), :username3, "" ) ) + 1 ) AS username,
  :password,
  :role

In php I have this code for binding values:

$dbp = $db->prepare( $query );
for( $i = 1 ; $i <= 3 ; $i++ ){
        $dbp->bindValue( ':username' . $i , ( $i == 2 ? 'test%' : 'test' ) );
}
$dbp->bindValue( ':password' , '0' );
$dbp->bindValue( ':role' , 'test' );
$dbp->execute();

I have in the query 5 parameters:

:username1 , :username2 , :username3 , :password , :role

and each of everyone of them are assigned with the bindValue function.

Hovewer it triggers this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]:
Invalid parameter number: parameter was not defined' in [...]
PDOStatement->bindValue(':username1', 'test') #1 {main} thrown in [...]

But the parameter do exists in my query and in my for loop. Can't I use a parameter in the CONCAT function?

This link, however, doesn't help me:

1) The :parameter name does not match the bind by mistake (typo?)

As I said, every parameter is matched: i echoed right before every bindValue, and every parameter was written correctly.

2) Completely forgetting to add the bindValue()

5 parameters, 5 bindValues , 5 echos with right matching

Third point in not for my question.

Community
  • 1
  • 1
Mc89
  • 11
  • 6
  • Have you var_dump()ed `$query` right before `prepare()`? – Álvaro González Sep 11 '15 at 15:20
  • simple test: if for whatever crazy reason mysql DIDN'T allow placeholders in a concat() call, try binding to `:username3` first, without your loop. e.g. call bind() manually, without the loop. – Marc B Sep 11 '15 at 15:23

1 Answers1

2

There are hundreds similar questions on this site already. Not a single one of them discovered something mysterious about PDO binding yet. All were caused by a simple typographic error. No exceptions.

You can use parameter in any part of query, as long as it is representing complete and single data literal.

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345