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

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

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.

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

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.

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

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

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);