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");
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.
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
Hello,
I tried to regenerate this case but it’s working as expected. I’m emailing you updated build to test with it.