SQL Error when marking custom field as searchable
VictorUlloa, Fri Sep 02 2022, 11:53AMHi:
When I add a custom ftdbfield to a grid, and then mark it as "Sortable" and "Searchable", I got errors like this:
(The custom field is "nombrecasa"):
2022-09-02 13:45:45 | [c:\phspeed\xampp\htdocs\_libs\php\spdbtable.php] [926]::sqlstate[42s22]: column not found: 1054 unknown column 'nombrecasa' in 'where clause'
::select pksoftware,nombresoftware,nombrecortosoftware,versionvigente,compilacionvigente,fkcasa,ordensoftware,validarupdates,categoriasoftware from software where nombresoftware like '%vnc%' or nombrecortosoftware like '%vnc%' or fkcasa like '%vnc%' or nombrecasa like '%vnc%' or ordensoftware like '%vnc%' or validarupdates like '%vnc%' or categoriasoftware like '%vnc%' limit 10000 offset 0
2022-09-02 13:45:45 | [c:\phspeed\xampp\htdocs\_libs\php\spdbtable.php] [1216]::sqlstate[hy000]: general error: 1 no such column: nombrecasa
Two side effects:
* If I enter any text in the search box, the grid is filtered ok, even with the error message. But if I then change or remove the text from the search box, the grid doesn't refresh anymore
* If I remove the check from the "searchable" box for this custom field, the form is still searching on the field! I regenerate the form, close and reopen PHsPeed and nothing. The form only generates the correct code when I remove the "Sortable" check too.
Regards,
Re: SQL Error when marking custom field as searchable
VictorUlloa, Fri Sep 02 2022, 11:59AM
Additional info:
I don't know if this is the intended behaviour, but the custom field doesn't get "Sortable" unless I checked both "Sortable" AND "Searchable".
VictorUlloa, Fri Sep 02 2022, 11:59AM
Additional info:
I don't know if this is the intended behaviour, but the custom field doesn't get "Sortable" unless I checked both "Sortable" AND "Searchable".
Re: SQL Error when marking custom field as searchable
administrator, Fri Sep 02 2022, 06:05PM
If you add a ftdb... field to the grid then the lookup is executed after retrieving a record from the main query. The main query is the query that the sort applies to. So the new field is not a part of the original resultset, causing the error.
There are two ways to handle this, but both are similar in a way that the sortable field must be part of the original query. I'm not exactly sure if your version supports it already, but at least the upcoming version allows you to add a custom field to the database table. It generates a valid join, and then you can sort on the field. It also takes care of Crud function where the field is not used. The second method is not to use a dbtable, but dbquery and apply the join in the sql. For Crud operations you must supply the correct statements (ie. using the Crud component. You can also use the wizard to use the DBQuery and then apply the full query then.
administrator, Fri Sep 02 2022, 06:05PM
If you add a ftdb... field to the grid then the lookup is executed after retrieving a record from the main query. The main query is the query that the sort applies to. So the new field is not a part of the original resultset, causing the error.
There are two ways to handle this, but both are similar in a way that the sortable field must be part of the original query. I'm not exactly sure if your version supports it already, but at least the upcoming version allows you to add a custom field to the database table. It generates a valid join, and then you can sort on the field. It also takes care of Crud function where the field is not used. The second method is not to use a dbtable, but dbquery and apply the join in the sql. For Crud operations you must supply the correct statements (ie. using the Crud component. You can also use the wizard to use the DBQuery and then apply the full query then.
Re: SQL Error when marking custom field as searchable
VictorUlloa, Fri Sep 02 2022, 07:35PM
My PHsPeed version doesn't have the option to add custom fields to the table, just to the Grid, but will be a welcome feature! Thank you very much
VictorUlloa, Fri Sep 02 2022, 07:35PM
My PHsPeed version doesn't have the option to add custom fields to the table, just to the Grid, but will be a welcome feature! Thank you very much
Re: SQL Error when marking custom field as searchable
VictorUlloa, Mon Sep 05 2022, 10:47AM
I was wrong, I had found the option to add lookup fields to a DBtable, and those fields are both sortable and searchable. I now understand what you explained about the fields being part of the original data retrieved from the DB. Thank you very much for the explanation.
VictorUlloa, Mon Sep 05 2022, 10:47AM
I was wrong, I had found the option to add lookup fields to a DBtable, and those fields are both sortable and searchable. I now understand what you explained about the fields being part of the original data retrieved from the DB. Thank you very much for the explanation.
Print this page