How to search in phpMyAdmin

1. How does searching through databases and tables work in phpMyAdmin

2. Instructions for using the search function in phpMyAdmin

3. Search the database as a whole. Search multiple tables simultaneously

How does searching through databases and tables work in phpMyAdmin

Searching MySQL/MariaDB is performed using SQL queries and therefore requires knowledge of SQL.

phpMyAdmin, which could be called a web interface for MySQL/MariaDB, was created to make working with databases easier and reduce or completely eliminate the need to manually enter SQL queries.

Indeed, in phpMyAdmin you just need to click buttons, select values from drop-down lists, enter data into text fields – a typical GUI experience.

But, in fact, not everything is so simple – even in the graphical interface of phpMyAdmin, the search remains counterintuitive and requires knowledge, or at least a general understanding of SQL queries and comparison and search operators.

Let's look at an example of a search in phpMyAdmin performed by a novice user. Let's say we need to find all records containing the word “test” (case sensitive).

Click the “Search” button. We see two fields:

id
test_column

Enter the desired value in the “Value” field of the test_column field and press the “Go” button. And... nothing was found, apparently we did something wrong.

We return, repeat all the steps, but additionally from the “Operator” list, select, for example, the equal sign (=) as the most suitable for comparison and search.

And again failure.

Finally, using Google or the brute-force method, we reach the operator “LIKE %...%”, it produces at least some results:

As you can see, the search was completed, but not case sensitive. That is, values are shown that do not meet our search criteria.

You can then spend some more time searching in phpMyAdmin for a button that enables case-sensitive search. Until one day you find out that there is no such button there at all...

This example, by the way, is a good illustration of why the command line has not yet been replaced by a graphical interface. There are too many things that can be done either faster on the command line or exclusively using the command line interface.

But let's return to searching through databases and tables in phpMyAdmin.

Instructions for using the search function in phpMyAdmin

Below are detailed step-by-step instructions that will teach you:

search in phpMyAdmin for all tables of one database at once
search in phpMyAdmin by selected or only one table
search by specific columns both in one table and in all database tables at once
search for records containing as a substring the specified string of one or more words
search for records containing all specified words
search for records containing at least one specified word
search for records that exactly match the search string or number

Search the database as a whole. Search multiple tables simultaneously

In phpMyAdmin you can search both the database as a whole and individual tables.

If you click on the “Search” button on the tab with a list of tables belonging to the database, you can search through all tables and fields at once.

The string you want to search for must be entered in the “Words or values to search for (wildcard: "%")” "field.

If you already know about the percent sign (%) and underscore (_) symbols, you can use them in this field.

For details about wildcards, see the note: What is the difference between the LIKE operator and the equal sign (=) in MySQL/MariaDB. How to use LIKE in SQL

The search input must be correctly coordinated with one of the options from the “Find” list:

at least one of the words
all of the words
the exact phrase as substring
the exact phrase as whole field
as regular expression

Eg:

if you want to find a string (consisting of one or more words) as a substring in the data, then enter that string, do not use any wildcards and select the “the exact phrase as substring” option
If you want to display records containing all the searched words, then enter all these words separated by a space, do not add any wildcard characters and select the “all of the words” option
if you want to display entries containing any of the search words, then enter all these words separated by a space, do not add any wildcards and select the “at least one of the words” option
if you want to find records that exactly match a specified string or number, then enter that string or number, do not use any wildcards, and select the “the exact phrase as whole field” option

You can search all tables at once (the default), or you can select only those tables that interest you. To do this, in the list called “Inside tables”, you can select several tables to search. If you want to select several tables sequentially, then in the “Inside tables” list, click on the first one, then press and hold the Shift button and click on the last one.

If you want to select several tables that are not sequential, then click on the first one, and then press and hold the Ctrl key before clicking on each subsequent table.

The last item is “Inside column”. Here you can specify the column in which the search should be performed. If you do not specify a column, the search will be performed across all fields of the selected tables.

After clicking the “Search” button, all tables will be shown, both those in which matches were found and those in which no matches were found. Next to the table name you will see the number of matches. If there are more than zero matches, the “Browse” and “Delete” buttons are available.

The “Browse” button will display the found lines: you can view, change, copy or delete.

And the “Delete” button will immediately delete all found lines.

Single table search

To search for a specific table, click on its name in the list of tables.

Then click the “Search” button

You will see that the search interface is now radically different – now there are no options with a verbal description of the search method (as substrings or as an entire field), now you need to select “Operator”.

First, decide which column you want to search for and enter the search string in the “Value” field.

Now you need to select “Operator” and you need to do it correctly – otherwise it will turn out like in the example at the very beginning of this article.

Which Operator should be selected in phpMyAdmin:

if you want to find a string (consisting of one word or multiple words) as a substring in the data, then enter that string, do not use any wildcards and select the option “LIKE %...%”
if you want to find records that exactly match a specified string or number, then enter that string or number, do not use any wildcards, and select the “LIKE” or “=” option
if you want to find records that do NOT contain a specified string (consisting of one word or several words), then enter that string, do not use any wildcards and select the “NOT LIKE %...%” option
if you want to find records that do not exactly match (i.e. are not equal to) a specified string or number, then enter that string or number, do not use any wildcards, and select the “NOT LIKE” or “!=” option

This may all seem confusing at first, but as you gain experience, you will realize that “LIKE %...%” is almost always the best option.

In the previous section (searching the entire database), we considered the options “find records containing any of the specified words” and “find records containing all of the specified words.” So, when searching through tables, there are no such options. More precisely, you can go to “Extra options” and try to do something similar there. But, frankly, it is easier to learn the SQL query language than to understand that graphical interface.

Conclusion

The phpMyAdmin GUI has its advantages for working with MySQL and MariaDB databases. phpMyAdmin is especially useful for importing and exporting small databases or when you need to find and edit a value in a table.

This post should teach you the basics of searching databases and tables in phpMyAdmin.