Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
Why don't you just execute your sql query ? $this->db->query($sql) – Brewal yesterday
@Brewal, not open to injection or any other security vulnerabilities? – Smudger yesterday

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
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; } }` – Smudger yesterday
1  
instead of ` $query=$this->db->query($sql)` use ` $query=$this->db->get()` and than your if condition and so on – raheel shan yesterday
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). – Cryode 19 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.