codeigniter issue using UNION SQL operator in developing an jquery-ui autocomplete function

[eluser]tim peterson[/eluser]
Hi all,

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..

Code:

$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,

thank you, Tim

[eluser]danmontgomery[/eluser]
First, you can't assign a value to a function. This will never work:

Code:

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

Other than that, the forum's syntax highlighting in your code should immediately point out what the problem is. You need to escape your single quotes or use double quotes.

Code:

'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%\''
//or
"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%'"