Sending dynamic array of data to model and build query based on that? How to achieve without Active Records?

[eluser]behnampmdg3[/eluser]
Hi;

I send an array of data to model to build the query nice and easy like this:

Code:

public function results_products_prev($data, $record_start=0,$number_of_records=50)
  {
   $this->db->select('id');
   $this->db->from('products_table USE INDEX (PRIMARY)');
   $this->db->join('products_status', 'products_status.id = products_table.status');
   $this->db->order_by('products_table.id DESC');
   $this->db->where('products_table.id <', $record_start);
   $this->db->where($data);
   $this->db->limit($number_of_records);
   $query = $this->db->get();
   return $query->result_array();
  }

Now in different queries where I cant (or choose not to) use Active Records, what would be the best way to build the query dynamically based on the $data?

For exmple $data may or may not contain id or status columns in the WHERE condition.Something is telling me I might need to have multiple queries OR have logic in my model? Or can I just produce all the conditions below with 1 query?

Code:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";

Code:

$sql = "SELECT * FROM some_table WHERE status = ? AND author = ?";

Code:

$sql = "SELECT * FROM some_table WHERE id = ? AND author = ?";

Thank you forum

[eluser]TheFuzzy0ne[/eluser]
I take it $data is essentially your WHERE condition? If so, you'll need to add some logic to your model to generate the query.

Code:

public function results_products_prev($data, $record_start=0, $number_of_records=50)
{
    $where = '';
    foreach ($data as $k => $v)
    {
        if ($where)
        {
            $where .= ' AND ';
        }
        
        $where .= "{$k} = {$v}";
    }
    
    $sql = "
        SELECT * FROM some_table
        WHERE {$where}
        LIMIT {$record_start}, {$number_of_records}";
    
    return $this->db->query($sql);
}

The code above is untested, but it might work. Who knows?

Query bindings are designed for when the query is the same, but only the values change. If the field names will change, you will need to generate the query dynamically (as above).

[eluser]behnampmdg3[/eluser]
TheFuzzy0ne

I managed to do it in a good way with no logic in model, WITH pagination haaa great

Model

Code:

class Model_products extends CI_Model {

public function search_results($data, $sql,$where, $values)
{
  $select_sql = "SELECT q2.id,
      q3.code,
      q3.class,
      q3.category,
      q3.price,
      q3.production_date,
      q4.title AS STATUS
  FROM   (SELECT q1.id
    FROM   products_table AS q1
    WHERE  q1.id  ".$sql['next_prev_sign']." ".$sql['record_start']."
    ".$where."
    ORDER  BY q1.id ".$sql['order']."
    LIMIT  20) AS q2
      INNER JOIN products_table AS q3
        ON q3.id = q2.id
      JOIN products_status AS q4
     ON q4.id = q3.status ";
    
  $results = $this->db->query($select_sql, $values);
  return $results->result_array();
}

Controller

Code:

class Results extends CI_Controller {

protected $where='';
protected $search_data='';
protected $value='';
protected $sql='';

public function search_results($link=NULL,$record_start=0)
  {
   $this->load->model('model_products');
   $this->search_data = $this->session->userdata('data');
   $this->sql['record_start'] = $record_start;
  
   ##Create ORDER BY##
   if($link=='prev')
    {
     $this->sql['next_prev_sign'] = " < ";
     $this->sql['order'] = "DESC";
    }
   else
    {
     $this->sql['next_prev_sign'] = " > ";
     $this->sql['order'] = "ASC";
    }
   ##Create ORDER BY end##
  
   ##Create WHERE##
   $conditions = $this->values = array();
   if(array_key_exists("price_from", $this->search_data ))
    {
     $conditions[] = "price BETWEEN ? AND ?";
     $this->values[] =  $this->search_data['price_from'];
     $this->values[] = $this->search_data['price_to'];
     unset($this->search_data['price_from']);
     unset($this->search_data['price_to']);
    }
   foreach($this->search_data as $val=>$row)
    {
     $conditions[] = $val." = ?";
     $this->values[] = $row;
    }
   if($conditions)
    {
     $this->where = " AND " . implode(" AND ", $conditions);
    }
   ##Create WHERE end##
  
   ##Retrieve data from model
   if($link=='prev')
    {
     $data['results'] = array_reverse($this->model_products->search_results($this->search_data, $this->sql, $this->where, $this->values));
    }
   else
    {
     $data['results'] = $this->model_products->search_results($this->search_data, $this->sql, $this->where, $this->values);
    }
   ##Retrieve data from model end
  
   ##Links and pagination
   if($this->next_link($data['results'], 'search_results'))
    {
     $data['next'] = $this->next_link($data['results'], 'search_results');
    }
   else
    {
     $data['next'] = "";
    }
  
   if($record_start!=0)
    {
     $data['prev'] = $this->previous_link($data['results'], 'search_results');
    }
   else
    {
     $data['prev']='';
    }
   $this->load->view('header');
   $this->load->view('results',$data);
   $sections = array(
      'queries' => TRUE,
   'config' => FALSE);

   $this->output->set_profiler_sections($sections);
   $this->output->enable_profiler(TRUE);
  }
  
protected function next_link(&$array, $source)
  {
   $tmp=end($array);
   return "<a >Next Page >></a>";
  }
protected function previous_link(&$array, $source)
  {
   $tmp=reset($array);
   return "<a ><< Previous Page</a>";
  }
}