when I write anything in the autocomplete field it show null null null null null instead of real results from the database. The weird thing is that the number of nulls is the actual exact number of search results of this query
$l->select_command = "SELECT id, il.qty, il.product_id , pr.name as product_name, invHeader_id, il.price, (il.price * il.qty) as total,
(SELECT SUM(il.price * il.qty) as t1 FROM invLine il
LEFT OUTER JOIN product pr ON pr.product_id = il.product_id
WHERE invHeader_id = $id) as ttotal
FROM invLine il
LEFT OUTER JOIN product pr ON pr.product_id = il.product_id
WHERE invHeader_id = $id";
$l->set_columns(
array(
array( title => "الرقم", name => id, width => 50, hidden => false, resizable => true),
array( title => "product_id", name => product_id, width => 50, hidden => true, editable => true),
array( title => "invHeader_id", name => invHeader_id, width => 150, hidden => true, resizable => true, editable => false, edittype => select, formatter => select, stype => select, editoptions => array( value => $g->get_dropdown_values("select distinct id as k, id as v from invHeader") ), searchoptions => array( value => ":;". $g->get_dropdown_values("select distinct id as k, id as v from invHeader") ) ),
array( title => "المنتج", name => product_name, dbname => "pr.name", width => 150, hidden => false, resizable => true, editable => true, formatter => autocomplete, formatoptions => array( "sql"=>"SELECT product_id, name FROM product", "search_on"=>"name", "update_field" => "product_id" )),
array( title => "الكمية", name => il.qty, width => 150, hidden => false, resizable => true, editable => true),
array( title => "السعر", name => il.price, width => 150, hidden => false, resizable => true, editable => true),
array( title => "المجموع", name => total, width => 150, hidden => false, resizable => true, editable => false),
array( title => "ttotal", name => ttotal, width => 150, hidden => true, resizable => true, editable => false),
)
);
You need to alias the 2 fields in sql as k,v
SELECT product_id as k, name as v FROM product
array( title => "المنتج", name => product_name, dbname => "pr.name", width => 150, hidden => false, resizable => true, editable => true, formatter => autocomplete, formatoptions => array( "sql"=>"SELECT product_id as k, name as v FROM product", "search_on"=>"name", "update_field" => "product_id" )),