SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

by

Just to provide an answer – because this error is pretty common – here are a few causes:

  1. The :parameter name does not match the bind by mistake (typo?). This is what happened here. They have :alias in the SQL statement, but bound :username. So when the param binding was attempted, Yii/PDO could not find :username in the sql statement, meaning it was “one parameter short” and threw an error.

  2. Completely forgetting to add the bindValue() for a parameter. This is easier to do in Yii other constructs like $critera, where you have an array or params ($criteria->params = array(':bind1'=>'test', ':bind2'=>'test)).

  3. another possible reason is invalid character in the placeholder name

  4. Weird conflicts with CDataProvider Pagination and/or Sorting when using together and joins. There is no specific, easy way to characterize this, but when using complex queries in CDataProviders I have had weird issues with parameters getting dropped and this error occurring.

One very helpful way to troubleshoot these issues in Yii is to enable parameter logging in your config file. Add this to your db array in your config file:

'enableParamLogging'=>true,

And make sure the CWebLogRoute route is set up in your log section. This will print out the query that gave and error, and all of the parameters it was attempting to bind. Super helpful!

More Related Contents:

  1. “Invalid parameter number: parameter was not defined” Inserting data
  2. PHP / PDO echo data from database into a table
  3. Can I mix MySQL APIs in PHP?
  4. How to apply bindValue method in LIMIT clause?
  5. PDOException “could not find driver”
  6. Row count with PDO
  7. PDO get the last ID inserted
  8. How do I use password hashing with PDO to make my code more secure? [closed]
  9. Error when preparing a multiple insert query
  10. How to view query error in PDO PHP
  11. PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
  12. Insert/update helper function using PDO
  13. Resetting array pointer in PDO results
  14. PDO multiple queries
  15. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  16. Insert multiple rows with PDO prepared statements
  17. Do SQL connections opened with PDO in PHP have to be closed
  18. PDO::PARAM for type decimal?
  19. Update query with PDO and MySQL
  20. Is “SET CHARACTER SET utf8” necessary?
  21. Alternative for mysql_num_rows using PDO
  22. Unknown database error in PHP but it exists in PHPMyAdmin
  23. Set PDO to throw exceptions by default [duplicate]
  24. PDO: MySQL server has gone away
  25. How do detect that transaction has already been started?
  26. return one value from database with mysql php pdo
  27. return multiple response data in one response
  28. How to get a query error from prepare() in PDO PHP?
  29. How can I pass an array of PDO parameters yet still specify their types?
  30. PHP PDO how to run a multiple query request?

Leave a Comment