Build an SQL query from an array of whitelisted search values

Question

I want to search data using ajax method with multiple fields search option (e.g. name, college, department, year, nationality e.t.c ). I have insert name for searching and rest of fields are empty than it went to foreach loop but this if (isset($_GET[$field]) && !empty($_GET['$field'])) condition not successful and went to else loop

  $fields = array(
  'name' => TRUE,
  'gender' => TRUE,
  'colf' => TRUE,
  'deptf' => TRUE,
  'natf' => TRUE,
  'fstatusf' => TRUE,
  'fyearf' => TRUE
  );
 foreach ($fields as $field => $like) {
  if (isset($_GET[$field]) && !empty($_GET['$field'])) {
    $value = $_GET[$field];
    $search[] = $field . ( $like ? ('LIKE "%' . $value . '%"') : ('="' . $value . '"') );
       }
 } 
  if ($search) {
  $sql = 'SELECT * FROM fmaf WHERE ' . implode(' or ' . $search);
  }

else{
$sql="SELECT * FROM fmaf";

    }

pass only those input fields which have been provided by user — Rahul
– Rahul, Commented Dec 13, 2015 at 21:22
Please please please, don't forget to validate/sanitize user input (eg. $_GET) — Joaquín O
– Joaquín O, Commented Dec 13, 2015 at 21:43
Are you sure the problem is really related to MySQL? What have you tried to resolve it? Where are you stuck? — Nico Haase
– Nico Haase, Commented Mar 13 at 12:23

Salman Karim · Accepted Answer · 2015-12-15 11:27:57Z

1

At last i have found the solution and thanks to cFreed and other who help me. My main concern is that if user want to search with one field only or more than 1 field in that case below answer is helpful for me and may be also for someone:

if (empty($_GET['name']) && empty($_GET['gender']) && empty($_GET['colf']) && empty($_GET['deptf']) && empty($_GET['natf']) && empty($_GET['fstatusf']) && empty($_GET['fyearf']))
{
    $sql="select * from fmaf ";
}
else
{
 $wheres = array();

$sql = "select * from fmaf where ";

if (isset($_GET['name']) and !empty($_GET['name']))
{
    $wheres[] = "name like '%{$_GET['name']}%' ";
} 

if (isset($_GET['gender']) and !empty($_GET['gender']))
{
    $wheres[] = "gender = '{$_GET['gender']}'";
} 

if (isset($_GET['colf']) and !empty($_GET['colf']))
{
    $wheres[] = "college = '{$_GET['colf']}' ";
} 

if (isset($_GET['deptf']) and !empty($_GET['deptf']))
{
    $wheres[] = "department = '{$_GET['deptf']}' ";
} 

if (isset($_GET['natf']) and !empty($_GET['natf']))
{
    $wheres[] = "nationality = '{$_GET['natf']}' ";
} 

if (isset($_GET['fstatusf']) and !empty($_GET['fstatusf']))
{
    $wheres[] = "finalstatus = '{$_GET['fstatusf']}' ";
}

if (isset($_GET['fyearf']) and !empty($_GET['fyearf']))
{
    $wheres[] = "fyear = '{$_GET['fyearf']}' ";
} 

foreach ( $wheres as $where ) 
{
$sql .= $where . ' AND ';   //  you may want to make this an OR
  }
 $sql=rtrim($sql, "AND "); 

     }

answered Dec 15, 2015 at 11:27

Salman Karim

2711 bronze badges

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

1 Comment

mickmackusa Mar 13 at 11:11

https://stackoverflow.com/questions/4559925/why-check-both-isset-and-empty

amallia · Accepted Answer · 2015-12-13 21:28:10Z

0

You need to build the query depending on the request. A toy example is this:

$sql = "select * from student where 1 = 1".(isset($name)?" AND name like '%$name%":"").(isset($country)?" AND country = '$country'":"").";";

answered Dec 13, 2015 at 21:28

amallia

777 bronze badges

Comments

cFreed · Accepted Answer · 2015-12-13 21:38:39Z

0

You may use a simple way, being able to face any case, like this:

// define searchable fields, with option for LIKE|EQUAL (TRUE|FALSE)
$fields = [
  'name' => TRUE,
  'country' => TRUE,
  'address' => TRUE,
  'gender' => FALSE,
  'state' => FALSE
];
foreach ($fields as $field => $like) {
  if (isset($_GET[$field]) AND !empty($_GET['$field'])) {
    $value = $_GET[$field];
    // prepare WHERE condition item, depending on LIKE option
    $search[] = $field . (
      $like ? ('LIKE "%' . $value . '%"') : ('="' . $value . '"')
    );
  }
}
if ($search) {
  $sql = 'SELECT * FROM student WHERE ' . implode(' AND ' . $search);
}

answered Dec 13, 2015 at 21:38

cFreed

4,4641 gold badge26 silver badges35 bronze badges

7 Comments

Salman Karim Over a year ago

I tried it but getting error and please see above i have updated my question

cFreed Over a year ago

@SalmanKarim. I may have a bug in the code I proposed (not tested), so please report exactly the error you get.

cFreed Over a year ago

I don't understand: your updated question is quite different from the original one. So first my proposed solution should be change accordingly. Then anyway you don't explain which error you get, nor with my code, nor with your new question...

Salman Karim Over a year ago

1st time all data have loaded but when i want to search data with selection than no data appear. I am not getting any error :(. I think have some logical issue with the if else statement. I have debug code and the values are passing in ajax jquery.

cFreed Over a year ago

Apparently your current version is based on RiggsFolly answer, not mine: so first try adapating my own proposal. On the other hand, in your current version I notice that when field search params are all empty, your query shows that status is a distinct table, while the 2nd query doesn't take care of this.

|

Collectives™ on Stack Overflow

Build an SQL query from an array of whitelisted search values

3 Answers 3

1 Comment

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related