[RESOLVED] SELECT * FROM table WHERE column='$EMPTY VARIABLE'

dabdura

Hello, the most helpful forum in the world 😉

I'm trying to display filtered results from database


$var1 = "US";
$var2 = "not selected from dropdown"; // when I choose to only filter based on other
$var3 = "female";

$result = mysql_query("SELECT * FROM offers WHERE country_id='$var1' AND name='$var2' AND gender='$var3' ORDER BY ".$sortby." ".$ascdesc."") or die(mysql_error());

$var variables are selected from dropdown elsewhere, I just entered here as an example.
So the problem is if one of the variables is not selected(empty) query doesn't return the results, is there any way I can tell query to ignore an empty $var2 and filter based on other selected(defined variables) ones???

I tried setting $var='*' but it didn't work, is there any symbol that defines "any" or "all" or somthing like that

thanks in advance

bradgrafelman

dabdura wrote:
I tried setting $var='*' but it didn't work, is there any symbol that defines "any" or "all" or somthing like that

Well in MySQL there is the "LIKE" syntax, so you could do LIKE '%' (where '%' is the wildcard for any character), but it would make more sense to simply omit that term from the WHERE condition since it's meaningless.

dabdura

But, if I omit that term I won't be able to filter based on that term, when needed.
Anyways could you show me a simple example on how to omit that?

bradgrafelman

dabdura;10967481 wrote:
But, if I omit that term I won't be able to filter based on that term, when needed.

I didn't mean omit it completely; I meant omit that field whenever you don't need it. For example, you could do something like:

$sql = 'SELECT col1, col2 FROM foo';
$where = array();

if(!empty($bar))
  $where[] = "bar = '$bar'";

if(!empty($foobar))
  $where[] = "foobar = '$foobar'";

// and finally, add the [b]$where[/b] clause if it exists:
if(!empty($where))
  $sql .= implode(' AND ', $where);

dabdura

Thanks a lot for the answer it guided me in the right direction

Here's the working code just in case anybody needs it:


$where = array();

if(!empty($var1)) $where[] = "column = '$var1'";
if(!empty($var2)) $where[] = "column = '$var2'";
if(!empty($var3)) $where[] = "column = '$var3'";

if(count($where)) $filter ="WHERE ".implode(' AND ', $where);

$result = mysql_query("SELECT * FROM table ".$filter.") or die(mysql_error());