Choose your database:
MS SQL Server
SQL Anywhere

Subscribe to our news:
Charles Phillips: "Just want to take a second to really thank you for all the great support you give – I think that’s by far the best feature you offer".
Grey: "We're a two-person company, it's just me an my wife. I'm the technical guru, and she handles the business operations. I have to know a lot about MySQL, but that's much too technical for her. I have frequently had to setup CGI scripts (I code in Perl) so she can manage some of our tables (suppliers, manufacturers, etc).

I discovered PHP Generator a couple of days ago, tried the free version,and within a few hours I had purchased the Pro version (as well as SQL Maestro for MySQL).

Today I am completing the conversion of the last of my custom table managers to PHP Generator. This is eliminating several thousand lines of code that I don't have to support any more!

Thanks for this fantastic product".


Add your opinion

PHP Generator for MySQL online Help

Prev Return to chapter overview Next

Lookup settings


Lookups are used in data grids and in data input forms. For read-only views (List, Single Record View, Print, etc) lookups are used to display records of another dataset corresponding to and instead of values stored in the webpage base data source. For Insert and Edit forms lookup editors are used to simplify input by selecting a value storing in another dataset and corresponding to a pre-defined value from the base one.


In data input forms the following  editors can be used for lookup controls: Dynamic Combobox (default option), Radio Group, Combobox, Cascading Combobox, and Dynamic Cascading Combobox. Here are some recommendations:


for lookup data sources containing a few (usually no more than 5-7) values, all editors can be used.
for lookup data sources containing no more than 15-20 values, both Dynamic Combobox and Combobox editors can be used.
for lookup data sources containing 20+ values, the Dynamic Combobox editor is recommended.
to allow users select the desired value step-by-step (for example, user selects a country and then a city in this country), Cascading Combobox or Dynamic Cascading Combobox can be used.


Setting a lookup editor bound to a data source (table, view, query)

If you create a PHP script for taking orders, the 'Orders' table generally includes a field hosting a number indicating the customer who made the order. Working directly with the customer numbers is not the most natural way; most users will prefer to work with customer names. However, in the database the customers' names are stored in a different table to avoid duplicating the customer data for each order by the same customer. To get around such a situation, you can enable a lookup editor:


check the Use lookup box;
select the foreign table/view/query as Data Source;
specify the field with the same data as Link field;
set the field with the data to be appeared in the lookup editor as Display field;


By default, PHP Generator for MySQL uses the Dynamic Combobox editor for a column linked by a foreign key with a single column from another table (if Setup lookups by foreign key option is enabled).


Add new item on the fly

Turn this option ON in order to allow adding new items directly in Insert and Edit forms. When this option is checked, a plus button is displayed  on the right of the corresponding control (Live demo).



Click the ellipsis button to customize the modal dialog to be displayed when adding a new item. For example, you might want to hide some fields and/or customize the form layout.



Display lookup record

Turn this option ON in order to allow users to see the whole lookup record clicking on the corresponding cell in the data grid (live demo).



Click the ellipsis button to customize the modal dialog to be displayed when viewing the record. For example, you might want to hide some fields and/or customize the form layout.



Using Filter condition

Filter condition allows you to reduce the list of values represented in the lookup editor with a specified criteria. This condition corresponds to the WHERE clause applied to the data source (you must not add the WHERE keyword to beginning of the condition). The following operators can be used in this clause: =,<> (!=), >, <, >=, <=, BETWEEN, LIKE, IN.  It is also possible to use predefined variables like %CURRENT_USER_NAME%.


Example 1

To enable a lookup editor with a list of USA cities (the corresponding value of "country_id" is 103) named like Da*, specify the following condition: country_id = 103 AND city LIKE 'Da%'


Example 2

Suppose we have a table that contains a column 'owner' with owner information. To set a lookup editor with the list of values owned by the current user, specify the filter condition as follows: owner = '%CURRENT_USER_NAME%'


NB. The Filter Condition property cannot be used to implement dependent lookups or another similar logic. Only predefined variables and constant expressions are allowed.


Setting a lookup editor represented data of multiple columns

To create such lookup editor, create a query with all the necessary data concatenated into a single column and specify the query as Data Source. A complete example can be found below.



Suppose we have three tables: 'employee' with a list of office employees, 'job' with employees' job titles and salaries, and 'department' with a list of office departments.


See definitions here


CREATE TABLE employee (

  EMP_NO       integer NOT NULL PRIMARY KEY,

  FIRST_NAME   varchar(15) NOT NULL,

  LAST_NAME    varchar(20) NOT NULL,

  JOB_CODE     integer NOT NULL,

  DEPT_NO      integer NOT NULL





  JOB_TITLE    varchar(25) NOT NULL,

  SALARY       real NOT NULL



CREATE TABLE department (


  DEPARTMENT  varchar(20) NOT NULL,

  HEAD_DEPT   integer,

  MNGR_NO     integer,

  BUDGET      real,

  LOCATION    integer,

  PHONE_NO    char(20)




To enable a lookup editor for the 'HEAD_DEPT' field of the 'department' table representing first name, last name, and job title of the employee, follow the steps above with the following query text:



  e.EMP_NO as ID, 


FROM employee e,

     job j




Setting a lookup editor bound to a custom value list

To create a lookup editor bound to a custom value list i.e. to a list of values that are not stored in a database table and cannot be retrieved by a query, process as follows:


Select Combobox or Radio group in the Edit Properties line in the Insert/Edit group;
Click the ellipsis button next to the "Edit properties";
Enter the list of the allowed values in pairs an_allowed_value=value_to_be_represented separated by a comma (Example: 1=One,2=Two).


By default, PHP Generator for MySQL creates lookup editors described above for columns based on enumeration data types.

Prev Return to chapter overview Next