Trailing forward slashes in LIKE query.

[klance]

This one's got me stumped, big time. I am using PHP to query a PostgreSQL database.

I have a table that contains UNIX directory paths in a varchar column. When I run the following query:

SELECT path FROM my_table WHERE path LIKE 'root/subdir';

I get the following result (assume that a matching record exists):

root/subdir

Okay, now that I know there's a matching record, I try this:

SELECT path FROM my_table WHERE path LIKE 'root/%';

...and the query returns NOTHING!

If I execute the exact same query in pgsql, I get the following result:

path

root/subdir
(1 row)

PostgreSQL seems to be able to understand a forward slash whether it appears in the middle or at the end of a LIKE query. PHP, on the other hand, handles a forward slash okay in the middle (between two literal strings), but doesn't seem to behave properly when the forward slash precedes the % (wildcard).

Can anybody help?
Thanks!

[Sxooter]

Try printing the query to the web browser and see if the % or / is missing. It could be that it is getting interpreted by PHP. I that case, you might be able to build it like this:

$query = 'select * from table where field='."'".'text%/'."'";