Gustavo Lazaro asked 3 years ago
Abu. When I add the WHERE clause to an IN field it gives an error 500. Is there any way to solve it?
Example: $ col ["formatoptions"] = array ("sql" => "SELECT playercode AS k, name AS v FROM players WHERE state = 'A' AND codCategory IN (6,7) ORDER BY name", "search_on" => "name", "update_field" => "playerCode");
Abu Ghufran Staff answered 3 years ago
I verified IN clause with autocomplete query and it works as expected. Kindly test this query in sql ide (such as phpmyadmin) and see if it works as expected.
If issue persist, please send complete code for review along with related db tables.
Gustavo Lazaro answered 3 years ago
Abu sent you a screenshot of the bug.
If I write (it works):
"sql" => "SELECT playercode AS k, name AS v FROM players WHERE status = 'A' AND categorycode = 6 ORDER BY name", "search_on" => "name", "update_field" => "playercode"
If I write (gives error):
"sql" => "SELECT playercode AS k, name AS v FROM players WHERE status = 'A' AND codCategory IN (6, 7) ORDER BY name", "search_on" => "name", "update_field" => " codJugador "
$col = array();
$col[“title”] = “Jugador”;
$col[“name”] = “nombre”;
$col[“dbname”] = “ju.nombre”;
$col[“editable”] = true;
$col[“editrules”] = array(“required”=>true);
$col[“editoptions”] = array(“size”=>60, “maxlength”=>60);
$col[“formatter”] = “autocomplete”;
$col[“formatoptions”] = array(“sql”=>”SELECT codJugador AS k, nombre AS v FROM jugadores WHERE estado=’A’ AND codCategoria IN ($codCategoria) ORDER BY nombre”, “search_on”=>”nombre”, “update_field”=>”codJugador”);
$cols[] = $col;
Error:
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 nombre like ‘%s%’ ORDER BY nombre’ at line 1 – SELECT codJugador AS k, nombre AS v FROM jugadores WHERE estado=’A’ AND codCategoria IN (1,2,3,5,6,7,8,9,10,11,12,13,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49) WHERE nombre like ‘%s%’ ORDER BY nombre
Attach images
Thank you very much
Abu Ghufran Staff answered 3 years ago
Hello,
I tried to regenerate this case but it’s working as expected. I’m emailing you updated build to test with it.
