CODEWITHSUNDEEP
phpsqlpostgresql
Nemo
Nemo

Reputation: 513

Problems composing php sql query for postgresql table using LIKE

I am trying to retrieve data from a postgresql table therefore I have written a code. Now I am trying to compose the sql query but it isn't working. With my query I am trying to search in the table genes_searchterms and find all geneID matching where in the column types the value is equal to Pathway and where in the column searchterm the word aging is present. Bellow the code to compose my query: 

$sqlquery = "SELECT DISTINCT \"gene_id\" FROM \"" . \"genes_searchterms\" .
 "\" WHERE ". "(\"types\" LIKE  '". \"Pathway\" . "' 
AND  \"searchterm\" LIKE '" . \"%aging%\" ."') ";

Is there something wrong in the composition of my query? I use drupal and I don't get any errors when executing the code. I can't figure out where I got it wrong. I use PostgreSQL 9.2.23. In the fiddle is a part displayed of my table https://www.db-fiddle.com/f/t7ThJ7Aen9XYzv2EtepinP/0

Upvotes: 1

Views: 133

Answers (1)

finder2
finder2

Reputation: 1084

I've tried to get your Query up and running in db-fiddle. I also got no result, so I rechecked your condition and tried them separately. The error was in the "searchterm" condition.

You're using the ~* operator wich matches, according to the documentation, a Regex case insensitive. Since % isn't a pattern in Regex it matches "%aging%" literally. Only if you use LIKE in the query. % will be used as a wild card.

You could ether use a correct Regex or switch to LIKE.

Upvotes: 1

Related Questions