Custom View Setup

Custom View allows you to save your SELECT statements in a project and use the results like normal tables. If your database, for example, MySQL 4, does not support views, you'll find this feature extremely useful. Even if your database support views, you'll also find it handy as you can create different versions of PHP pages with Custom Views for the same table/view in a single project.

A Custom View is basically a SELECT statement. Custom Views give you an option to save your SELECT statements right within the PHP Report Maker interface, they are, however, not to replace views provided by the database. Views provided by database allow you to use them like regular tables, though views are usually not updatable. Also, query builder provided by database itself ensures that the generated SQL is 100% compatible with the database. You should always use views provided by your database whenever available. If you use MySQL 5, always use views provided by your database.

After loading the database in PHP Report Maker, the database objects (tables, views, custom views and reports) will be shown in the left pane (the database pane). 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:

PHP Report Maker 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.

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

To create joins, drag a field from one table to its linked field in an other table.

Note: Always drag from the main table to its detail table or lookup table. Visually, it usually means you should drag from a table with more links to an other table with less or no links. Reversing the dragging direction may result in a different SQL not compatible with your database.

If you need to add criteria (WHERE clauses), click the small [...] button on the [Criteria] tab and select [Add condition]. A blank condition will appear, you can enter a field to the left side by any of the following ways:

• Drag a field from its table in the builder area, or
• Click the left side of the condition, right click and select [Insert field], or
• Click the left side of the condition, press Shift+Enter

Then you can enter your condition to the right side. Repeat for more conditions. You can make composite conditions (using AND, OR, etc. for conditions) by clicking the number button of an existing condition and then [Make composite condition] to add another condition.

In the [Selection] tab, you can choose to use DISTINCT, change aliases, add aggregates (and hence a GROUP BY clause) for fields.

In the [Sorting], you can build your ORDER BY clause easily.

When necessary, you can click [View] and then [Query Builder Options...] to enable the [Grouping Criteria] tab which allowing you to build a HAVING clause for your SQL. There are many other options for the query builder in the options dialog box, but usually you don't need to change them.

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.

Note:

1. 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.
   
   
2. 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:

• 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.

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 (e.g. View Tag settings) 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 correctly and therefore the source table and source field can be found.)

When a Custom View is added, PHP Report Maker 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, PHP Report Maker 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 Notes on Custom Views

1. 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.
   
   
2. During searching, sorting and grouping, PHP Report Maker 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 PHP Report Maker. 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.