[RESOLVED] mysqli (prepare, bind_param)

[knowj]

I am trying to work out the most effective way of structuring a piece of code for a multipart search with optional criteria:

e.g. a car search:
Make
Model
Engine

Each item is optional so you can select make and engine and leave model blank or just select make or model.

A prepared statement expects a set number of parameters within the bind_param which would cause issue if for instance someone doesn't select a make as make would ='' returning false for all result which have a make selected.

what would be the most efficient way to either structure my query or php code so I don't have to write a separate query for each possible outcome or have a large collection of 'if' conditions.

Starting code:

if ($stmt = $this->db->prepare("SELECT * FROM `cars` WHERE make=?, model=?, engine=?"))
	{
		$stmt->bind_param('i', $make, $model, $engine);
		$stmt->execute();
		$stmt->bind_result($this->id, $this->name, $this->make, $this->model, $this->engine, $this->doors);
		while ($stmt->fetch())
			{
				$entry[] = $this->objectToArray();
			}
		return $entry;
	}

thanks in advance.

[NogDog]

Here's a way to do it using call_user_func_array(). I ended up writing this class to test it, as I wasn't sure if it would work -- hopefully you can adapt it to yours (and maybe clean it up a bit).

<?php
class Test
{
   private $db;
   private $search = array(
      'make'   => '',
      'model'  => '',
      'engine' => ''
   );
   private $id;
   private $make;
   private $model;
   private $engine;

   public function __construct(mysqli $db)
   {
      $this->db = $db;
   }

   public function search()
   {
      $searchParams = array();
      foreach($this->search as $key => $val)
      {
         if(!empty($val))
         $searchParams[] = "`$key` = ?";
      }
      $query = "SELECT * FROM `cars`";
      $numParams = count($searchParams);
      if($numParams)
      {
         $query .= " WHERE " . implode(' AND ', $searchParams);
      }
      $stmt = $this->db->prepare($query) or die ('Eek!');
      if($numParams)
      {
         $params = array_filter($this->search);
         array_unshift($params, str_repeat('i', $numParams));
         call_user_func_array(array($stmt, 'bind_param'), $params);
      }
      $result = array();
      if($stmt->execute())
      {
         $stmt->bind_result($this->id, $this->make, $this->model, $this->engine);
         while($stmt->fetch())
         {
            $result[] = array($this->id, $this->make, $this->model, $this->engine);
         }
      }
      return $result;
   }

   // using this to set the search criteria:
   public function __set($key, $val)
   {
      if(isset($this->search[$key]))
      {
         $this->search[$key] = $val;
         return true;
      }
      user_error("Invalid key '$key'");
      return false;
   }
}

$db = new MySQLi('localhost', 'root', '&*&*&*&*', 'test');
$test = new Test($db);
$test->make = 1;
$test->engine = 3;
$result = $test->search();
var_dump($result);
?>

[knowj]

Cheers Nogdog as always an amazing help.