Hi,

We use custom SQL commands to create calculated fields which are then displayed in the phpgrid columns. This display works fine. We have a number of PHP files each displaying a grid. We have a HTML frontend with links to these files.

Heres the SQL to populate our Boom Licence table:

$g->select_command = "
SELECT
b.clockno,
b.licno,
b.expirydate,
CONCAT(e.surname, ', ' ,e.firstname) AS fullname
FROM
boomliftlic b,
employees e
WHERE
b.clockno = e.clockno
";

Here is the column code for the AS field:

$col = array();
$col["title"] = "Name";
$col["name"] = "fullname";
$col["width"] = "125";
$col["fixed"] = true;
$col["editable"] = false;
$col["search"] = true;
$cols[] = $col

The issue is that when using the autofilter in the grid it throws an error:

Couldn't execute query. Unknown column 'fullname' in 'where clause' – SELECT count(*) as c FROM ( SELECT b.clockno, b.licno, b.expirydate, CONCAT(e.surname, ', ' ,e.firstname) AS fullname FROM boomliftlic b, employees e WHERE b.clockno = e.clockno AND `fullname` LIKE '%f%') pg_tmp

I've tested with a few tables and any SQL query that use the "AS" command will cause that columns filter to break. Also when it errors and you close the error window browsing to any other table looks like it appends that WHERE clause on the end of the next grids SQL as well. If you browse to another table with a field called "fullname" it will have the filter value already in that field and instantly display an SQL error window. If you browse to a table without a fullname field none of the filters are set but it still appends the "AND `fullname` LIKE '%f%'" to the end of the query and displays the SQL error window. Both these display no data in the tables either.

Any clues or workarounds I can use to address this? My quick fix at the moment is to make these columns "non searchable" so users can't cause the error but they will ideally want to filter by these columns.