Data Management

Creating and managing a Mysql database with phpMyAdmin

phpMyAdmin is a graphical user interface (GUI) developed in PHP, for creating and managing MySQL databases.

phpMyAdmin is a HTML interface developed in PHP, for creating and managing MySQL databases.

Before you download and install the latest version, have a look at the requirements section on the phpMyAdmin Wiki.

The Interface

The screenshot of the interface is displayed below. The left-hand side of the screen contains the following icons; home, log out, query window (for executing SQL queries), phpMyAdmin documentation and the link to the MySQL Reference Manual. Below the icons is a Databases drop-down menu, that allows you to jump to the database of your choice. The tables will be displayed below the database you selected.

Setting up a User account and privileges

The root account has all privileges and should be used for administration purposes only. For security reasons, we are going to create a different user account with restricted access. On the home page click Privileges and then click Add a new User.

The password field is optional. You can chose a password yourself or have one generated for you. We are giving the user basic access, therefore we're only ticking the checkboxes that will give them basic rights. For instance, they will not be able to delete a database. Click Go to set the privileges. You can apply privileges to databases and tables and columns within a database.

Create a Database

Let's now create a database and call it book_shop. Type in the name into the Create New database field and click Create.

The equivalent SQL command will be displayed as well, as shown below.

Create a Table

You should then be prompted to create a table in the database. We're going to create a table called books and enter 4 in the number of fields. Enter the following fields: ISBN, Author, Title and Price.

Next, set the data types and length/values as show below. The ISBN will serve as the primary key.

To set the primary key, choose the database and then the table and click on the primary icon as shown below.

Insert Data into the Table

To insert data into the table, select book_shop from the drop-down on the left-hand side and then the books table below it. Click on the Insert tab and continue to fill in the different fields with values, as shown below.

Managing your database

To view and manage your database, select it in the drop-down on the left-hand side. You will get an overview of your database and nine tabs for carrying out certain actions. Below is a description of the different tabs.

|> Structure: displays the different tables in the database and allows you to create a new table. A small drop-down menu, lets you perform the following on table/s you have selected; empty, drop, print view, check, optimise, repair and analyse. The Action section provides the following options: browse, search, display the structure, insert a record, empty or drop a table.
|> SQL: lets you execute SQL queries on the database.
|> Search: lets you search for words and phrases in the database.
|> Query: allows queries to be executed using the interface components and manual SQL queries to be run.
|> Export: allows you to save your database into a file format of your choice. CSV, Excel, Word, LaTex, PDF, SQL and XML are some of the formats available.
|> Import: import an SQL file.
|> Operation allows you to create a new table, rename and copy the database.
|> Privileges: shows what users have access to the current database and their rights. You also have an edit option next to each user.
|> Drop: lets you delete the database if you need to.

phpMyAdmin is a handy, graphical tool for creating and managing MySQL databases. The interface is straight-forward and easy to learn and it allows users to execute SQL queries manually. It is also open source, so you can download and use it for free.

Editor's Picks

Free Newsletters, In your Inbox