Search code examples
phpmysqlsql-like

Trouble with LIKE MySQL query

I have the following MySQL query that I execute from a .php page

SELECT * FROM servers WHERE name LIKE '%$value%'

which, when executed, selects 0 rows (However, the query runs successfully, so I can't use mysql_error() to debug). When I run the query in PHPMyAdmin it selects the appropriate rows. Other queries such as

SELECT * FROM servers

work fine. I can put my code up here if it will help.

Solution

  • Edit: Here's something offering an improvement based on Marek's answer below. Please see the comments regarding the practice of putting variables directly into queries and consider using prepared statements. Anyway, here it goes.

    1. PHP substitutes variables inside doubly-quoted strings, but not inside singly-quoted strings.
    2. One quote character is just treated as an ordinary character within a string delimited by the other.

    Putting that together, you can write:

    $q = "SELECT * FROM servers WHERE name LIKE '%$value%'"; //Fine

    You cannot write:

    $p = 'SELECT * FROM servers WHERE name LIKE "%$value%"'; //Broken!

    $q works because it's a doubly-quoted string, and the apostrophes are just ordinary characters. $p does not work because it's a singly-quoted string.

    As pointed out by GoodFather below, you can also say ${value} to avoid ambiguities with the ambient string, e.g. $r = "ABC${value}DEF";.