PHP mysql - ...AND column='anything'... ?

Question

Is there any way to check if a column is "anything"? The reason is that i have a searchfunction that get's an ID from the URL, and then it passes it through the sql algorithm and shows the result. But if that URL "function" (?) isn't filled in, it just searches for:

...AND column=''...

and that doesn't return any results at all. I've tried using a "%", but that doesn't do anything.

Any ideas?

Here's the query:

mysql_query("SELECT * FROM filer 
             WHERE real_name LIKE '%$searchString%' 
                   AND public='1' AND ikon='$tab' 
                   OR filinfo LIKE '%$searchString%' 
                   AND public='1' 
                   AND ikon='$tab' 
             ORDER BY rank DESC, kommentarer DESC");

The problem is "ikon=''"...

Answer 1

Try using this:

AND ('$tab' = '' OR ikon = '$tab')

If the empty string is given then the condition will always succeed.

Alternatively, from PHP you could build two different queries depending on whether $id is empty or not.

Answer 2

Run your query if search string is provided by wrapping it in if-else condition:

$id = (int) $_GET['id'];

if ($id)
{
  // run query
}
else
{
  // echo oops
}

Answer 3

You can dynamically create your query, e.g.:

$query = "SELECT * FROM table WHERE foo='bar'";

if(isset($_GET['id'])) {
    $query .= " AND column='" . mysql_real_escape_string($_GET['id']) . "'";
}

Update: Updated code to be closer to the OP's question.

Answer 4

There is noway to check if a column is "anything"
The way to include all values into query result is exclude this field from the query.
But you can always build a query dynamically.
Just a small example:

$w=array();
if (!empty($_GET['rooms'])) $w[]="rooms='".mysql_real_escape_string($_GET['rooms'])."'";
if (!empty($_GET['space'])) $w[]="space='".mysql_real_escape_string($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mysql_real_escape_string($_GET['max_price'])."'";


if (count($w)) $where="WHERE ".implode(' AND ',$w); else $where='';
$query="select * from table $where";

For your query it's very easy:

$ikon="";
if ($id) $ikon = "AND ikon='$tab'";
mysql_query("SELECT * FROM filer 
             WHERE (real_name LIKE '%$searchString%' 
                   OR filinfo LIKE '%$searchString%')
                   AND public='1' 
                   $ikon 
             ORDER BY rank DESC, kommentarer DESC");

I hope you have all your strings already escaped

Answer 5

and ikon like '%' would check for the column containing "anything". Note that like can also be used for comparing to literal strings with no wildcards, so, if you change that portion of SQL to use like then you could pre-set the variable to '%' and be all set.

However, as someone else mentioned below, beware of SQL injection attacks. I always strongly suggest that people use mysqli and prepared queries instead of relying on mysql_real_escape_string().