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. Database

QueryBuilder returns different results

Hello!

The first code results in 1 row being returned, while the second one returns the expected 2 rows. Why does Phalcon return 2 different results?

This script is supposed to return all the rows that have user_id equal to 11 or 13. Yet it returns only 1 row when binding is involved.

The difference between the 2 codes is that in the second code snippet I directly input the $user_ids variable in the where.

FIRST:

$user_ids = "11, 13";
$this->modelsManager->registerNamespaceAlias('OT', 'App\Models');
      $userSuspensions = $this->modelsManager->createBuilder()
        ->from("OT:UsersSuspensions")
        ->columns('user_id, ended')
        ->where("user_id IN (:user_ids:)", ["user_ids" => $user_ids])
        ->getQuery()
        ->execute();

SECOND:

$user_ids = "11, 13";
$this->modelsManager->registerNamespaceAlias('OT', 'App\Models');
      $userSuspensions = $this->modelsManager->createBuilder()
        ->from("OT:UsersSuspensions")
        ->columns('user_id, ended')
        ->where("user_id IN ($user_ids)", ["user_ids" => $user_ids])
        ->getQuery()
        ->execute();
1


77.7k
Accepted
answer
edited May '17

Binding parameters only a accept a single scalar variable.

Your first example is trying to fit a string ($user_ids ) into a single binded parameter.

Your second example uses the PHP variable replacement in strings, and will bind no parameters. That is why it's returning two records, because you do a simple string injection.

What you're looking for is inWhere:

$user_ids = [11, 13]; // changed
$this->modelsManager->registerNamespaceAlias('OT', 'App\Models');
      $userSuspensions = $this->modelsManager->createBuilder()
        ->from("OT:UsersSuspensions")
        ->columns('user_id, ended')
        ->inWhere("user_id", $user_ids) // changed
        ->getQuery()
        ->execute();

https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html#methods (sry, forum messes up links with underscore)



145.0k
edited May '17

Also you can use:

$user_ids = [11, 13];
->where("user_id IN ({user_ids:array})", ["user_ids" => $user_ids])