Steven
Steven

Reputation: 25304

How to apply wildcard in instr() in MySQL?

Using a wildcard so all records will be matched.

My code:

if(empty($tag))
{
$tag="%";
}

mysql_query("select * from mytable where instr(tag,'$tag')>0") or die(mysql_error());

But it returns zero result. Even if

 if(empty($tag))
    {
    $tag="*";
    }

It still returns zero result. How to resolve this problem?

Upvotes: 0

Views: 3768

Answers (2)

zombat
zombat

Reputation: 94217

INSTR does not support wildcards.

If you want to use wildcards in a MySQL query, you'll have to use a function that supports it, such as LIKE or REGEXP, ie:

mysql_query("select * from mytable where tag LIKE '%$tag%'")

The above query will work for any value of $tag. A blank value (empty string) will return all records. Be sure you properly sanitize your $tag value as well.

Upvotes: 1

wallyk
wallyk

Reputation: 57784

First of all, instr (and its counterpart locate) do not recognize wildcards or any other special expression—only literal strings. The usual way to handle this is to modify the SQL accordingly:

if (empty($tag))
     $whereclause = "";  // match all records
else $whereclause = "where instr(tag, '$tag') > 0";

mysql_query("select * from mytable $whereclause") or die(mysql_error());

Upvotes: 0

Related Questions