Book Image

phpMyAdmin Starter

By : Software Freedom Conservancy Inc, Marc Delisle
Book Image

phpMyAdmin Starter

By: Software Freedom Conservancy Inc, Marc Delisle

Overview of this book

phpMyAdmin is an extremely popular, free and open source tool intended to easily handle the administration of MySQL with the use of a web browser. It can perform a variety of tasks in order to efficiently manage web databases. With this helpful step-by-step guide, we will build a solid understanding of the core capabilities of phpMyAdmin.If you are new to phpMyAdmin this is the perfect companion for getting you off to a flying start. No previous experience with phpMyAdmin is required as you will be guided through downloading and installing it on your system. After installation we will explore the use of top features and the vibrant online community surrounding phpMyAdmin.The amount of things that you can do with phpMyAdmin is overwhelming, so the quick start guide will show you how to create your very first tables. The list of resources will help you to become familiar with the phpMyAdmin community, which is a valuable part of any Open Source project. Finally, you will see how to use five key features of phpMyAdmin ñ including browsing, searching, and changing data, exporting to Excel, and creating relations between tables - that will allow you to perform simple tasks to learn the basics and set off on your own.
Table of Contents (8 chapters)

Quick start — Creating your first tables

phpMyAdmin is all about managing MySQL tables, so this section guides you in creating a database to hold your new project, then creating two tables.

The theme for the sample tables is a grocery store, with departments and items. Of course an information system for a real grocery store would contain many more tables describing employees, suppliers, and sales but two tables will suffice for our purpose.

We suppose here that your MySQL username is sarah and that this account is allowed to create databases with the prefix sarah_.

Step 1—Creating a database

A MySQL database is a container for tables. The new database will be named sarah_grocery, assuming that the system administrator enforces the policy that all databases must have the username as a prefix. The rest of the database name consists of the project name, which here is grocery.

Currently you might be seeing one existing database, information_schema. This is not a real database but contains metadata about the whole data structure.

  1. Log in to MySQL via phpMyadmin's login panel, with the username sarah.

  2. Click on the Databases menu tab.

  3. Change the database name from sarah_.... to sarah_grocery.

  4. Click on the Create button.

Step 2—Opening the database

You must now tell phpMyAdmin to use sarah_grocery as the current database. This way, all actions will take place in the context of this database. The easiest way to open it is by clicking on the database name from the navigation panel (which is located on the left-hand side if you are using a left-to-right language like English).

Step 3—Creating the first table

Now that you have a database to play with, it's time to create a table that will hold the description of the grocery's departments. To be able to create inter-table relations in a later task without the need for installing further phpMyAdmin elements, all the tables in this exercise will use the InnoDB storage engine—see and

InnoDB is a good choice for the storage engine, as it offers solid performance, transactions, referential integrity, and crash recovery.

When opening a database, you are by default in its Structure page where you can see existing tables and views belonging to this database. You now use the Create table initial dialog to specify the table name and initial number of columns:

Clicking on Go brings up the columns panel where you'll create two columns, id (an integer column) and description (a variable-size character column having a maximum length of 100). The id column is marked as being the primary key.

Scrolling to the bottom of this panel and clicking Save creates the table. You now see a different Structure panel, which shows the existing table, department, on which you can apply actions, and the Create table dialog to create further tables.

Step 4—Creating the second table

You will now use your table creation skills to create the item table, containing the following columns:

  • id (an integer and primary key)

  • dept_id (an integer)

  • description (VARCHAR 100)

  • weight (an integer)

You will notice that both tables have a column id as the primary key; however, there is no risk of confusion because SQL requires using the table name in queries, such as the following:


Step 5—Populating the tables

You need some sample data to play with these tables. There is more than one way of inserting data into a table:

  • An application that programmatically inserts data

  • An INSERT query typed in phpMyAdmin's query box

  • An import operation via phpMyAdmin's Import menu

  • An insertion via phpMyAdmin's Insert menu

In the current step you'll use the Insert menu, which can be reached from the database Structure page. Let's begin by inserting new departments; on the line for the department table, click on Insert, which brings the insertion panel. Then enter this sample data:

Clicking on Go generates the following query, sends it to the MySQL server and displays it on-screen—this is handy, both to reassure you about the action done and possibly to teach you SQL.

INSERT INTO `sarah_grocery`.`department` (`id` ,`description`
'1', 'Baby foods'
), (
'2', 'Frozen foods'

You need to also populate the item table. Here you create two items in the frozen foods department: