在自动完成搜索查询中使用SQL UNION和Codeigniter / PHP

Question

I'm trying to develop an autocomplete function in Codeigniter, can someone let me know if there is any problem with this query? It seems reasonable to me, but I'm not getting any output...

for example, if someone types in 'Ma' in the search box, i want to use the following query..

$term=$this->input->post('term')='Ma'; 

$this->db->query(  'SELECT subject
                    FROM items
                    WHERE subject LIKE 'Ma%'
                  UNION ALL
                    SELECT first_name
                    FROM accounts
                    WHERE first_name LIKE 'Ma%'
                  UNION ALL
                    SELECT description
                    FROM items
                    WHERE description LIKE 'Ma%'');
$query=$this->db->get();

$items = array();

foreach ($query->result() as $row)
array_push($items, $row->subject);

$companies = array_slice($items, 0, 5);
echo json_encode($items);

The result should look something like this:

Marios Shoes1
Mario Shoes9
marklast
Mark
marktwook

Any advice would be greatly appreciated.

Answer 1

If you are using query then I think it should be done like this

$query = $this->db->query(  'SELECT subject
                    FROM items
                    WHERE subject LIKE 'Ma%'
                  UNION ALL
                    SELECT first_name
                    FROM accounts
                    WHERE first_name LIKE 'Ma%'
                  UNION ALL
                    SELECT description
                    FROM items
                    WHERE description LIKE 'Ma%'');