How do I stop the select_command from adding "WHERE 1=1 LIMIT 1 OFFSET 0" to the end of my custom Select statement?
This query is used internally to get the field names for grid header.
It is not used in data fetching. If you can tell what exact problem are you facing with this query, i may suggest some solution.
You can search this string in jqgrid_dist.php and change with more desired one.
Per your documentation;
Grid Options
Custom SQL Query
By default, when we define the ->table property, it fetches all the possible columns of table.
We can provide custom SQL query in ->select_command property to pick columns available for grid.
We can use complex multi-join sub-queries in it.
$g->select_command = "SELECT i.id, invdate , c.name, i.note, i.total, i.closed FROM invheader i INNER JOIN clients c ON c.client_id = i.client_id";
Here is my select_command
$d->select_command = "SELECT pipn.id,pipn.products_id,products_description.products_name,products.products_price,pipn.inventory_position_room_id,pipn.inventory_position_rack_id,pipn.inventory_position_level_id,pipn.products_stored_qty,pipn.date_update,pipn.is_deleted FROM products_inventory_position_new AS pipn LEFT OUTER JOIN products ON products.products_id = pipn.products_id LEFT OUTER JOIN products_description ON products_description.products_id = pipn.products_id ORDER BY pipn.products_id";
Here is the error I get:
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 1=1 LIMIT 1 OFFSET 0' at line 1 – SELECT pipn.id,pipn.products_id,products_description.products_name,products.products_price,pipn.inventory_position_room_id,pipn.inventory_position_rack_id,pipn.inventory_position_level_id,pipn.products_stored_qty,pipn.date_update,pipn.is_deleted FROM products_inventory_position_new AS pipn LEFT OUTER JOIN products ON products.products_id = pipn.products_id LEFT OUTER JOIN products_description ON products_description.products_id = pipn.products_id ORDER BY pipn.products_id WHERE 1=1 LIMIT 1 OFFSET 0
The WHERE 1=1 LIMIT 1 OFFSET 0 is getting automatically added to the end of the statement. How do I stop that from happening?
If the select_command is only used for column names then your documentation is wrong, as it states "We can provide custom SQL query in ->select_command property to pick columns available for grid.
We can use complex multi-join sub-queries in it."
How do you tell tell the grid what data goes in the custom columns defined above??
Please try removing ORDER BY from SQL, and set it in using following code.
$grid["sortname"] = 'id'; // by default sort grid by this field
$grid["sortorder"] = "desc"; // ASC or DESC
…
$g->set_options($grid);
It should resolve the case.
i get this error i want to use sql command:
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 1=1 LIMIT 1 OFFSET 0' at line 1 – SELECT WHERE 1=1 LIMIT 1 OFFSET 0
Hi,
I have tried a query and works fine, I need to load grid based in post value from other page in comparison value into query. this is my query:
SELECT
`demo_candidatos`.`demo_eleccion_ideleccion` AS `Ideleccion`,
`demo_candidatos`.`idcandidatos` AS `Idcandidatos`,
`demo_candidatos`.`nombre` AS `Candidatos`,
`demo_candidatos`.`numero` AS `Numero_Tarjeton`,
`demo_candidatos`.`tarjeton` AS `tarjeton`
FROM
(`demo_candidatos`
JOIN `demo_eleccion`,`demo_voting`)
WHERE
((`demo_eleccion`.`ideleccion` = `demo_candidatos`.`demo_eleccion_ideleccion`)
AND (`demo_eleccion`.`estado` = 'Abierto') AND (`demo_eleccion`.`ideleccion` = `_POSTVAR`)AND
(`demo_eleccion`.`ideleccion` = `_POSTVAR`)AND
(`demo_eleccion`.`ideleccion` = `_POSTVAR`))
GROUP BY `demo_candidatos`.`idcandidatos`
Thanks!