SQL error when filtering calculated fields

QuestionsSQL error when filtering calculated fields
Matthew asked 4 years ago

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.

4 Answers
Matthew answered 4 years ago

I've just addressed the "retaining the error on further tables" by disabling persistsearch on all our files.

$grid["persistsearch"] = false;

Matthew answered 4 years ago

Just found another issue I thinks related. For the same Boom Licence grid above when we try to filter by the "clockno" field it throws a different error:

Couldn't execute query. Column 'clockno' in where clause is ambiguous – 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 `clockno` LIKE '%1%') pg_tmp

It obviously doesn't know to search by the b or e table. I tried this:

SELECT
b.clockno as clockno,

but still errors:

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

Abu Ghufran answered 4 years ago

Pasting from docs:

// in case of field name ambiguity b/w tables, you can set:
$col["dbname"] = "b.clockno";

For first query of full name case, you can set something similar:
$col["dbname"] = "CONCAT(e.surname, ', ' ,e.firstname)";

It replaces the column name with dbname property in where and order by clauses.

Matthew answered 4 years ago

Awesome mate, that's done the trick! Thanks very much for the prompt response

Your Answer

19 + 6 =

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!