UNION query using CodeIgniter query builder methods [duplicate]

Question

I want to display my data from database into select2 server side, I want to combine my table into one with same field but failed, this is my model:

$json = [];
if (!empty($this->input->get("q"))) {
    $this->db->like('nama', $this->input->get("q"));    
    $query = $this->db->select('idmuzakki as id,nama as text')
            ->limit(10)
            ->get("muzakki");
    $query = $this->db->select('idcorporate as id, nama as text')
            ->limit(10)
            ->get("corporate"); 
    $json = $query->result();
}

echo json_encode($json);

How can I combine 2 table into 1 result with same field (as id and as text) for select2 server side?

Brian Ramsey · Accepted Answer · 2017-02-08 11:16:00Z

1

Either do 2 queries and join the results in the Object (or array) you're using, or use a UNION.

CI doesn't have this option with query builder so:

$this->db->query('(SELECT idmuzakki as id,nama as text FROM muzakki  WHERE nama LIKE \'%$this->input->get("q")%\' LIMIT 10)
    UNION ALL
    (SELECT idcorporate as id, nama as text FROM corporate WHERE nama LIKE \'%$this->input->get("q")%\' LIMIT 10)');
$result = $query->result();
$json = json_encode($result);

UNION ALL will keep duplicates, if you don't want this, then just use UNION

If you want to merge the objects,name each query to a different variable and then use

$this->db->like('nama', $this->input->get("q"));    
$query1 = $this->db->select('idmuzakki as id,nama as text')
        ->limit(10)
        ->get("muzakki");
        ->result();
$query2 = $this->db->select('idcorporate as id, nama as text')
        ->limit(10)
        ->get("corporate")
        ->result(); 
$result = array_merge($query1, $query2);
$json = json_encode($result);

Of the 2 methods, Union is probably much nicer...

This is all pseudo-code(should work though), I dont have mysql or ci handy atm

edited Feb 8, 2017 at 11:16

answered Feb 8, 2017 at 10:21

Brian Ramsey

8507 silver badges21 bronze badges

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

6 Comments

Nasihun Amin Suhardiyan Over a year ago

your 1st option "the results could not be loaded".. your 2nd option in firebug debugger "b is null"..

Brian Ramsey Over a year ago

as I said, that is all pseudo-code, you should be able to get the gist of it and debug it... For the 2nd one the raw result isn't usable until results are extracted...

Nasihun Amin Suhardiyan Over a year ago

I think the problem is "UNION ALL" when I try single query its work, but when I try combine (UNION ALL) its not working

Brian Ramsey Over a year ago

try updated answer... I'm used to SQL Server syntax at the moment... missed the parentheses

Nasihun Amin Suhardiyan Over a year ago

work like a charm, added "() UNION ALL ()", thanks for helping me

|

Collectives™ on Stack Overflow

UNION query using CodeIgniter query builder methods [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related