codeigniter change complex query into active record

Question

I have a codeigniter app.

My active record syntax works perfectly and is:

  function get_as_09($q){
    $this->db->select('m3');
    $this->db->where('ProductCode', $q);
    $query = $this->db->get('ProductList');
    if($query->num_rows > 0){
        foreach ($query->result_array() as $row){
            $row_set[] = htmlentities(stripslashes($row['m3'])); //build an array
       }
       return $row_set;
    }
  }

This is effectively

select 'm3' from 'ProductList' where ProductCode='$1'

What I need to do is convert the below query into an active record type query and return it to the controller as per above active record syntax:

select length from
(SELECT  
      [Length]
      ,CONCAT(([width]*1000),([thickness]*1000),REPLACE([ProductCode],concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options
  FROM [dbo].[dbo].[ProductList]) as p
  where options='25100cr' order by length

I picture something like below but this does not work.

$this->db->select(length);
$this->db->from(SELECT  [Length],CONCAT(([width]*1000),([thickness]*1000),REPLACE[ProductCode],concat(([width]*1000),([thickness]*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options
      FROM [dbo].[dbo].[ProductList]);
$this->db->where(options, $q);
$this->db->order(length, desc);

Help appreciated as always. Thanks again.

Why don't you just execute your sql query ? $this->db->query($sql) — Brewal
– Brewal, Commented Apr 18, 2013 at 12:48
@Brewal, not open to injection or any other security vulnerabilities? — Smudger
– Smudger, Commented Apr 18, 2013 at 12:58

Community · Accepted Answer · 2017-05-23 12:04:57Z

2

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this Go to system/database/DB_active_rec.php Remove public or protected keyword from these functions

public function _compile_select($select_override = FALSE)
public function _reset_select()

Now subquery writing in available And now here is your query with active record

$select =   array(
                'Length'
                'CONCAT(([width]*1000)',
                'thickness * 1000',
                'REPLACE(ProductCode, concat((width*1000),(thickness*1000),REPLACE((convert(varchar,convert(decimal(8,1),length))),'.','')),'')) as options'
);
$this->db->select($select);
$this->db->from('ProductList');

$Subquery = $this->db->_compile_select();

$this->db->_reset_select();

$this->db->select('length');
$this->db->from("($Subquery)");
$this->db->where('options','25100cr');
$this->db->order_by('length');

And the thing is done. Cheers!!! Note : While using sub queries you must use

$this->db->from('myTable')

instead of

$this->db->get('myTable')

which runs the query.

Source

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Apr 18, 2013 at 13:33

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Smudger Over a year ago

Hi raheel, how do set this entire query = $q so I can do ` $query=$this->db->query($sql); if($query->num_rows > 0){ foreach ($query->result_array() as $row){ $row_set[] = htmlentities(stripslashes($row['length'])); //build an array } return $row_set; } }`

Muhammad Raheel Over a year ago

instead of ` $query=$this->db->query($sql)` use ` $query=$this->db->get()` and than your if condition and so on

Aken Roberts Over a year ago

For what it's worth, the query builder is not designed for hacky things like this. If you need subqueries and whatnot, you should be writing the queries manually. You can use query bindings to make sure your data is escaped (proper validation before hand is also recommended).

Collectives™ on Stack Overflow

codeigniter change complex query into active record

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related