Search Form With One or More (Multiple) Parameters

Codeigniter - Search form with multiple parameters

How about just skipping any parameters that weren't provided?

function query($no, $first)
{
  $where = array();

  if ($no != '') $where['no'] = $no;
  if ($first != '') $where['first'] = $first;

  if (empty($where))
  {
    return array(); // ... or NULL
  }
  else
  {
    return $query = $this->db->get_where('table', $where);
  }
}

PDO database search with multiple parameters

You're not using parameters in your query, you're using string concatenation. Worse, you're concatenating variables that don't exist yet, so PHP is substituting an empty string. In essence, the query you're building is:

SELECT *
FROM medisGO_patient
WHERE lastName LIKE '%%'
AND firstName LIKE '%%'
AND birthday LIKE '%%'
AND course LIKE '%%'
AND id LIKE '%%'

That's why you're getting the entire table.

You should be using named parameters instead, while you should add the % signs to the values:

$sql = "SELECT *
        FROM medisGO_patient
        WHERE lastName LIKE :lastName
        AND firstName LIKE :firstName
        AND birthday LIKE :birthday
        AND course LIKE :course
        AND id LIKE :id";

$lastName = '%' . trim($_POST['lastName']) . '%';
$firstName = '%' . trim($_POST['firstName']) . '%';
$course = '%' . trim($_POST['course']) . '%';
$birthday = '%' . trim($_POST['birthday']) . '%';
$no = '%' . trim($_POST['no']) . '%';

$statement = $connection->prepare($sql);
$statement->bindParam(':lastName', $lastName, PDO::PARAM_STR);
$statement->bindParam(':firstName', $firstName, PDO::PARAM_STR);
$statement->bindParam(':birthday', $birthday, PDO::PARAM_STR);
$statement->bindParam(':course', $course, PDO::PARAM_STR);
$statement->bindParam(':id', $no, PDO::PARAM_STR);
$statement->execute();

PHP search many inputs

Something like this

 $sql = 'SELECT * FROM sometable';
 $where = [];
 $params = [];

 if($a){
    $where[] = 'a = :a';
    $params[':a'] = $a;
 }

 if($b){
    $where[] = 'b = :b';
    $params[':b'] = $b;
 }


 if(!empty($where)){
    $sql .= ' WHERE '.implode(' AND ', $where);
 }

$stmt = $PDO->prepare($sql);
$res = $stmt->execute($params);

And so On.

It almost always preferable to use and array and implode for things like this instead of concatenation. Often concatenation will leave you with a hanging "separator" in this case " AND ". For example if we tried this with concatenation:

//if we put WHERE here and then nothing passes our conditions we wind up with:
//"SELECT * FROM sometable WHERE" which wont work
$sql = 'SELECT * FROM sometable ';

//we still need something like an array if we want to prepare our query.
//which is something we should always do
$params = []; 

 if($a){
    //if we put WHERE here, then what if this condition doesn't pass
    //do we put it in the next condition? How do we tell.     .
    $sql .= 'WHERE a = :a AND ';
    $params[':a'] = $a;
 }

 if($b){
     //again if the first condition didn't pass how do we know to put "WHERE" here.
    //"SELECT * FROM sometable b = :b AND" which wont work
    $sql .= 'b = :b AND ';
    $params[':b'] = $b;
 }

 if($c){
    //lets say the first 2 conditions passes but this last one failed
    //"SELECT * FROM sometable WHERE a = :a AND b = :b AND" which wont work
    $sql .= 'c = :c';
    $params[':c'] = $c;
 }

 //we would need to do something like this to trim the last "AND" off
 $sql = preg_replace('/\sAND\s$/', '', $sql);

 //--------------------
 //now if we were prepending "AND" instead of appending it, we're no better off.
 //--------------------
  //we can fix the where issue by using a string variable (and testing it latter)
  $where = '';

  if($a){
    $where .= 'a = :a';
    $params[':a'] = $a;
  }

  if($b){
    //However lets say the first condition failed, we get this:
    //"SELECT * FROM sometable WHERE AND b = :b" which wont work
    $where .= ' AND b = :b';
    $params[':b'] = $b;
    //--------------------------
    //so in every condition following we would have to test $where
    //and if its not empty then we can prepend "AND"
    if(!empty($where)) $where .= ' AND ';
    $where .= 'b = :b';
    $params[':b'] = $b;
 }

 if($c){
    if(!empty($where)) $where .= ' AND ';
    $where .= 'c = :c';
    $params[':c'] = $c;
  }


 //finally to fix the "WHERE" issue we need to do something like this:  
  if(empty($where)) $sql .= ' WHERE '.$where;

 //we could also try something like this in every condition:
  if($d){
    if(empty($where)) $where .= ' WHERE ';
    //However, this breaks our fix for prepending "AND", because 
    //$where will never be empty when we test it.
    //if(!empty($where)) $where .= ' AND ';
    $where .= 'd = :d';
    $params[':d'] = $d;
  }

Hopefully that all makes sense. It's just so much easier to use an array and implode it later.

I just wanted to show that to help visualize the issues with concatenation. We wind writing more code, using the same number of variables and double the conditional logic. Or we can get into complicated things like Regex to trim the hanging AND off etc.

Hope that helps!

BECAUSE I mentioned it in the comments.

If you are using "OR" you can of course do the same thing with that, but typically "OR" will cause a full scan of the DB. It's just the way OR works. When we use "AND" the DB (basically) takes the return set and applies the next condition to that, because both have to pass. However, with "OR" rows that failed the first condition could still pass if the second condition passes. So the DB must scan the full record set for each or, as well as keep track of all the rows that passed in the previous conditions. It's just the way the logic works for "OR".

Now for improved "OR" performance we can use a sub-query that is a union. Like this:

$sql = 'SELECT * FROM sometable AS t';
$union = [];
$params = [];


if($a){
   $union[] = 'SELECT id FROM sometable WHERE a = a:';
   $params[':a'] = $a;
}

if($b){
  $union[] = 'SELECT id FROM sometable WHERE b = b:';
  $params[':b'] = $b;
}

if(!empty($union)){
   $sql .= '
   JOIN( '.
        implode(' UNION ', $union).
   ' ) AS u ON t.id = u.id
}

What we wind up with is something like this query:

SELECT
    *
FROM
   sometable AS t
JOIN (
     SELECT id FROM sometable WHERE a = a:
  UNION
     SELECT id FROM sometable WHERE b = b:
) AS u ON t.id = u.id

When we use "OR" as our dataset grows the DB must store these results in temp table as well as search the entire dataset. Because we are pulling all the columns in the table, this dataset will quickly grow. Once it hits a certian size it will get swapped to Disc and our performance will take a big hit for that.

With the Union query, we also create a temp table. But because we are only concerned with pulling out the ids this temp table will be very small. Union unlike Union ALL will also automatically remove duplicate records further reducing our dataset. So we want to use Union and not Union ALL.

Then we join this back on the table in the outer query and use that to pull the all the columns from just the rows that we need.

Basically we are accepting the fact that we need a temp table and minimizing the impact of that.

This might not seem like it would be much faster, and in some cases it might not be (when no swapping happens). But for me, using a query like you describe where users can search on multiple fields, I was able to reduce the time it took from about 15 seconds to under 1 second. My query had several joins in it such as if a user put in a state, I had to join on participant then participants_addresses (junction table) and then addresses and then finally on states. But if they put in a phone I had to join participant > participants_phones > phone etc.

I can't guarantee this will work in every case and you should use Explain and SQL_NO_CACHE when benchmarking your queries. For example EXPLAIN SELECT SQL_NO_CACHE * FROM .... Explain will tell you how the indexes are working and No Cache prevents the DB from caching the query if you run it multiple times. Caching will make it look like it is fast when it's really not.

You can do something similar when sorting, which also kills performance.

 SELECT
    *
 FROM
    sometable AS t
 JOIN (
        SELECT id FROM sometable WHERE a = a: ORDER BY date DESC
 ) AS u ON t.id = u.id

This has a similar effect of only sorting the id's in the temp table (instead of the whole dataset), and then when we join it, it actually keeps the order the ids are in. I forget if the order of the subquery vs the outer query matter.

For fun you can even combine the two with 2 nested sub-queries, with the Union as the deepest query (it's something like this).

 SELECT
    *
 FROM
    sometable AS t
 JOIN (
        SELECT id FROM sometable AS t0 JOIN (
             SELECT id FROM sometable WHERE a = a:
           UNION
             SELECT id FROM sometable WHERE b = b:
        ) AS u ON t0.id = u.id
        ORDER BY t0.date DESC
 ) AS t1 ON t.id = t1.id

It can get pretty complicated though ... lol.

Anyway, I was bored and maybe, just maybe, it will work for someone like it did for me. (this is what happens when I don't get sleep) :)

UPDATE

IF you have problems with the parameters you can output the SQL with the values filled in by doing this:

   echo str_replace(array_keys($params), $params, $sql)."\n";

But use this only for Debugging, not for putting the data into the query because that would defeat the purpose of using prepared statements and open you up to SQLInjection attacks. That said, it can make it easier to see if you are missing anything or have any spelling errors. I also use this when I just want to test the query in PHPMyAdmin, but am to lazy to cut an paste the data into it. Then I just copy the output put it in PHPMyAdmin and then I can rule out any issues with PHP or tweak the query if need be.

You can also have issues if you have to many elements in the array, AKA extra placeholders that are not in the query.

For that you can do

  //count the number of : in the query
  $num_placeholders = substr_count(':', $sql);
  //count the elements in the array
  $num_params = count($params);

  if($num_placeholders > $num_params ) echo "to many placeholders\n";

  else if($num_placeholders < $num_params ) echo "to many params\n";

One last thing to be mindful of when mixing "AND" and "OR" is stuff like this

 SELECT * FROM foo WHERE arg1 = :arg1 OR arg2 = :arg2  AND arg3 = :arg3

The way it executes this is like this

SELECT * FROM foo WHERE arg1 = :arg1 OR (arg2 = :arg2  AND arg3 = :arg3)

This will return all rows that match arg1 regardless of the rest of the query.
Most of the time this would not be what you want. You would actually want it to do it this way:

SELECT * FROM foo WHERE (arg1 = :arg1 OR arg2 = :arg2)  AND arg3 = :arg3

Which is called an "Exclusive OR". This will return all rows that match arg1 OR arg2 AND arg3

Hope that helps.

Ajax search for multiple parameters usng a single input box

Before you even look at the code below, a few things to note:

• This looks horribly subject to SQL injection. Without being able to see your PHP code and what you to do $q before inserting into this query...all I have to say is: be careful. If you're not validating the input of $q, all I would have to do is type: "'; DROP TABLE localities;" note this technically wouldn't work because mysql_query only supports single commands, but the risk is still there
• This query type will not allow you to use indexes and is likely to perform very slow. Consider removing the % before the $q, to allow MySQL to use indexes, or switch to a full-text engine with partial word indexing capabilities. If this table gets large enough this could become quite slow

However, this is the correct way:

(
    SELECT
      'Locality' AS `type`,
       `locality_name` AS `name`
    FROM
       `locality`
    WHERE
       `locality_name` LIKE '%$q%'
)
UNION ALL
(
    SELECT
       'City' AS `type`,
       `city_name` AS `name`
    FROM
       `cities`
    WHERE
       `city_name` LIKE '%$q%'
)

How to capture data from multiple form search fields in one variable

If you give multiple inputs the same name, $_POST['find'] will just contain the input from one of them (the last one, I think). You need to either give them different names, or you can end the name with []. When you do the latter, PHP will put all the inputs into an array.

<form name="search" method="post" action="http://example.com/search/">
Seach for: <input type="text" name="find[]" /> <input type="submit" name="search" value="Search" />
Search for movies by Type
<select name="find[]">
    <option value="Sci-Fi" selected>Sci-Fi</option>
    <option value="Comedy">Comedy</option>
    <option value="Drama">Drama</option>
</select>
</form>

With this, you can do:

$input = implode(' ', array_filter($_POST['find']));

array_filter will remove any empty values, then the rest will be concatenated together with space between them.

PHP search with multiple fields

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 "); 

     }

---

Related Topics