Autocomplete 500 server error

QuestionsAutocomplete 500 server error
Gary Brett asked 5 years ago

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;

8 Answers
Gary Brett answered 5 years ago

Hi, also tried removing $col["dbname"] = "tbl_leads"; with no joy?

Thank you

Abu Ghufran answered 5 years ago

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

Gary Brett answered 5 years ago

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?

Abu Ghufran answered 5 years ago

I've emailed you latest build, kindly recheck after update.

Gary Brett answered 5 years ago

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

Gary Brett answered 5 years ago

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;

Abu Ghufran answered 5 years ago

Please share full code for review.

Gary Brett answered 5 years ago

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

Your Answer

17 + 8 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!