PHP & MYSQL: How can i neglect empty variables from select

Question

if i have 4 variables and i want to select DISTINCT values form data base

<?php
$var1 = ""; //this variable can be blank
$var2 = ""; //this variable can be blank
$var3 = ""; //this variable can be blank
$var4 = ""; //this variable can be blank

$result = mysql_query("SELECT DISTINCT title,description FROM table WHERE **keywords ='$var1' OR author='$var2' OR date='$var3' OR forums='$var4'** ");

?>

note: some or all variables ($var1,$var2,$var3,$var4) can be empty

what i want: i want to neglect empty fields

lets say that $var1 (keywords) is empty it will select all empty fileds, but i want if $var1 is empty the result will be like

$result = mysql_query("SELECT DISTINCT title,description FROM table WHERE author='$var2' OR date='$var3' OR forums='$var4' ");

if $var2 is empty the result will be like

$result = mysql_query("SELECT DISTINCT title,description FROM table WHERE keywords ='$var1' OR date='$var3' OR forums='$var4' ");

if $var1 and $var2 are empty the result will be like

$result = mysql_query("SELECT DISTINCT title,description FROM table WHERE date='$var3' OR forums='$var4' ");

and so on

Take a look at Gail Shaw's blog post on catch-all queries.

Joe Stefanelli
– Joe Stefanelli

2011-03-04 22:48:33 +00:00
Commented Mar 4, 2011 at 22:48 — Joe Stefanelli
– Joe Stefanelli, Commented Mar 4, 2011 at 22:48

Dimitry · Accepted Answer · 2011-03-04 23:21:44Z

4

Try this.


$vars = array(
  'keywords' => '', // instead of var1
  'author' => '',  // instead of var2
  'date' => '', // instead of var3
  'forums' => '', // instead of var4
);

$where = array();
foreach ($vars as $varname => $varvalue) {
  if (trim($varvalue) != '') $where[] = "`$varname` = '" . mysql_real_escape_string($varvalue) . "'";
}
$result = mysql_query("SELECT DISTINCT title, description FROM table WHERE " . join(" OR ", $where));

edited Mar 4, 2011 at 23:21

answered Mar 4, 2011 at 22:49

Dimitry

6,6332 gold badges24 silver badges21 bronze badges

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

2 Comments

Artefact2 Over a year ago

Sorry, no +1 because the $$ syntax is horrible and is a result of poorly chosen designs.

cash-cash Over a year ago

thanks Dimitry Z .. but you forgot [)] at the end in latest line i tried it, but it crashed my localhost twice

cash-cash · Accepted Answer · 2011-03-07 15:23:06Z

Thanks alot every one specially experimentX .. Your answer helped me to get the right function i Just replaced (isset) with (!empty) .. Then every thing will be more than OK

$vars = array(
        (!empty($_GET["var1"]))? " keyword = '". $_GET["var1"] ."' ": null, 
        (!empty($_GET["var2"]))? " author  = '". $_GET["var2"] ."' ": null,
        (!empty($_GET["var3"]))? " date    = '". $_GET["var3"] ."' ": null,
        (!empty($_GET["var4"]))? " forums  = '". $_GET["var4"] ."' ": null
    );


function myfilterarray($var)
{
    return !empty($var)?$var: null;
}

$newvars = array_filter($vars, 'myfilterarray');

$where = join(" OR ", $newvars);

$sql = "SELECT DISTINCT title, description FROM table ".(($where)?"WHERE ".$where: null);

echo $sql;

with this function if there is empty variable it will be neglected

Thanks again every one for your helpful suggestion

Matt · Accepted Answer · 2011-03-04 22:52:31Z

0

make your select statement string before you call mysql_query(...) so do something along the lines of this:

$queryString = "Select DISTINCT title, description FROM table WHERE";
if(!empty($var1))
   $queryString .= " keywords = $var1";

and so forth for all of your variables. you could also implement a for loop and loop through your $var1 - $var# and check for !empty($var#)

answered Mar 4, 2011 at 22:52

Matt

1,2651 gold badge16 silver badges24 bronze badges

1 Comment

cash-cash Over a year ago

i think there is an easy way, the problem will be with (OR) and if i have many variables this will not be effective

Jerome · Accepted Answer · 2011-03-04 22:52:54Z

0

Why do you not simply build a if else structure? Like

if ($var1!="" && $var2!="" && $var3!="" && $var4!=""){
   $result = mysql_query("SELECT DISTINCT title,description FROM table WHERE keywords ='$var1' OR author='$var2' OR date='$var3' OR forums='$var4' ")
} else if ($var2!="" && $var3!="" && $var4!=""){
   $result = mysql_query("SELECT DISTINCT title,description FROM table WHERE author='$var2' OR date='$var3' OR forums='$var4' ");
} else if {
...
}

answered Mar 4, 2011 at 22:52

Jerome

2751 gold badge2 silver badges7 bronze badges

4 Comments

NullUserException Over a year ago

So basically you are duplicating tons of code, and there are 2^4 = 16 possible combinations. If there were 5 variables, there would be 32 combinations, and so on.

Jerome Over a year ago

yeah I just realised that one would need to check for all the possible cases.

cash-cash Over a year ago

i was using this way and i found that will be an effective way, i'll need to write mush cases lets take example 1 - when $var1 empty and other not empty 2 - when $var2 empty and other not empty 3 - when $var3 empty and other not empty 4 - when $var4 empty and other not empty 5 - when $var1 and $var2 are empty and others not empty 6 - when $var1 and $var3 are empty and others not empty etc.

cash-cash Over a year ago

i mean (it will not be an effective way)

Philoxopher · Accepted Answer · 2011-03-05 08:11:33Z

0

(I just posted the below in his duplicate post, so I'm re-posting the below here)

Forgive me if anything is wrong, it's very late here and I just typed this in notepad on Windows, without an environment to test on. * Use with caution * :)

$vars = array(
'blah1' => '',
'blah2' => '',
'blah3' => '',
);

 $sql_statement = "SELECT first, last FROM names WHERE";

 $clause = "";
 foreach($vars as $k=$v)
{
$k = trim($k);
if(!empty($k))
{
    $clause .= " `$k` = '$v' OR";
}
}
$clause = rtrim($clause, "OR");

// $clause should have what you want.

answered Mar 5, 2011 at 8:11

Philoxopher

1,6743 gold badges15 silver badges17 bronze badges

Comments

S L · Accepted Answer · 2011-03-05 08:33:22Z

Well, there are manu ways of doing this but the shortest way I have found is creating an array of the following form

$vars = array(
        (isset($_GET["var1"]))? " keyword = '". $_GET["var1"] ."' ": null, 
        (isset($_GET["var2"]))? " author  = '". $_GET["var2"] ."' ": null,
        (isset($_GET["var3"]))? " date    = '". $_GET["var3"] ."' ": null,
        (isset($_GET["var4"]))? " forums  = '". $_GET["var4"] ."' ": null
    );

function myfilterarray($var)
{
    return !empty($var)?$var: null;
}

$newvars = array_filter($vars, 'myfilterarray');

$where = join(" OR ", $newvars);

$sql = "SELECT DISTINCT title, description FROM table ".(($where)?"WHERE ".$where: null);

echo $sql;

Your result for http://localhost/?var1=sadfsadf&var2=sadfasdf&var3=asdfasdf

 SELECT DISTINCT title, description FROM table WHERE keyword = 
 'sadfsadf' OR author = 'sadfasdf' OR date = 'asdfasdf'

Your result for http://localhost/?

SELECT DISTINCT title, description FROM table

Collectives™ on Stack Overflow

PHP & MYSQL: How can i neglect empty variables from select

6 Answers 6

2 Comments

Comments

1 Comment

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related