We can take advantage of phpMyAdmin's Search (at the table level) or Query (at the database level) features to build a rather complex query, execute it, and then easily create a view from the results. We will see how this is done.

We mentioned that a view can be used to limit the visibility of columns (and, in fact, of tables). Let us say that the number of pages in a book is highly classified information. We open the book table, click on Search, and choose a subset of the columns that does not include the page_count column (we might have to open the Options slider).

Clicking on Go produces a results page, where we see a CREATE VIEW link in the Query results operations section. We use this link to access the view creation panel, which already has the underlying query in the AS box. We need to choose a name for this view (here, we use book_public_info), and we can optionally set different column names for it (here, we use number, title), as shown in the following screenshot:

The other options can influence the view's behavior, and have been explained in the MySQL manual (http://dev.mysql.com/doc/refman/5.5/en/create-view.html). The LOCAL CHECK OPTION clause influences the behavior of the updateable views (this is explained in the MySQL manual at the page cited previously).

Clicking on Go generates the view we asked for. At this point, the view has been created. If we refresh our browser's page and then access the marc_book database, we will see the following screenshot:

In the main panel, we see the information on the newly-created view. The number of rows for the view currently indicates ~0 (more on this in the Controlling row counting for improved performance section, later in this chapter), and View is indicated in the Type column. There is no collation or size associated with a view.

As a view has similarities with a table, its name is available along with the names of the ordinary tables. On clicking the view name, a panel similar to the one seen for tables is displayed, but with fewer menu tabs than seen in a normal table. Indeed, some operations do not make sense on a view, for example, Import. This is because a view does not actually contain data. However, other actions, such as Browse, are perfectly acceptable.

Let us browse the view shown in the following screenshot:

We notice that, in the generated SQL query, we do not see our original CREATE VIEW statement. The reason is that we are selecting from the view using a SELECT statement, hiding the fact that we are pulling data from a view. However, exporting the view's structure would show how MySQL internally stored our view:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `book_public_info` AS
select `book`.`isbn` AS `number`,`book`.`title` AS `title` from `book`;

The main panel's menu may look similar to that of a table. However, when necessary, phpMyAdmin generates the appropriate syntax for handling views.

phpMyAdmin has a configuration parameter, $cfg['MaxExactCountViews'], that controls the row-counting phase of phpMyAdmin. Sometimes, a view comprises many huge tables, and browsing it would make a large number of virtual rows appear. Therefore, the default value of 0 for this parameter ensures that no row counting happens for views. In this case, we will see rather strange results when browsing a view: Showing rows 0 - -1 (0 total, Query took 0.0006 sec). This is more acceptable than slowing down a server.

Nonetheless, if we prefer to see a more exact row count for views, we can put a larger value in this parameter, which acts as an upper limit for the row counting phase.