Using Custom View

WARNING! Custom View expects simple SELECT statements only, they are NOT designed to and cannot replace views provided by the database. PHPMaker supports creating database view, you should always use database views whenever available. Views provided by database allow you to use them more like regular tables. If you use MySQL 5, you should ALWAYS use MySQL view.

A Custom View is basically a stored SELECT statement. Custom View allows you to save your SELECT statements in a project as a virtual table. If your database, for example, MySQL 4, does not support views, you'll find this feature useful.

Converting Custom View to Database View

Custom View expects a simple SELECT statement only. If you create a Custom View with a SELECT statement with some complex clauses, or with an UNION statement instead of a single SELECT statement, the sorting or searching may fail. In such cases you should change the Custom View to database view. PHPMaker allows converting your Custom View to database view provided that the database user have CREATE VIEW privilege.

After loading the database in PHPMaker, the database objects (tables, views, custom views and reports) will be shown in the left pane (the database pane). To convert a Custom View to database view, right click the Custom View in the database pane and select [Convert Custom View to View]. Alternatively, you can select the Custom View first, then click [Edit] in the main menu and then select [Convert Custom View to View]. After creating the view, the existing Custom View and field settings will be moved to the new view. If your project is connected to a development database, export the new views and recreate them on your production database during deployment.

If under some circumstances you cannot change Custom View to database view (e.g. you are still using MySQL 4.1) and you must use Custom View, you can try use a derived table (supported since MySQL 4.1) in the format of SELECT * FROM (<Original SQL>) AS t as the SQL of the Custom View. (Note for MySQL 4.1 users only: If you upgrade to MySQL 5 later, you should convert your Custom Views to MySQL views. If you use the derived table approach before, edit the Custom View, remove the derived table and use the original SQL first, MySQL 5 still does not support subquery in the FROM clause when creating view.)

Creating Custom View

To create a custom view, right click the database pane and select [Add Custom View]. Alternatively, you can click [Edit] in the main menu and then select [Add Custom View].

The Custom View Setup window will show up:

PHPMaker will give the new Custom View a temporary name, CustomView<n>, where n is an integer. If you want to change the name, enter a new name in the [Custom View name] edit box in the main toolbar.

It is recommended that the built-in visual query builder be used to build your SQL. The query builder interface is intuitive and fully supports drag-n-drop, in most cases you don't need to type the table or field names.

On the left hand side, the available tables are displayed in the table pane. On the right hand side, there are three tabs: [Builder], [SQL] and [Result]. (The [Result] tab will only be visible after you execute the SQL.)

Builder

To build your SQL, drag your tables from the table pane on the left to the builder area and check the required fields.

To create a link between two objects (i.e. join them) you should select the field by which you want to link an object with another and drag it to the corresponding field of another object. After you finish dragging, a line will appear between the linked fields. The join type created by default is INNER JOIN, i.e. only matching records of both tables will be included in resulting dataset. To define other types of joins you should right click the link and select the Edit... item from the drop down menu or simply double-click it to open the Link Properties dialog. This dialog allows you to define join type and other link properties.

The easiest way to add a field to the list of query output fields is to check the checkbox at the left of field name in the Query Building Area. To include all the fields of an object you should click the checkbox at the left of the asterisk item of an object.

Note You should select your fields explicitly, not using the * symbol.

Another way is to select a field name from the drop-down list of the Expression column in the Columns Pane. And the most common way is to write any valid expression to the Expression column in the Columns Pane.

To remove a field from the list of query output fields you should uncheck the checkbox at the left of field name in the Query Building Area or you may remove it by unchecking the Output column checkbox. Such operations as removing lines from the Columns Pane or re-ordering output fields are available by right clicking on the left-most gray column via the drop-down menu.

To define sorting of output query fields you should use the Sort Type and Sort Order columns of the Columns Pane.

The Sort Type column allows you to specify how the fields will be sorted - in Ascending or Descending order.

The Sort Order column allows you to setup the order in which fields will be sorted, if there are more than one field to sort specified.

To cancel sorting by some field you should clear the Sort Type column for this field.

To define criteria for the expression listed in the Columns Pane you must use the Criteria column.

Here you should write the criteria omitting the expression itself. For get the following criteria in your query

WHERE (field >= 10) AND (field <= 20)

you should write

>= 10 AND <= 20

in the Criteria column.

You may specify several criteria for one expression using the Or... columns. These criteria will be concatenated in the query with the OR operator.

To setup grouping by some of the fields and/or to define aggregate functions on grouped rows you may use the Group by column.

You may select one of the following values for this column from the drop-down list:

"Expression" and "Where": These values are used when no grouping is specified. The "Expression" value is set when this expression is used as output expression in the SELECT clause and nothing else. The "Where" value is set automatically when you define a criteria to this expression that results in including this expression to the WHERE clause. Normally you shouldn't care about value of the Group By column when you don't want to define grouping.
"Group by" and "Having": These values are similar to the previous two, but used when you want to define grouping in your query. In this case you should set the "Group by" value for all columns you want to group by. Specifying criteria for the grouped columns will include these criteria in the HAVING clause. If you want to include an expression ONLY in the HAVING clause you should set the "Having" value in the Group By column for this expression.
Aggregate functions (Count, Sum, etc): By selecting one of these values you will create an aggregate expression for the value indicated in the Expression column.

SQL

You can always click the [SQL] tab to check the SQL generated by the query builder, the SQL editor is also syntax-highlighted to enhance the readability of the SQL.

The SQL tab is actually an SQL editor, you can type your SQL directly without using the query builder here, or paste your SQL from elsewhere, or open a saved SQL script (*.sql) from file.

Notes

If the SQL is not generated by the built-in query builder, there are chances that query builder cannot parse the SQL and display it visually in the [Builder] tab. However, this does not necessarily mean that the SQL is invalid, you can verify its validity by executing it (see below) and check if it returns the data you want in the [Result] tab. And you can create the Custom View directly by click the OK button without switching to the [Builder] tab.
If you type your SQL directly, always use aliases (use "AS") for fields whenever applicable. Otherwise, the alias will be assigned by the database and you will not be able to refer to the field with a known and meaningful name.

Result

When you have finished your SQL, you can test the SQL by any one of the following ways: (for "DIRECT" connection only)

Clicking the [Execute] button in the toolbar
Pressing [F9] on your keyboard
Clicking [Query] in the menu and then [Execute]

The [Result] tab will become visible and display the result data. If the data is correct, the Custom View setup is almost done.

Note: Executing the SQL is for testing the SQL only. The data displayed in the [Result] tab will not show the memo fields and blob fields, they may be represented by "(MEMO)" and "(BLOB)" only. This is not related to the data that will be displayed by the generated scripts.

Since a Custom View is based on an existing table, there is an option that the fields in the Custom View use the same Field Setup (Edit Tags, View Tags, etc.) as the source table. If you want to copy field settings, check the checkbox [Copy field settings from source table (when applicable)]; if not, uncheck it. Then you can press [OK] to finish.

Note: "when applicable" means that if enabled, field settings will be copied from the source table when a new Custom View is created, or when a new field is found after editing a Custom View, and the field can be found from existing tables. (This is also why building a SQL with the built-in query builder and not using * symbol are recommended, these ensure that the SQL can be parsed and therefore the source table and source field can be found.)

When a Custom View is added, PHPMaker will load it and display it in the database pane and Table Setup page under the [Custom Views] node. To view the SELECT statement of the Custom View, right click the Custom View in database pane and select [Object Properties]. Alternatively, you can select the Custom View first, then click [Project] in the main menu and then select [Object Properties].

To edit a custom view, right click the Custom View in the database pane and select [Edit Custom View]. Alternatively, you can select the Custom View first, then click [Edit] in the main menu and then select [Edit Custom View].

When a Custom View is added or edited, PHPMaker will check the SQL. If the SQL is invalid, the icon of the Custom View will have a cross on it like , you can view the error in the [Object Properties] window and then edit the Custom View to correct the SQL.

Important

After a Custom View is created, it works independently from the table(s) it based on. Changing the structure of the source table(s) does not change the Custom View. If you delete a field in the source table that is used by a Custom View, the Custom View will fail. You should edit the Custom View to update the SELECT statement. Also, a Custom View has its own field settings, changing field settings in the source table does not change the field settings in related Custom Views.
During searching, sorting and grouping, PHPMaker generated codes need to change the WHERE and/or ORDER BY clause of SQL for the list page dynamically. For Custom Views, since there might be fields with aliases, it is necessary to use the actual expressions of the aliases in order to ensure the result SQL works. For example, if you have a field, "UnitPrice*12 AS TotalPrice" in your SQL, you cannot sort using "ORDER BY TotalPrice", you need to use "ORDER BY UnitPrice*12". Therefore, if your Custom View contain fields with aliases, it is important that the SQL can be parsed correctly by PHPMaker. To make sure that, here are the recaps:
1. It is recommended that the built-in visual query builder be used to build your SQL.
2. You should select your fields explicitly, not using the * symbol.
3. If you type your SQL directly, always use aliases (use "AS") for fields whenever applicable.