In this article by Marc Delisle, we present mechanisms that can be used to find the data we are looking for instead of just browsing tables page-by-page and sorting them. This article covers single-table and whole database searches.

Single-Table Searches

This section describes the Search sub-page where single-table search is available.

Daily Usage of phpMyAdmin

The main use for the tool for some users is with the Search mode, for finding and updating data. For this, the phpMyAdmin team has made it possible to define which sub-page is the starting page in Table view, with the $cfg[‘DefaultTabTable’] parameter. Setting it to ‘tbl_select.php‘ defines the default sub-page to search.

With this mode, application developers can look for data in ways not expected by the interface they are building, adjusting and sometimes repairing data.

Entering the Search Sub-Page

The Search sub-page can be accessed by clicking the Search link in the Table view. This has been done here for the book table:

Selection of Display Fields

The first panel facilitates a selection of the fields to be displayed in the results:

All fields are selected by default, but we can control-click other fields to make the necessary selections. Mac users would use command-click to select / unselect the fields.

Here are the fields of interest to us in this example:

We can also specify the number of rows per page in the textbox just next to the field selection. The Add search conditions box will be explained in the Applying a WHERE Clause section later in this article.

Ordering the Results

The Display order dialog permits to specify an initial sorting order for the results to come. In this dialog, a drop-down menu contains all the table’s columns; it’s up to us to select the one on which we want to sort. By default, the sorting will be in Ascending order, but a choice of Descending order is available. It should be noted that on the results page, we can also change the sort order.

Search Criteria by Field: Query by Example

The main usage of the Search panel is to enter criteria for some fields so as to retrieve only the data in which we are interested. This is called Query by example because we give an example of what we are looking for. Our first retrieval will concern finding the book with ISBN 1-234567-89-0. We simply enter this value in the isbn box and choose the = operator:

Clicking on Go gives the results shown in the following screenshot. The four fields displayed are those selected in the Select fields dialog:

This is a standard results page. If the results ran in pages, we could navigate through them, and edit and delete data for the subset we chose during the process. Another feature of phpMyAdmin is that the fields used as the criteria are highlighted by changing the border color of the columns to better reflect their importance on the results page. It isn’t necessary to specify that the isbn column be displayed. We could have selected only the title column for display and selected the isbn column as a criterion.

Print View

We see the Print view and Print view (with full texts) links on the results page. These links produce a more formal report of the results (without the navigation interface) directly to the printer. In our case, using Print view would produce the following:

This report contains information about the server, database, time of generation, version of phpMyAdmin, version of MySQL, and SQL query used. The other link, Print view (with full texts) would print the contents of TEXT fields in its entirety.