Search Filter Query Problem

Nookage · December 9, 2021, 7:16am

Hi. I’ve used the tutorial Filtering Database Query with a Text Input previously to create a basic search function for a dynamic table.

However this time I’m using a Paged Query and it’s not finding records when using the ‘contains’ condition.

This is how my page looks without filtering:

And after including a Paged Query:

This is the Paged Query View:

When swapping to a Custom Query, I can filter records when putting in dummy data surrounded by wildcard characters.

Here is how the page looks when I enter a term surrounded by wildcards:

Can anyone suggest a reason why I can’t get my queries working correctly?

Thanks.

Teodor · December 9, 2021, 7:18am

Is it not showing records also when you start typing?

Nookage · December 9, 2021, 7:23am

Unfortunately not when I use the Paged Query. If I swap to a Custom Query instead, then the records show when typing, but only if I include wildcard characters.

Teodor · December 9, 2021, 7:24am

Can you check for errors please:

Also the Paged Query returns a different structure than the regular query, so you will need to reapply it on the page (recreate the table), after changing this to a paged query.

Nookage · December 9, 2021, 7:53am

Thanks Teodor. The error returned shows I’ve got a problem with my table encodings.

{
"code": "HY000",
"file": "/var/www/html/dmxConnectLib/lib/db/Connection.php",
"line": 112,
"message": "SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'",
"trace": "#0 /var/www/html/dmxConnectLib/lib/db/Connection.php(112): PDO->prepare('SELECT COUNT(*)...')\n#1 /var/www/html/dmxConnectLib/modules/dbconnector.php(183): lib\\db\\Connection->execute('SELECT COUNT(*)...', Array)\n#2 /var/www/html/dmxConnectLib/lib/App.php(195): modules\\dbconnector->paged(Object(stdClass), 'getCategories')\n#3 /var/www/html/dmxConnectLib/lib/App.php(159): lib\\App->execSteps(Object(stdClass))\n#4 /var/www/html/dmxConnectLib/lib/App.php(127): lib\\App->execSteps(Array)\n#5 /var/www/html/dmxConnectLib/lib/App.php(116): lib\\App->exec(Object(stdClass), false)\n#6 /var/www/html/dmxConnectLib/lib/App.php(94): lib\\App->exec(Object(stdClass))\n#7 /var/www/html/dmxConnect/api/data/admin-categories.php(8): lib\\App->define(Object(stdClass))\n#8 {main}"

So it seems the query set up should be OK.

Nookage · December 9, 2021, 8:30am

For anyone else experiencing this problem, changing my table’s collation to utf8_general_ci fixed things. I simply applied the following query:

ALTER TABLE CI_category CONVERT TO CHARACTER SET utf8;

(Replace CI_category with your own table name.)

Thanks Teodor. I’ll debug before I ask any more questions.