CODEWITHSUNDEEP
phppostgresqlwildcardsql-like
Dean
Dean

Reputation: 211

Using LIKE Wildcard is causing random characters

I have a php script with a query like so:

$b = $a[0];  // 2115
$c = $a[1];  // 2197
$sql = "SELECT DISTINCT a.document_id 
        FROM archive_locations a 
        INNER JOIN archive_locations b 
           ON (a.document_id = b.document_id) 
           AND 
           (
              a.street_address LIKE '%$b%' 
              OR a.location_name LIKE '%$b%' 
           )
           AND 
           (
              b.street_address LIKE '%$c%' 
              OR b.location_name LIKE '%$c%' 
           )
           ORDER BY a.document_id;";

But when I echo this query, I get this:

SELECT DISTINCT a.document_id 
FROM archive_locations a 
INNER JOIN archive_locations b 
   ON (a.document_id = b.document_id) 
   AND 
   (
      a.street_address LIKE '%!15%' 
      OR a.location_name LIKE '%!15%' 
   )
   AND 
   (
      b.street_address LIKE '!97%' 
      OR b.location_name LIKE '!97%' 
   )
   ORDER BY a.document_id;

It seems the character % is causing the issue. I tried escaping it, but there must be some special escape or pattern needed that I am unaware of. I could use some help. Thanks!

Upvotes: 0

Views: 93

Answers (1)

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

You can try like this way with curly braces e.g { and } before and after the variable name.

$sql = "SELECT DISTINCT a.document_id 
        FROM archive_locations a 
        INNER JOIN archive_locations b 
           ON (a.document_id = b.document_id) 
           AND 
           (
              a.street_address LIKE '%{$b}%' 
              OR a.location_name LIKE '%{$b}%' 
           )
           AND 
           (
              b.street_address LIKE '%{$c}%' 
              OR b.location_name LIKE '%{$c}%' 
           )
           ORDER BY a.document_id;";

When to use curly braces:

When you are defining a variable inside a string, PHP might mix up the variable with other characters if using simple syntax to define a variable and this will produce an error

See more: When to wrap curly braces around a variable

Upvotes: 1

Related Questions