Search SQL Using LIKE instead of CONTAINS

[TMR]

Hi All,

Quick question on SQL statements.

I’m creating a simple search within an ‘articles’ MariaDB table - it looks up three columns: subject, body, description. To get this to work correctly on the selected search query I have to use % wildcards so need to use ‘LIKE’ instead of ‘CONTAINS’ but see there is no option to do this on the ‘simple’ option in the database query manager.

I have to use ‘Custom’ method to build my queries, my question is: Would it be possible to include ‘LIKE’ as an option in the ‘Simple’ Database Query…?

[sid]

Why does the Wappler use whereLike but not whereILike?

The knex library is only used in for NodeJS and the other server languages have a different implementation. I checked the docs and they don’t mention on which databases it will work. They added the feature for ILike somewhere last year. So, I checked the source code, and I actually don’t like how they implemented it. Here are my findings: Postgres all should be working fine; they just generate the ILIKE query as expected. SQLite3 will throw an error, no support there. MySQL the whereILike …

[TMR]

thanks for that @sid

[Hyperbytes]

I think:
Begins with = like mystring%
Ends with = like %mystring
Contains = like %mystring%

Never been 100% sure.

Multi keywords needs a little more logic but can be done quite easily.

[TMR]

I tried CONTAINS every way possible but could only get my query to run with a LIKE %mystring%

[Hyperbytes]

I use it all the time, can you give me a sample input string? Single word or multiple?

[TMR]

Its working fine now, this is the only way I could get it to work, all other combos I tried using CONTAINS wouldnt output correct data.

SELECT ArticleID, CompanyID, ArticleCategory, ArticleDesc, ArticleBody, ArticleSubject, ArticleAuthor, ArticleAuthorEmail, ArticleImageURL
FROM articles
WHERE CompanyID = :P1 /* {{$_GET.cid}} */ AND (ArticleDesc LIKE :P2 /* {{'%'+$_GET.filtersearch+'%'}} */ OR ArticleSubject LIKE :P3 /* {{'%'+$_GET.filtersearch+'%'}} */)
ORDER BY ArticleID DESC

[Hyperbytes]

I think your grouping may be the issue.
for a single keyword search i use this method where $_GET.filter is keyword and $_GET.companyID is the ID

As I say, multi keyword needs a bit more conditional work but easily do-able

[TMR]

Thanks for that @Hyperbytes , I’ve got something similar coming up in my next section so I’ll give it another try and re-arrange groupings.

[Hyperbytes]

for info, i have tested this under node and everything works as it should so if there is a bug it seems PHP specific

[TMR]

I’m on Node too Brain, I see a similar issue being reported here, maybe they are related: CONTAINS being used instead of LIKE in places it shouldn't be used in database manager queries

[Hyperbytes]

Maybe database type related then, i just checked using node and wappler 5.5.2 and it seems to work as it should.
I am using a node local server setup and SQLite

[Hyperbytes]

Looks like your issue is unrelated as node is not effected according to George