php codeigniter select query

Question

This is the my original SQL query

select 'car' as type,id,title,status from car_product
union
select 'van' as type,id,title,status from van_product

Now I want to run this query using CodeIgniter, so I try to use this one

$this -> db -> select('`car` As type,id,title,status');
$this -> db -> from('car_product');
$this -> db -> where('status =',$status);
$query_car = $this -> db -> get()->result();

$this -> db -> select('`van` As type,id,title,status');
$this -> db -> from('van_product');
$this -> db -> where('status =',$status);
$query_van = $this -> db -> get()->result();

return array_merge($query_car, $query_van);

but it's not working, please can you help?

http://stackoverflow.com/questions/2040655/union-query-with-codeigniters-active-record-pattern — sinisake, Mar 21 '15 at 15:51
"union" is not a problem, problem is "'car' as type" part , it's not supporting in select query , Other thing is i don't want to use "$this->db->query" this one — Chamara Maduranga, Mar 21 '15 at 15:59
Can you please write the error message and print out the `$this->db->last_query()` — Shaiful Islam, Mar 21 '15 at 16:13
this is the error msg , Error Number: 1054 Unknown column 'car' in 'field list' SELECT `car` As type, `id`, `title`, `date`, `status` FROM (`car_product`) WHERE `status` = 'P' Filename: C:\wamp\www\admin\system\database\DB_driver.php Line Number: 330 — Chamara Maduranga, Mar 21 '15 at 16:16
Read the error. There isn't any column named `car` in your table. — Parag Tyagi, Mar 21 '15 at 16:18
use this `$this -> db -> select('car type,id,title,status');` and `$this -> db -> select('van type,id,title,status');` — Shaiful Islam, Mar 21 '15 at 16:22
Hi Parag Tyagi , yes there is no 'car' column, please check my original sql query then you can find my requirement — Chamara Maduranga, Mar 21 '15 at 16:27

score 1 · Accepted Answer · answered Mar 21 '15 at 16:22

You can use ->query() method directly without using queryBuilder for this.

$query = $this->db->query("select 'car' as type,id,title,status from car_product union    select 'van' as type,id,title,status from van_product");

Then you can deal it as you deal with your other queries

e.g:

return $query->result();

score 1 · Answer 2 · answered Mar 21 '15 at 17:16

if you want to do it the active record way, use this one.

 $this -> db -> select('"car" type,id,title,status',false);
 $this -> db -> from('car_product');
 $this -> db -> where('status =',$status);
 $query_car = $this -> db -> get()->result();

 $this -> db -> select('"van" type,id,title,status',false);
 $this -> db -> from('van_product');
 $this -> db -> where('status =',$status);
 $query_van = $this -> db -> get()->result();

 return array_merge($query_car, $query_van);

https://ellislab.com/codeigniter/user-guide/database/active_record.html

$this->db->select(); has an optional second parameter that enables you to use compound select statement when needed.

Note: you will not be protected against backticks when you set the optional parameter to "false"

@ChamaraMaduranga Accept the answer (green tick left to answer) if the answer helped. It'll close the question and help others for future use. — Parag Tyagi, Mar 21 '15 at 18:19

score 0 · Answer 3 · answered Mar 21 '15 at 16:07

0

Try removing '=', :

ex: $status = 'Chamara'

$this->db->where("status",$status);

//Produces: WHERE 'status' = 'Chamara'

Suggest: You can use arrays. Maybe in future you'll need to set more than one condition

$condition = array("status"=>$status);
$this->db->where($condition);

Suggest 2: Use $this->db->get('van_product'); instead of

$this -> db -> from('van_product');
$query_van = $this -> db -> get()->result();

// Produces: SELECT * FROM van_product

answered Mar 21 '15 at 16:07

thanks Deusmar , but my actual issue is ` $this -> db -> select('`car` As type,id,title,status'); $this -> db -> from('car_product'); $this -> db -> where('status =','P'); $query_car = $this -> db -> get()->result();` codeignator didn't support `'`car` As type` – Chamara Maduranga Mar 21 '15 at 16:11

php codeigniter select query

3 Answers3