How to get all values when parameter is null

Question

I have a query form where I need to fetch details from a custom table in MYSQL. If the parameter is left blank all records should be fetched. If there is a value entered in the parameter then records for that value should be fetched.

This is my code so far:

<?php
$host = 'localhost';
$dbname = 'test';
$username = 'test';
$password = 'xxx';
session_start();    
global $wpdb, $current_user; 
$conn = mysqli_connect($host, $username, $password, $dbname);
if (!$conn) {
    die ('Failed to connect to MySQL: ' . mysqli_connect_error());
}
$param_1=mysqli_real_escape_string($conn,$_GET['param_1']);

 if (!empty($param_1)){
  $sql = "SELECT column1 ,column2,column3,column4,column5
            FROM xxx
          WHERE column1='$param_1'";
   } else {
   $sql = 'SELECT column1 ,column2,column3,column4,column5
             FROM xxx';
}

$query = mysqli_query($conn, $sql);

if (!$query) {
    die ('SQL Error: ' . mysqli_error($conn));
}
?>

This works fine with one parameter. I will need to add more parameters and those could also be null.

For e.g.

$sql = "SELECT column1 ,column2,column3,column4,column5
            FROM xxx
          WHERE column1='$param_1' AND column2='$param_2";

Either of these could be null. How do I take care of this in MYSQL?

My question is what would be the best way to take care of this situation?

Thanks in advance.

Just inverse your if check. If it's empty or null display everything, else display with parameters. — Budimir Skrtic, Apr 23 '19 at 13:48
what is relation between `column1` and `$param_1`? is it like 1 is common between columns and variable and rest for 2,3,etc? — Rahul, Apr 23 '19 at 13:58
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Apr 23 '19 at 14:05

score 1 · Answer 1 · answered Apr 23 '19 at 13:50

You can keep appending the query like this:

$sql = "SELECT column1 ,column2,column3,column4,column5
       FROM xxx
       WHERE 1=1 ";

if(!empty($param1)){
 $sql.= "  and column1='$param1'";
}
if(!empty($param2)){
 $sql.= " and column2='$param2'";
}
if(!empty($param3)){
 $sql.= "  and column3='$param3'";
}

Note: Passing parameters like this would lead to SQL injection, use binding to pass parameters to avoid SQL Injection. Here is a good read about it.

Rahul Singh · Answer 2 · 2019-04-23T14:02:33.707

1

You can follow the below steps

<?php
$host = 'localhost';
$dbname = 'test';
$username = 'test';
$password = 'xxx';
session_start();    
global $wpdb, $current_user; 
$conn = mysqli_connect($host, $username, $password, $dbname);
if (!$conn) {
    die ('Failed to connect to MySQL: ' . mysqli_connect_error());
}

$whereArr=[];
if(isset($_GET['param_1'])){
   $whereArr[]="column1=" . mysqli_real_escape_string($conn,$_GET['param_1']);
}

if(isset($_GET['param_2'])){
   $whereArr[]="column2=" . mysqli_real_escape_string($conn,$_GET['param_2']);
}

if(isset($_GET['param_3'])){
   $whereArr[]="column3=" . mysqli_real_escape_string($conn,$_GET['param_3']);
}

$whereStr='';
if(count($whereArr)>0){
      $whereStr="WHERE " . implode(" AND ",$whereArr);
}

  $sql = "SELECT column1 ,column2,column3,column4,column5
            FROM xxx " . $whereStr;

$query = mysqli_query($conn, $sql);

if (!$query) {
    die ('SQL Error: ' . mysqli_error($conn));
}
?>

Check for each param in the above demonstrated, Put them in array.

Then check if array is isset or not, if isset create a where string and the append it to your query.

Even if no param is set your query will run without where clause.

edited Apr 23 '19 at 14:02

answered Apr 23 '19 at 14:00

Rahul Singh

918
14
31

1

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's – RiggsFolly Apr 23 '19 at 14:05
@RahulSingh in the solution provided by you, $whereArr is an array right? How do I append the second parameter to the array ? Coz now when the second parameter is getting added to the array the first parameter is not there. – David D'Lima Apr 24 '19 at 06:34
Yes it is an array, are appending value like `$whereArr[]="str"` or you can use `array_push($whereArr,'str')`. For details use the below link https://www.php.net/manual/en/function.array-push.php – Rahul Singh Apr 24 '19 at 06:41

score 0 · Answer 3 · answered Apr 23 '19 at 14:40

You can do something like this for optimization of your code,

$getArr = array_filter($_GET);
// checking sql injection
$getArr = array_map(function ($v) use ($conn) {
    return mysqli_real_escape_string($conn, $v);
}, $getArr);
$temp = [];
// fetching numbers for that key
foreach ($getArr as $key => $value) {
    $temp[$key] = preg_replace('/[^\d]/', '', $key);
}
$str = '';
// creating condition for data fetched in get
array_walk($temp, function ($item, $key) use (&$str, $getArr) {
    $str .= " column$item = '" . $getArr[$key] . "' AND ";
});
// raw query
$sql = 'SELECT column1 ,column2,column3,column4,column5 FROM xxx';
// if not empty string
if (!empty($str)) {
    $sql .= rtrim($str,'AND ');
}
echo $sql;die;

How to get all values when parameter is null

3 Answers3