Multiple and %LIKE% statement concatenated in a OR or in a AND statement

[frabanca]

Hi! I'm using the latest versione of dibi with the latest version of xampp (PHP 7.4) installed on an updated macOS Catalina 10.15.3.

I'm trying to make a SELECT query that gets all-and-only the results matching given search criteria, but the "LIKE" modifier seems to work in an unexpected way.

My code:

      $fields = '*';
      // Assume we want all-and-only active users named "Frank"
      $filters = [
        'us_active' => 1,
        '%~like~' => [
          ['us_first_name' => 'Fra']
        ]
      ];
      $ordering = ['created_at', false];
      $limit = 20;
      $offset = 0;

      // Items query
     // NB: $this->db is a wrapper for DIBI
      $query = $this->db->query(
        'SELECT %n FROM %n WHERE %and ORDER BY %by %lmt %ofs',
        $fields,
        $this->settings->getTableName(),
        $filters,
        $ordering,
        $limit,
        $offset
      );
      $result = $query->fetchAll();
      $count = $query->getRowCount();

Expected output:

SELECT
    *
FROM
    users
WHERE
    `us_active` = 1
    AND `us_first_name` LIKE %Fra%
ORDER BY
    `created_at` DESC
LIMIT
    0, 20

Generated output:

SELECT
    *
FROM
    users
WHERE
    `us_active` = 1
    AND `` LIKE %Fra%
ORDER BY
    `created_at` DESC
LIMIT
    0, 20

As you can see the field 'us_first_name' was insered without the column name.

So, my question is: how can I generate a query with multiple and dynamic LIKE statements concatedated in a AND or in a OR statement?

Thanks for any help!

[dg]

Change

$filters = [
       'us_active' => 1,
       '%~like~' => [
          ['us_first_name' => 'Fra']
        ]
];

to

$filters = [
       'us_active' => 1,
       'us_first_name%~like~' => 'Fra',
];

[frabanca]

Hi @dg ! Thanks for your answer! I've used your code but the problem persists. Have you some other suggestions? :)

Used code

      // ... other code like in the first comment

      $filters = [
        'users.us_active' => 1,
        'users.us_first_name%' => 'Fra',
      ];

      $query = $this->db->query(
        'SELECT %n FROM %n WHERE %and ORDER BY %by %lmt %ofs',
        $fields,
        $this->settings->getTableName(),
        $filters,
        $ordering,
        $limit,
        $offset
      );
      $result = $query->fetchAll();

SQL output

SELECT * 
FROM `users` 
WHERE (`users`.`us_active` = 1) AND (`users`.`us_first_name` = 'Fra') 
ORDER BY `us_role` ASC 
LIMIT 10

I've also used filters like this but it generates an error:

      $filters = [
        'us_active' => 1,
        'us_first_name%like' => 'Fra',
      ];

[dg]

Github formatted it badly :-) It should have been 'us_first_name%~like~' => 'Fra'

[frabanca]

Oh! That was so close :) Awesome, it worked super well! Thanks for your help David!