We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Beginners

SQLSTATE[HY093]: Invalid parameter number

I get error Exception: SQLSTATE[HY093]: Invalid parameter number on phalcon 

    $phql = "SELECT Matches.* FROM \Api\Model\Matches as Matches
    WHERE (Matches.user1_id = :user1_id: AND Matches.user2_id = :user2_id:) OR (Matches.user1_id = :user2_id: AND Matches.user2_id = :user1_id:)";
    $rows = $this->modelsManager->executeQuery($phql, [
        "user1_id"    => $user1_id,
        "user2_id"    => $user2_id
    ]);

but it works:

    $phql = "SELECT Matches.* FROM \Api\Model\Matches as Matches
      WHERE (Matches.user1_id = :user1_id: AND Matches.user2_id = :user2_id:) OR (Matches.user1_id = :user3_id: AND Matches.user2_id = :user4_id:)";
      $rows = $this->modelsManager->executeQuery($phql, [
            "user1_id"    => $user1_id,
            "user2_id"    => $user2_id,
            "user3_id"    => $user1_id,
            "user4_id"    => $user2_id
      ]);

Phalcon Version: 3.2.0

PHP Version: 7.1.5

Is this normal behavior or not? Thank you.

1


93.7k
Accepted
answer


79.0k
edited Nov '17

That's not a best way to tackle your query - it's error prone and it's not DRY. You'd better use IN caluse in this case.

$builder->inWhere("id", [1, 2, 3]);
public inWhere (mixed $expr, array $values, [mixed $operator])

Appends an IN condition to the current WHERE conditions

https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder