When mysql WHERE clause is empty, return all rows

Question

$randomvariable=$_GET['randomvariable'];
$search="SELECT * from objects
          WHERE transactiontype='$randomvariable'
          order by id DESC";

Now if $randomvariable is empty (nothing), I would like it to return all rows. Currently if it's empty it returns nothing, because it basically searches for nothing from all of the rows.

It doesn't really matter as all of the mysql information is visible to users anyway. There is no admin-panel. — user1770565, Oct 24 '12 at 08:30

rid · Answer 1 · 2012-10-24T08:38:29.053

$randomvariable = ESACPE_MYSQL_STRING($_GET['randomvariable']);
$search =
    "SELECT * FROM objects " .
    (empty($randomvariable) ? "" : "WHERE transactiontype='$randomvariable' ") .
    "ORDER BY id DESC";

Where ESCAPE_MYSQL_STRING is the relevant function for escaping strings for whatever MySQL driver you're using.

Another, more modular way:

$search = array(
    "select" => "SELECT * FROM objects",
    "where" => "WHERE transactiontype='$randomvariable'",
    "order" => "ORDER BY id DESC"
);

if (empty($randomvariable)) {
    unset($search["where"]);
}

$search = implode(' ', $search);

The nice thing about this is that you can add, remove or alter the query for any situation easily, having easy access to any part of the query.

You could also do this with CASE() in SQL, but it's somewhat cumbersome and you shouldn't expect good performance either:

SELECT * FROM objects
WHERE transactiontype LIKE
    CASE WHEN '$randomvariable' = '' THEN
        '%'
    ELSE
        '$randomvariable'
    END CASE
ORDER BY id DESC

this is fine, but what if there are multiple conditions then there will be a problem with how to place the AND — DragonFire, Feb 15 '17 at 23:39

matthias · Answer 2 · 2012-10-24T08:36:45.670

2

Another approach:

if ($_GET['randomvariable'] != "") {
   $where = "transactiontype = " . $randomvariable;
} else {
   $where = "1";
}

$search = "SELECT * from objects WHERE " . $where . " ORDER BY id DESC";

edited Oct 24 '12 at 08:36

answered Oct 24 '12 at 08:25

matthias

2,255
1
23
28

GBD · Answer 3 · 2012-10-24T08:31:55.133

1

Try as below

$randomvariable=mysql_real_escape_string($_GET['randomvariable']);

$where = '';
if($randomvariable){
 $where .= "WHERE transactiontype='{$randomvariable}'";
}

$search="SELECT * from objects ".$where." order by id DESC";

edited Oct 24 '12 at 08:31

answered Oct 24 '12 at 08:25

GBD

15,847
2
46
50

K8sN0v1c3 · Answer 4 · 2016-05-16T18:20:50.930

There are some great answers here. I have one to add for a simple solution.

I sometimes run into this issue when I don't need a WHERE clause since none of my conditions to build my WHERE clause are met. A simple trick I like to use is something like this:

$sql_statement = "SELECT * FROM ".$table_names." WHERE 1 = 1 ";
if ($array) {
   foreach ($array as $key => $value){  
       $sql_statement .= " AND ".$key." = '".$value."' ";
   }
}

This way, you don't need any tricky logic or string manipulation because 1 always equals 1 in your WHERE clause and you can keep your looped string concats the same format. You can obvious extend this concept to do more, but for the purposes of this question, this psuedocode is just a simple way to achieve the goal.

score 1 · Answer 5 · answered Jun 03 '16 at 22:41

1

You can use the IN operator to indicate many values for the WHERE clause and include all possible values for transactiontype as the default parameter.

answered Jun 03 '16 at 22:41

Headache

239
2
8

score 0 · Answer 6 · answered Oct 24 '12 at 08:25

0

Split it in 2 queries:

$randomvariable = $_GET['randomvariable'];
if($randomvariable)
  $search="SELECT * from objects WHERE transactiontype='$randomvariable' order by id DESC";
else
  $search="SELECT * from objects order by id DESC";

answered Oct 24 '12 at 08:25

JvdBerg

21,777
8
38
55

I'm going to have to use like 100 of where clauses. This would be quite painful. – user1770565 Oct 24 '12 at 08:27
put the query with if-then in a function and call it with the sql and the var – JvdBerg Oct 24 '12 at 08:29

score 0 · Answer 7 · answered Oct 24 '12 at 08:26

If you really need to do it in SQL and not in your language, you can do this:

$search="SELECT * from objects WHERE ("" = '$randomvariable' or transactiontype='$randomvariable') order by id DESC";

This will not perform well, however, and an IF/ELSE in your language should be preferred.

score 0 · Answer 8 · answered Oct 24 '12 at 08:27

0

Add a simple if statement checking if $randomvariable is null. If it is then change the query to return all rows.

answered Oct 24 '12 at 08:27

Desert Ice

4,461
5
31
58

When mysql WHERE clause is empty, return all rows

8 Answers8

Linked