The Search pages in the Database or Table view are intended for single-table lookups. This article by Marc Delisle, covers the multi-table Query by example (QBE) feature available in the Database view.
Many phpMyAdmin users work in the Table view, table-by-table, and thus tend to overlook the multi-table query generator, which is a wonderful feature for fine-tuning queries. The query generator is useful not only in multi-table situations but also for a single table. It enables us to specify multiple criteria for a column, a feature that the Search page in the Table view does not possess.
The examples in this article assumes that a multi-user installation of the linked-tables infrastructure has been made and that the book-copy table created during an exercise in the article on Table and Database Operations in PHP is still there in the marc_book database. To access the code used in this article Click Here.
To open the page for this feature, we go to the Database view for a specific database (the query generator supports working on only one database at a time) and click on Query.
The screenshot overleaf shows the initial QBE page. It contains the following elements:
- Criteria columns
- An interface to add criteria rows
- An interface to add criteria columns
- A table selector
- The query area
- Buttons to update or to execute the query
The initial selection includes all the tables. In this example, we assume that the linked-table infrastructure has been installed into the marc_book database. Consequently, the Field selector contains a great number of fields. For our example, we will work only with the author and book tables:
We then click Update Query. This refreshes the screen and reduces the number of fields available in the Field selector. We can always change the table choice later, using our browser's mechanism for multiple choices in drop-down menus (usually, control-click).
Three criteria columns are provided by default. This section discusses the options we have for editing their criteria. These include options for selecting fields, sorting individual columns, entering conditions for individual columns, and so on.
Field Selector: Single-Column or All Columns
The Field selector contains all individual columns for the selected tables, plus a special choice ending with an asterisk (*) for each table, which means all the fields are selected:
To display all the fields in the author table, we choose `author`.* and check the Show checkbox, without entering anything in the Sort and Criteria boxes. In our case, we select `author`.`name`, because we want to enter some criteria for the author's name.
For each selected individual column, we can specify a sort (in Ascending or Descending order) or let this line remain intact (meaning no sort). If we choose more than one sorted column, the sort will be done with a priority from left to right.
When we ask for a column to be sorted, we normally check the Show checkbox, but this is not necessary because we might want to do just the sorting operation without displaying this column.
Showing a Column
We check the Show checkbox so that we can see the column in the results. Sometimes, we may just want to apply a criterion on a column and not include it in the resulting page. Here, we add the phone column, ask for a sort on it, and choose to show both the name and phone number. We also ask for a sort on the name in ascending order. The sort will be done first by name, and then by phone number, if the names are identical. This is because the name is in a column criterion to the left of the phone column, and thus has a higher priority:
Updating the Query
At any point, we can click the Update Query button to see the progress of our generated query. We surely have to click it at least once before executing the query. For now, let's click it and see the query generated in the query area. In the following examples, we will click Update Query after each modification:
We have selected two tables, but have not yet chosen any columns from the book table, so this table is not mentioned in the generated query.
In the Criteria box, line, we can enter a condition (respecting the SQL WHERE clause's syntax) for each of the corresponding columns. By default, we have two criteria rows. To find all authors with Smith in their name, we use a LIKE criterion—LIKE '%SMITH%'—and click Update Query:
We have another line available to enter an additional criterion. Let's say we want to find the author 'Maria Sunshine' as well. This time, we use an = condition. The two condition rows will be joined by the OR operator selected by default from the left side of the interface:
To better demonstrate that the OR operator links both the criteria rows, let's now add a condition, LIKE '%8%', on the phone number:
By examining the positioning of the AND and OR operators, we can see that the first conditions are linked by an AND (because AND is chosen under the name column) and that the second row of conditions is linked to the rest by the OR operator. The condition we just added (LIKE '%8%') is not meant to find anyone, because in an exercise in Chapter 12, we changed the phone number of all authors with name 'Smith' to '444-5555'.
If we want another criterion on the same column, we just add a criteria row.
Adjusting the Number of Criteria Rows
The number of criteria rows can be changed in two ways. First, we can select the Ins checkbox under Criteria to add one criteria row (after clicking on Update Query):
As this checkbox can only add one criteria row at a time, we'll uncheck it and use instead the Add/Delete Criteria Row dialog. Here, we choose to add two rows:
Another click on the Update Query button produces the following:
We can also remove criteria rows. This can be done by choosing negative numbers in the Add/Delete Criteria Row dialog or by ticking the Del checkbox beside the rows we want to remove. Let's remove the two rows we just added because we don't need them now:
The Update Query button refreshes the page with the specified adjustment.
Adjusting the Number of Criteria Columns
We can add or delete columns by using a similar mechanism: the Ins or Del checkboxes under each column, or the Add/Delete Field Columns dialog. We already had one unused column (not shown on the previous images to save space). Here, we have added one column using the Ins checkbox located under the unused column (this time we will need it):
phpMyAdmin can generate the joins between tables in the query it builds. Let's now populate our two unused columns with the title and genre fields from our book table and see what happens when we update the query:
phpMyAdmin used its knowledge of the relations defined between the tables to generate a left join on the author_id key field. A shortcoming of the current version is that only the internal relations are examined, not the InnoDB relations.
There may be more than two tables involved in a join.
Executing the Query
Clicking the Submit Query button sends the query for execution. In phpMyAdmin version 2.11.0, there is no easy way (except by using the browser's Back button) to come back to the query generation page after we have submitted our query. One can use Bookmarks to save the generated query for later execution.
In this article, we have covered various aspects such as opening the query generator, choosing tables, entering column criteria, sorting and showing columns, and altering the number of criteria rows or columns. We also saw how to use the AND and OR operators to define relations between rows and columns, and how to use automatic joins between tables.
If you have read this article you may be interested to view :