How to use where when using custom query in Codeigniter

Question

I am using codeigniter for my database application. I am not sure if it is possible to use get_where for custom query in codeigniter database class.

This is my query

  $this->db->query("select model, varient, (select color from mtbl_colors where mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles");

Now if i want to filter the above using where clause, I am using the query as

  function getVehicles($model='',$varient='')
 {
      if($model!='')
          {
            $q=$this->db->query("select model,varient,(select color from mtbl_colors where
            mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles 
            where model='$model'")->result();
            return $q;
           }
       elseif($varient!='')
          {
         $q=$this->db->query("select model,varient,(select color from mtbl_colors where
            mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles 
            where varient='$varient'")->result();
            return $q;
           }
    }

This is only an example, where i have to write all conditions for each where conditions. So, I may be missing some thing in codeigniter which can accomplish such multiple where conditions more easier than i am using right now.

EDIT::::

after suggestion from @Yan I tried as follows

 function getVehicles($where_clause='',$where_value='') {
    $sql = "select model,varient,(select color from mtbl_colors where
    mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles";
   $where_clause=array("varient", "model", "colorid");
   $where_value=array("MAGNA 1.4", "SANTRO", "3")

    if (!empty($where_clause) && !empty($where_value)) {
      $sql .= " where $where_clause = ?";
     return $this->db->query($sql, $where_value)->result();
    }
   return false;
  }

I received a database error saying "invalid column value Aarray"

as my intention was to implement multiple condition to generate results as per my filter options.

You can find about this in codeigniter documentation http://ellislab.com/codeigniter/user-guide/database/queries.html — machineaddict

Yan Berk Yan Berk · Accepted Answer · 2012-06-17T11:01:39

Try this:

$sql = "select model,varient,(select color from mtbl_colors where
        mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles";

$where_condition = '';
if (!empty($model)) {
    $sql .= " where model = ?";
    $where_condition = $model;   
}
elseif (!empty($varient)) {
    $sql .= " where varient = ?"; 
    $where_condition = $varient; 
}

if (!empty($where_condition)) {
   return $this->db->query($sql, $where_condition)->result();
}
return false;

Using this way escapes the input.

EDIT: A nicer solution but you need to sanitize the where_clause variable:

function getVehicles($where_clause='',$where_value='') {
   $sql = "select model,varient,(select color from mtbl_colors where
        mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles";

   if (!empty($where_clause) && !empty($where_value)) {
      $sql .= " where $where_clause = ?";
      return $this->db->query($sql, $where_value)->result();
   }
   return false;
}

EDIT 2 - Using an array:

function getVehicles($where_array) {
    $sql = "select model,varient,(select color from mtbl_colors where
        mtbl_colors.colorid=mtbl_vehicles.colorid) as color from mtbl_vehicles";

    $values = array();
    $counter = 0;
    foreach ($where_array as $key => $value) {
        if ($counter > 0) {
           $sql .= " AND ";
        }

        $sql .= " where $key = ?";
        array_push($values, $value);
        $counter ++;
    }
    return $this->db->query($sql, $values)->result();
}

How to use where when using custom query in Codeigniter

1 Answers