php pdo search code with multiple parameters

Question

Below is my code...

I am trying to create search with multiple parameters in php pdo....with Multiple if else condition...in singlw query...

plz help ...to make this...how can i do this with php pdo...

  <?php
$db=new PDO('mysql:host=localhost;dbname=circulation_scheme_prepaid','root','');
if(isset($_POST['Submit']))
{   
$result=$db->prepare('SELECT * FROM receipt_entry WHERE');
    if($_POST['book']!='')
    {
         $result->bindParam(':book',$_POST['book']);
    }    
$result->execute(); 
$data = $result->fetchAll();
}
?>

I nned to make above code like this..

<?php
require_once("includes/config.php");
if(isset($_POST['search']))
{   
    $sql = "SELECT * FROM properties WHERE";

    if($_POST['location']!='')
    {
        $location = $_POST['location'];
        $sql .= " location = '$location' AND";
    }

    if($_POST['purpose']!='')
    {
        $purpose = $_POST['purpose'];
        $sql .= " purpose = '$purpose' AND";
    }
    $sql = substr($sql, 0 ,-3); 

$query = mysql_query($sql);
while($row = mysql_fetch_array($query,MYSQL_ASSOC))
        {
            $rows[] = $row;
        }

}
?>

I suspect what you actually want to know is whether to add _' where '_ or _' and '_ to the current sql _before_ appending the new test to the current sql string. — Ryan Vincent, Mar 11 '15 at 16:29

score 1 · Accepted Answer · answered Mar 11 '15 at 17:51

You can try something like this :

if (isset($_POST['search'])) {
  $sql = 'SELECT * FROM properties';
  $where = array();
  $params = array();

  if (!empty($_POST['location'])) {
    $where[] = "location = :location";
    $params[':location'] = $_POST['location'];
  }

  if (!empty($_POST['purpose'])) {
    $where[] = "purpose = :purpose";
    $params[':purpose'] = $_POST['purpose'];
  }

  if(count($where) > 0)
      $sql .= ' WHERE ' . implode(' AND ', $where);

  $stmt = $db->prepare($sql);

  foreach($params as $param => $value) {
    $stmt->bindParam($param, $value);
  }

  $stmt->execute();
  $data = $stmt->fetchAll();

  print_r($data);
}

Using this method, I think you'll need [`bindValue`](http://php.net/manual/en/pdostatement.bindvalue.php) [instead of](http://stackoverflow.com/questions/1179874/pdo-bindparam-versus-bindvalue) [`bindParam`](http://php.net/manual/en/pdostatement.bindparam.php). Alternatively, you could use `$stmt->bindParam(":".$param,$params[$param])` or generate the `foreach` value [by reference](http://php.net/manual/en/pdostatement.bindparam.php#Hcom98145). Incidentally, a little explanation of your solution would also be helpful. — showdev, Mar 11 '15 at 18:08

php pdo search code with multiple parameters

1 Answers1