Drop down disables search on select fields?

QuestionsDrop down disables search on select fields?
Gary Brett asked 5 years ago

Hello there, I created a dropdown based on your demo and have it working ok as such. The select is based on data in another table, these records appear & I can select and store but it errors when I attempt to search on the fields with dropdown?

The grid is based on tbl_leads & select data is in dbo_advisers, here is the code in my page have I messed it up?

$col = array();
$col["title"] = "Adviser"; // caption of column
$col["name"] = "Adviser"; // grid column name
$col["dbname"] = "dbo_advisers.ContactFullName"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["search"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct ContactFullName as k, ContactFullName as v from dbo_advisers");
$col["editoptions"] = array("value"=>":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

When I enter text into the filter box on that column it returns;

Couldn't execute query. Unknown column 'dbo_advisers.ContactFullName' in 'where clause' – SELECT count(*) as c FROM (SELECT * FROM tbl_leads WHERE 1=1 AND `dbo_advisers`.`ContactFullName` LIKE '%lyn%') pg_tmp

Thanks

Gary

8 Answers
Gary Brett answered 5 years ago

Hi Abu, been playing with this but still cannot get it to work which is causing me an issue at present, many of my grids will operate in this same method. In the demo you use a field from the grid table, I am trying to use a field from another table which stores the names I need in the select?

Will it not work unless I use a column within the table the grid is based on, revised code below, marked where I am using another table?

// set database table for CRUD operations
$g->table = "tbl_leads";

// caption of column
$col = array();
$col["name"] = "ID"; // grid column name
$col["hidden"] = true; // hide on grid
$cols[] = $col;

$col = array();
$col["title"] = "Adviser"; // caption of column
$col["name"] = "Adviser"; // grid column name
$col["dbname"] = "dbo_advisers.ContactFullName"; // *****DIFFERENT TABLE
$col["editable"] = true;
$col["edittype"] = "select";
$str = $g->get_dropdown_values("select distinct ContactFullName as k, ContactFullName as v from dbo_advisers"); //*********DIFFERENT TABLE
$col["editoptions"] = array("value"=>":;".$str);
$col["searchoptions"] = array("value" => ":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

Kind Regards

Gary

Gary Brett answered 5 years ago

Just a thought would I need to add a query to the search options to tell it to look in a different table?

$col["searchoptions"] = array("value" => ":;".$str);

Gary

Abu Ghufran answered 5 years ago

The searching uses the select_command provided in grid config. If you specified only tablename then query becomes 'select * from table'.

When you have to search in other table, you need to specify JOIN query that includes both tables tbl_leads, dbo_advisers.

Specify $g->select_command = "JOIN query";
The field dbname work after that.

Gary Brett answered 5 years ago

Thanks Abu, if I had multiple select fields all pointing at different tables how would we define which query is for which select?

Thannk you

Abu Ghufran answered 5 years ago

The dropdown will be populated in same following way:

$str = $g->get_dropdown_values("select distinct ContactFullName as k, ContactFullName as v from dbo_advisers");
$col["editoptions"] = array("value"=>":;".$str);

The search uses one select_command and append WHERE clauses according to each search field.

To search with some other table's field, that table must be used in select_command, so that search field is present in fieldset when used in where clause. This also applies if you have various dropdown from multiple tables.

Gary Brett answered 5 years ago

Hi Abu & happy new year to you. After adding a query and leaving the original table in I can still add/edit new records with drop down but now when searching the field is filtered to blank..

I think the same thing happens on your demo for dropdown2 if I have understood it correctly?

To save time I thought I would simply have 1 form on the page to add new records with the table showing table below, is that possible? In effect the table is read/edit only while the form is for adding only? Cant workout though how to display the form for adding without clicking the + button at bottom of table?

Thank you

Gary Brett answered 5 years ago

Hi again Abu, ok got the search working now on 1 field but after adding a second query for another select field it errors on load with;

Couldn't execute query. Duplicate column name 'ID' – SELECT count(*) as c FROM (Select * From tbl_leads Inner Join tbl_status On tbl_status.Status = tbl_leads.Status WHERE 1=1) pg_tmp

My page looks like this now;

// set database table for CRUD operations
$g->table = "tbl_leads";

$g->select_command = "Select * From tbl_leads Inner Join dbo_advisers On tbl_leads.Adviser = dbo_advisers.ContactFullName";

$g->select_command = "Select * From tbl_leads Inner Join tbl_status On tbl_status.Status = tbl_leads.Status";

$col = array();
$col["title"] = "Adviser"; // caption of column
$col["name"] = "Adviser"; // grid column name
$col["dbname"] = "dbo_advisers.ContactFullName"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct ContactFullName as k, ContactFullName as v from dbo_advisers");
$col["editoptions"] = array("value"=>":;".$str);
$col["searchoptions"] = array("value" => ":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

$col = array();
$col["title"] = "Status"; // caption of column
$col["name"] = "Status"; // grid column name
$col["dbname"] = "tbl_status.Status"; // this is required as we need to search in name field, not id
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct Status as k, Status as v from tbl_status");
$col["editoptions"] = array("value"=>":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

Can you see where I am going wrong, removing the second select at top fixes the error but I then cannot search in status field?

Thank you

Abu Ghufran answered 5 years ago

Instead of 2 select_commands, you have to make single sql with 2 joins.

$g->select_command = "Select * From tbl_leads Inner Join dbo_advisers On tbl_leads.Adviser = dbo_advisers.ContactFullName";

$g->select_command = "Select * From tbl_leads Inner Join tbl_status On tbl_status.Status = tbl_leads.Status";

Replace with

$g->select_command = "Select * From tbl_leads
Inner Join dbo_advisers On tbl_leads.Adviser = dbo_advisers.ContactFullName
Inner Join tbl_status On tbl_status.Status = tbl_leads.Status
";

To make search dropdown in top flter, you need to set:

$col["stype"] = "select";
$col["searchoptions"] = array("value" => $str);

Your Answer

14 + 0 =

Login with your Social Id:

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 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

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

Let us improve this post!

Tell us how we can improve this post?