Wildcard operator for variable in mysql_query

Question

I have a mysql_query that must have WHERE columnName = '$variable1' in it. For example:

$result = mysql_query("SELECT * FROM database WHERE columnName = '$variable1'");

I want the contents of $variable1 to retrieve any value in ColumnName; sort of like a wildcard operator. I have tried using an asterisk (*) like this:

$variable1 = '*'
$result = mysql_query("SELECT * FROM database WHERE columnName = '$variable1'");

while($row = mysql_fetch_array($result)) {
    // Echo the content of ALL ROWS IN TABLE here
}

The result of this query, when echoed, should display all the rows in the database. The asterisk does not work as a universal variable, nor does a blank space (e.g. $variable = ''). What is the wildcard operator in MySQL that would match all text combinations?

if($var == '*') $result = mysql_query("SELECT * FROM database.table"); else $result = mysql_query("SELECT * FROM database.table WHERE columnName = '$variable1'"); what about this — Saif Hamed
– Saif Hamed, Commented Nov 27, 2013 at 2:42

Tippa Raj · Accepted Answer · 2013-11-27 02:52:49Z

3

the wildcard character in SQL is %. And you need to use LIKE instead of = for wildcard matching. For example:

SELECT * 
FROM table 
WHERE ColumnHas LIKE "%something%";

This example will match any string that has "something" in the middle, such as

"This is something to match".

If you want wildcard match only at the beginning of the string, then exclude the trailing % (and vice versa).

For your case,

$variable1 = '%'
$result = mysql_query("SELECT * FROM database WHERE columnName LIKE '$variable1'");

should work.

But your use case makes me think no wildcard matching is necessary. You can also use

$result = mysql_query("SELECT * FROM database WHERE columnName NOT NULL");

provided columnName is defined to hold NULL as default value.

edited Nov 27, 2013 at 2:52

answered Nov 27, 2013 at 2:46

Tippa Raj

5944 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JSW189 Over a year ago

Is it possible to use two wildcards for two separate columns? For example: $result = mysql_query("SELECT * FROM database WHERE column1 LIKE % AND column2 LIKE %");

zsaat14 Over a year ago

@JSW189 Yes, it is perfectly acceptable to use multiple wildcards in a single query.

Tippa Raj Over a year ago

@zsaat14 is right. You can use as many LIKEs as you like in a single WHERE clause. I just want to warn you that wildcard matching with LIKE can be BAD performance wise. It may result in table sweeps.

O. Jones Over a year ago

Please be careful scaling this up. Both the LIKE '%matchstring' and the NOT NULL matching instructions definitively defeat the use of indexes for searching.

Rob M. · Accepted Answer · 2013-11-27 02:39:06Z

1

This will work:

$variable1 = "' OR 1 = 1;"

answered Nov 27, 2013 at 2:39

Rob M.

36.6k6 gold badges56 silver badges51 bronze badges

Comments

O. Jones · Accepted Answer · 2013-11-27 02:37:14Z

0

If you want to retrieve rows with any value in a particular column, you simply refrain from mentioning that column in a WHERE clause.

answered Nov 27, 2013 at 2:37

O. Jones

110k17 gold badges134 silver badges187 bronze badges

2 Comments

JSW189 Over a year ago

That would be a simple solution, but the code needs the WHERE clause there. Is there any other way to work around this? Like a wildcard operator?

Tippa Raj Over a year ago

If you absolutely must have the where clause that does nothing, you can use WHERE 1 = 1

Collectives™ on Stack Overflow

Wildcard operator for variable in mysql_query

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related