phpMyAdmin examples

Last updated: 5 April 2021

This article looks at a few practical phpMyAdmin use-cases. If you are new to phpMyAdmin you might want to read our phpMyAdmin overview first.

All three examples use a WordPress database, which is used by the majority of our customers. The first example is WordPress-specific but the other two examples should be useful for any database:

• Reset a WordPress password
• Finding a string in a table
• Find and replace a string in a table

Reset a WordPress password

If you have lost the password for your WordPress dashboard then you need to reset it. You can do so via phpMyAdmin:

• Select the database and the users table in the left-hand pane
• Find your user account in the table and click the Edit link

Image: editing the users table.

Your user password is stored in the user_pass row. The string of characters you see in the Value column is not your actual password – it is a hashed version of the password (you don’t want to store passwords in a database in plain text!).

You can reset the password as follows:

• Enter the new password in the Value field
• Select MD5 from the Function column
• Click the Go button

Image: changing the password via the MD5 function.

The new password is automagically converted to a string with random characters and you should now be able to log in to the WordPress dashboard again.

Finding a string in a table

Finding a specific value in a database doesn’t have to be akin to searching for a needle in a haystack. Using phpMyAdmin you can quickly find a value in one or more tables.

Let’s imaging that you got a WordPress plugin such as WP Limit Login Attempts installed and that you have accidentally entered your WordPress user credentials incorrectly a couple of times. The WordPress install has now locked you out and you have to wait 20 minutes before you can try to log in again.

If you don’t want to wait that long you can unblock yourself. The plugin blocks your IP address, so you could search the database for your IP address and then remove the rows that are responsible for the block!

If you don’t know your IP address, you can quickly find it via findip.co.

Searching for a string

You can search for your IP address as follows:

• In phpMyAdmin, select your database in the left-hand pane
• Select the Search tab from the navigation bar
• In the Words or values to search for field, enter your IP address
• Click on Select all to search for the string in all database tables
• Click on the Go button

Image: searching all tables in the database for the string 81.104.41.211.

In our case we are looking for the IP address 81.104.41.211. That string appears four times in the wp2a_options table. You can view the entries by clicking on the Browse link.

Image: The search results, with a link to browse the tables that matched our search string.

Deleting rows

By looking at the values in the option_name column you can tell that these rows are related to our failed login attempts. You can deleted all the rows by ticking the Check all button and then the Delete button.

Image: removing the rows that are responsible for the IP block.

And that’s it – you can now try to log in to the WordPress dashboard again.

Find and replace a string in a table

The final example shows how to search and replace a string. We got an SSL certificate for our WordPress website, example.net, and have changed the website URL to https://example.net via the WordPress dashboard. However, there are a few links on the website that still point to http://example.net, and we want to change those links to https://example.net.

For WordPress websites you should only need to update the posts table. There may be matches in the options table as well, but doing a search and replace on that table can have unexpected results. As always, do make a backup of the database before you start doing work on it.

Searching a database table

Before we replace anything we will first do a search for the string http://example.net. As the string can appear anywhere in a field (at the start, somewhere in the middle or at the end) we need to use a special operator.

• Select the database and options table from the left-hand pane
• Select the Search from the main navigation
• On the post_content row, change the Operator to LIKE %…%
• Enter the value you want to find – in this case http://example.net
• Click on the Go button

Image: searching for the value ‘http://example.net’ in the ‘post_content’ field.

The LIKE %…% operator uses the percentage-sign wildcard. With that operator phpMyAdmin looks for the string anywhere in the post_content field. In our case, it finds just one match:

Image: we got a match!

Replacing a string

As there is only one match you can manually change the link. However, let’s do a “proper” search and replace anyway. To do so, select the Find and replace tab (just below the navigation). Here, you can simple enter the string you want to find and the string it should be replaced with:

Image: replacing the string ‘http://example.net’ with ‘https://example.net’.

A nice feature of phpMyAdmin is that it shows you a preview of the changes that are going to be made. In our example everything looks correct – phpMyAdmin again found one row and you can see that the change that will be made is exactly the change we want. To confirm the changes you can simply click on Replace.

Image: a preview of the changes that will be made.

Note: Executing a find and replace query on a database is potentially damaging, in particular on a ‘live’ database. Always make a backup before you run such queries, and consider if there are alternatives. For instance, a better way to change URLs from HTTP to HTTPS in WordPress would be to use a WordPress plugin such as Really Simple SSL.