#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171

    How to build a query structure based on the array of data. Logic in model?


    Hi;

    I send an array of data to model to build the query nice and easy like this:
    PHP Code:
    public function results_products_prev($data$record_start=0,$number_of_records=50)
      {
       
    $this->db->select('id');
       
    $this->db->from('products_table');
       
    $this->db->join('products_status''products_status.id = products_table.status');
       
    $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 which I dont like either.

    Or can I just produce all the conditions below with 1 query and no logic in model?

    PHP Code:
    $sql "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"
    $sql "SELECT * FROM some_table WHERE status = ? AND author = ?"
    $sql "SELECT * FROM some_table WHERE id = ? AND author = ?"
    Thank you forum
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,992
    Rep Power
    9397
    The best method I know for building queries by hand goes like
    PHP Code:
    $fields $conditions $bind = array();

    $fields[] = "*";

    $conditions[] = "id = ?";
    $bind[] = $id// if you need references, $bind[] =& $id;
    $conditions[] = "status = ?";
    $bind[] = $status;
    $conditions[] = "author = ?";
    $bind[] = $author;

    $query "SELECT " implode(", "$fields) . " FROM some_table";
    if (
    $conditions) {
        
    $query .= " WHERE " implode(" AND "$conditions);

  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Thanks Requinix;

    I used parts of your suggestion and made a code the works against all search criterias. Just need to check it with you before upload it

    PHP Code:
    public function search_results($data$record_start=0,$number_of_records=4,$next_prev_sign='>'$order=' ASC ')
        {
            
    $conditions $values = array();
            foreach(
    $data as $val=>$row)
                {
                    
    $conditions[] = $val." = ?";
                }
            foreach(
    $data as $val=>$row)
                {
                    
    $values[] = $row;
                }
            if (
    $conditions)
                {
                    
    $this->where " AND " implode(" AND "$conditions);
                }
            
    $sql "SELECT q2.id,
                   q2.code,
                   q2.class,
                   q2.category,
                   q2.price,
                   q2.production_date,
                   q4.title AS STATUS
            FROM   (SELECT q1.id,
                           q1.status,
                           code,
                           class,
                           category,
                           price,
                           production_date
                    FROM   products_table AS q1
                    WHERE  q1.id  "
    .$next_prev_sign." ".$record_start."
                    "
    .$this->where."
                    ORDER  BY q1.id "
    .$order.
                    LIMIT  "
    .$number_of_records.") 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($sql$values); 
            return 
    $results->result_array();
        } 
    This produces
    Code:
    SELECT q2.id,
           q2.code,
           q2.class,
           q2.category,
           q2.price,
           q2.production_date,
           q4.title AS STATUS
    FROM   (SELECT q1.id,
                   q1.status,
                   code,
                   class,
                   category,
                   price,
                   production_date
            FROM   products_table AS q1
            WHERE  q1.id > 0
                   AND class = '1'
                   AND category = 'C'
            ORDER  BY q1.id ASC
            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
    1 - Just need to find a way to remove single quotes from int columns.

    2 - I have logic in my model I don't like it! Is there any techniques to avoid this?

    3 - Any tips on how to add pirce between x and x ?



    So this according to the manual is secure as well agains injections.

    CI manual
    Bindings enable you to simplify your query syntax by letting the system put the queries together for you. Consider the following example:
    $sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";

    $this->db->query($sql, array(3, 'live', 'Rick'));

    The question marks in the query are automatically replaced with the values in the array in the second parameter of the query function.

    The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.
    Last edited by zxcvbnm; May 25th, 2013 at 10:53 PM.
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,992
    Rep Power
    9397
    0. Don't need two loops over $data.
    1. If CI is doing it then don't worry about it.
    2. What logic don't you like?
    3.
    PHP Code:
    $conditions[] = "price BETWEEN ? AND ?";
    $values[] =  $lower_price;
    $values[] = $higher_price
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by requinix
    0. Don't need two loops over $data
    What do you think? Simple and flexible.
    PHP Code:
    public function search_results($data$sql,$where$values)
        {
            
    $select_sql "SELECT q2.id,
                   q2.code,
                   q2.class,
                   q2.category,
                   q2.price,
                   q2.production_date,
                   q4.title AS STATUS
            FROM   (SELECT q1.id,
                           q1.status,
                           code,
                           class,
                           category,
                           price,
                           production_date
                    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();
        } 
    PHP 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 $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);
                
    $this->output->enable_profiler(TRUE);    
            }
            
        protected function 
    next_link(&$array$source)
            {
                
    $tmp=end($array);
                return 
    "<a class = \"yellow\" href=\"".base_url('results/'.$source.'/next/'.$tmp['id'])."\">Next Page >></a>";
            }
        protected function 
    previous_link(&$array$source)
            {
                
    $tmp=reset($array);
                return 
    "<a class = \"yellow\" href=\"".base_url('results/'.$source.'/prev/'.$tmp['id'])."\"><< Previous Page</a>";
            }    


IMN logo majestic logo threadwatch logo seochat tools logo