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.
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.
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.
Let's assume we are looking for something less precise: all books with 'cinema' in their title. First, we go back to the search page. For this type of search, we will use SQL's LIKE operator. This operator accepts wildcard characters: the % character (which matches any number of characters) and the underscore (_) character (which matches a single character). Thus we can use %cinema% to let phpMyAdmin find any substring that matches the word 'cinema'. If we left out both wildcard characters, we will get exact matches with only that single word.
Since phpMyAdmin 2.6.0, this substring matching has been made easier to access, by adding it to the Operator drop-down list. We only have to enter the word cinema and use the operator LIKE %...% to perform that match. We should avoid using this form of the LIKE operator on big tables (thousands of rows), because MySQL does not use an index for data retrieval in this case, leading to wait time that could add up to half an hour (or more). This is why this operator is not the default one in the drop-down list, even though this method of searching is commonly used on smaller tables.
In versions prior to phpMyAdmin 2.6.0, we need to manually insert the % characters to obtain '%cinema%', and use the LIKE operator from the drop-down list.
We also specify that the results be sorted (in ascending order) by title. In the search interface, only one sorting field is possible. Here is a screenshot showing how we ask for a search on cinema with the operator LIKE %...%:
The LIKE operator can be used for other types of wildcard searching, for example History%—which would search for this word at the beginning of a title. This form of the LIKE query also has the benefit of using an index, if MySQL finds one that speeds up data retrieval.
Using either of these methods of doing the query gives the following results:
Wildcard characters available are the % character (which matches any number of characters) and the underscore (_) character (which matches a single character).
We can use multiple criteria for the same query (for example, to find all English books of more than 300 pages). We see here that there are more comparison choices because of the page_count field being numeric:
Applying a WHERE Clause
Sometimes we may want to enter a search condition that is not offered in the Function list of the Query by example section; the list cannot contain every possible variation available in the language. Let's say we want to find all English or French books. For this, we can use the Add search conditions section:
The complete search expression is generated by combining the search conditions, a logical AND, and the other criteria entered in the Query by example lines.
We could have a more complex list of search conditions that would be entered in the same textbox, possibly with brackets and operators like AND or OR.
A Documentation link points to the MySQL manual, where we can see a huge choice of available functions. (Each function is applicable to a specific field type.)
Obtaining Distinct Results
Sometimes we want to avoid getting the same results more than once. For example, if we want to know in which cities we have clients, displaying each city name once is enough. Here we want to know in which languages our books are written. In the Select Fields dialog, we choose just the language field, and we check DISTINCT:
Clicking on Go produces the following:
Using DISTINCT, we only see each language once. Without this option, the row containing 'en' would have appeared three times.
Complete Database Search
In the previous examples, searching was limited to one table. This assumes knowledge of the exact table (and columns) where the necessary information might be stored.
When the data is hidden somewhere in the database or when the same data can be in various columns (for example, a title column or a description column), it is easier to use the database-search method.
We enter the Search page in the Database view for the marc_book database:
In the Word(s) or value(s) section, we enter what we want to find. The % wildcard character can prove useful here. We enter souvenirs.
In the Find section, we specify how to treat the values entered: we might need to find at least one of the words entered, all words (in no particular order), or the exact phrase (words in the same order, somewhere in a column). Another choice is to use a regular expression, which is a more complex way of doing pattern matching. We will keep the default value, at least one of the words.
We can choose the tables to restrict the search or select all tables. As we only have two (small) tables, we select them both.
As the search will be done on each row of every table selected, we might hit some time limits if the number of rows or tables is too big. Thus, this feature can be deactivated by setting $cfg['UseDbSearch'] to FALSE. (It is set to TRUE by default).
Clicking Go finds the following for us:
This is an overview of the number of matches and the relevant tables. We might get some matches in tables in which we are not interested. However, for the matches that look promising, we can Browse the results page, or we can Delete the unwanted rows.
In this article, we have covered single-table searches with query by example criteria and additional criteria specification, selecting displayed values, and ordering results. We also took a look at wildcard searches and full database search.
If you have read this article you may be interested to view :