phpMyAdmin: Visual interface to manage databases
Here we go again, another technical term… After reading about databases, what is phpMyAdmin?
phpMyAdmin is a free software tool acting as a visual interface to manage your databases. You can access this interface via your browser to create tables within the database, add new users, change a specific data or export all data for example.
The role of phpMyAdmin is to simplify your life. You can imagine the database as a big file and if you add/search/change/remove/etc. a data, it would be a big challenge within the enormous quantity of data. PhpMyAdmin solves all of these challenges by offering a graphical interface to manipulate all the information. The interface is very easy and practical, and provides many help messages to clarify how to proceed. It’s much more comfortable to visualize what you’re doing.
Related to the database objects you can execute tasks like:
- Browse databases, tables, views, fields, etc.,
- Create, copy, drop (remove), rename and modify databases, tables, fields…,
- Edit data on database tables,
- Execute any SQL action, including actions for data modification like Delete, Update and Insert.
PhpMyAdmin is often offered as a package if you take an admin panel with your hosting.
To get started, what does phpMyAdmin look like and how do we use it?
The screenshot below shows you the homepage of the phpMyAdmin interface:
On the left side of the screen you can see all existing databases which are on your server. You can click on the “+” sign on each database and this database will be expanded, showing you all the tables created in it as on the following image:
In the image above you can see that the database selected is “Opengis”. Then all the tables in “Opengis” are shown, and the table “world_borders” is selected and expanded. Then inside the table “world_borders” the columns are shown: FIPS, GEOG are the first two columns. There is also a “New” button to create a new column for the selected table. After the column names, the existing indexes of the table are shown. This is an example of an hierarchical tree of a database.
If you have a closer look, you can see other “New” buttons. The one on the top of the image is for creating a new database. And the one just below “Opengis” is for creating a new table within “Opengis”.
On the right hand side of the main screen you can now see a menu at the top, showing options like: Browse, Structure, SQL, Search, Import, Export, Privileges.
A few more details about some of these options:
The “Browse” option allows you to see the content of a database or a table, depending on the object (a database or a table) you selected before pressing the button “Browse”. Let’s first see the behavior of “Browse” if you have clicked on a specific table. We will see a table called “user” with 4 columns as an example: username, password, email and creation date. Let’s see the page when we press “Browse” when the table “users” is selected.
As seen on the image above, the Browse option shows 4 existing records in the table “users” with the values of each column. As you can see there are four users with their username, their password, their email, and the date on which they registered. Take a look at the data values, as we will now see how to search for records in a table.
The “Search” tab allows you to define your search criteria. It is a very useful option for SQL beginners. This search page allows you to easily process some search within your database and tables. In our example it will be within the “users” table.
For example one query could be the following: “all users who created an account in the year 2015”. Another one could be: “all users with ‘myapple.com’ in their email address”. Assuming you want all records which have “myapple.com” in their email. You would just have to enter “myapple.com” within the email row in the “values” column, because you are going to search all the emails who have for value something with “myapple.com” in it. You also have to use an operator or condition. Here are the most common ones:
- “=”, that means you are looking for something which has for value the exact string that you enter in the column value
- “LIKE % %” or “!=”. The “LIKE % %” is used for substring verification. In our example we are looking for the users who have “myapple.com” in their email. So we are looking for users with emails like email@example.com, as well as emails like firstname.lastname@example.org, and so all the emails that contain “myapple.com” and not just end with “myapple.com”. To inform phpMyAdmin about this “contain” condition, we use %%. So you can use the LIKE %% operator. It will apply a query like this: select all the users who have an email LIKE %myapple.com% (with %% meaning “in it”)
- LIKE, is like “LIKE %%”, but more flexible. For example you can use the operator LIKE and enter in the value field %myapple.com. The query would be: select all the users who have an email that ENDS WITH myapple.com (or that are LIKE myapple.com AT THE END). If you put the % at the end of your value, like myapple.com% that would be all the emails that begin with myapple.com. If you don’t put any %, it would be the same than the “=” operator.
To execute the Search please press the GO button, then you will see a page like the following one with the two records matching the search criteria.
The shown records in the above image are real data stored in the database, and you (if you have the right privileges as a user) can change or delete these records if needed. Let’s assume a user of our website entered a wrong email address and we want to fix this issue. We can fix it by using the Edit function on the “Browse” option of phpMyAdmin. As an example, we will change the email of user “ignacio”.
Once you click on the Edit button the following window appears, and you can edit any value. In our case we want to overwrite the wrong email value “email@example.com” with the correct value “firstname.lastname@example.org”. Please press GO.
Then we type the correct email value and press GO as on the image below and we’re done.
Most of the times, not to say always, a website allows you to make this kind of changes from its admin panel. However it is always useful to know how it works because you may need to do that directly from the database one day, for example because you, as an admin of your website, have lost your password and you even don’t remember your email! This way you can go to your database, and search for your email using the LIKE %% operator, and change your email and password directly within the database.
Further options in phpMyAdmin are Structure, SQL as well as Export and Import.
The “Structure” option shows you the outline of the current table with column names, data types and lengths. You can also take actions like drop column, change column, or even create a primary key or an index for the column. Here is an example of how to drop a column:
As on the image above, check the column you want to drop (in our case the column “city”) and then click on DROP. A pop-up appears to confirm if you really want to do this. Click OK and see the result on the image below (no more “city” column).
Note that, if you are just one level up, at the database level, the “structure” tab would display all the tables, and you would be able to execute quite the same functions but on the tables directly, like dropping a table for example.
The “SQL” option opens a window where you can execute any SQL query. These are buttons with SQL templates focusing on people who don’t have any SQL knowledge. Assuming you wish to execute an insert SQL command, by pressing the “Insert” button, you will have a partially written SQL. You only need to complete.
We will see the basic understandings of SQL request in the next article.
“Export” and “Import” options can be useful when you have to switch servers or when you want to export the list of all the users of your website and your website admin panel is not providing this option. Yes you can do that directly from your database!
As an example, let’s see how to export data from a specific table: In the left sidebar, click on the table you want to export (in our example the table “airport”), and then on the Export tab. The following page will appear:
To simplify, use the SQL format and click on GO. A pop-up will appear asking you to open or save the document. Save it in your related folder.
Let’s now have a look at how to add a user to an existing table.
First, click on the table to which you want to add a user, then click on “Privileges” in the main menu. In our example, we want to add a user to the table “airport” within the database “airplane”, as shown on the image below (“airplane.airport”, where “airplane” indicates the database and “airport” the table).
On the page above you can add a user by clicking on the link “Add user account” at the bottom of the list of all registered users.
A new page appears: Add the user information (name, host – in our cast localhost, password) with no global privileges as we create this user specifically for our table “airport” (and we will see that specific part in just a few minutes in another example, so that you know how to do this also when a user already exists). Finalize the registration process by clicking on the GO button at the bottom of this page.
Here we go, our user “Pilote” is now added – see image below.
Now we still need to learn how to link a user to a table. How can we do so? Within the User accounts section, edit the privileges for the specific user you want to link. In this example we want to give an existing user called “Test_user” all privileges (just for information as you can see this on the image below, we also just added this one as in the previous example). To give all privileges, please check all privileges for the database as on the image below and then click on the GO button.
On top, change from the button Databases to Tables within this specific user account: This will add the privileges for this specific table to the user (in our case all privileges). Then click on GO.
Well done, in our example, the user “Test_user” can now manage this specific table.
Of course there are much more functions available in phpMyAdmin. This course gives you a first general overview. In summary, phpMyAdmin is a useful tool which facilitates your life when dealing with database management. Just try it out!
If you want to test some functions before dealing with your own database, just visit the official site of phpMyAdmin and click on “Try demo” on the right hand side. A demo phpMyAdmin will be opened, ready for your first tests. Have fun! 🙂