Hi Abu, I have a grid with several select fields to populate data from other tables in db, this works great. Today I added an autocomplete field and whenever I type anything in that field it throws a 500: internal server error. Status: Error
Removing that field and the grid works fine again, is my code wrong?
$col = array();
$col["title"] = "Name";
$col["name"] = "Client";
$col["dbname"] = "tbl_leads"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["width"] = "80";
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array( "sql"=>"SELECT Client as k, Client as v FROM tbl_leads",
"search_on"=>"Client",
"update_field" => "Client");
$cols[] = $col;
Check if this query is correct: SELECT Client as k, Client as v FROM tbl_leads
Second, Check the sql query error using firebug ajax call response. It will tell exact reason of 500.
https://phpgrid.desk.com/customer/portal/articles/926266
Thanks Abu, the SELECT query returns correct, 2 columns with Client Name as expected. When I run in Firebug I start typing for example Karl & it pops up with the 500 error, it returns;
Couldn't execute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE like 'karl%'' at line 1 – WHERE like 'karl%'
I have no where query running on this grid at present?
Any ideas?
Hi Abu, before I do that can I add, I have found that if I change the col name it suddenly works but obviously doesn't store the entered value as that column does not exist in my table?
I change below from 'Client' to 'name'and the autocomplete is populated, if I select a value from the list and submit form it displays as 'Undefined'
$col = array();
$col["title"] = "Client";
$col["name"] = "name";
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array( "sql"=>"SELECT Client as k, Client as v FROM tbl_leads",
"search_on"=>"Client",
"update_field" => "Client");
$cols[] = $col;
I guess I could change my table column name but its referenced in lost of other places..
OK Abu, update the files above, changed name to Client and it now works and stores the value in table, on the autocomplete list now though it displays the full 'Client' column rather than filtering by letter? For example if I type John, it shows all names not just those with John in?
$col = array();
$col["title"] = "Client";
$col["name"] = "Client";
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array( "sql"=>"Select tbl_leads.Client As k, tbl_leads.Client As v From tbl_leads Where tbl_leads.Adviser = 'John Smith'",
"search_on"=>"Client",
"update_field" => "Client");
$cols[] = $col;
Hi Abu, I have it all sorted now thank you, in case it helps anyone in the future below is code that now works..
I changed the jqgrid_dist.php;
// callback function
$col = array();
$col["title"] = "Client";
$col["name"] = "Client";
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array( "sql"=>"SELECT *, Client as v FROM tbl_leads where Adviser='John Smith' ORDER BY Client desc",
"search_on"=>"concat(Client,'-',ID)",
//"callback"=>"fill_form");
"update_field" => "Client");
$cols[] = $col;
Thanks again for your help..