December 27th, 2007 - by MrSpooky

In this second installment of the Developer’s Toolbox, I’ll be sharing with you another of my favorite tools that saves me a lot of time and energy: phpMyAdmin. As comfortable as I am doing things from the command line, there are certain tasks in MySQL that are quite tedious to do by hand. Having a user interface to use makes these tasks much easier and quicker to complete.

If MySQL is not your DB of choice, there are other applications out there for interfacing the various other database platforms (such as phpMyAdmin’s relative for PostgreSQL, phpPgAdmin), but MySQL is the most widely used and phpMyAdmin is often included or offered as a part of many basic hosting plans.

As of this writing, 2.11.3 is the most current version of phpMyAdmin. It can be downloaded at the main phpMyAdmin download page. Please follow the installation instructions found in the phpMyAdmin documentation. I’m going to show you how to use phpMyAdmin to create a new database, create tables, insert records, backup your data, and import data into your database. There are many, many other things you can do with this wonderful application, but I’ll keep it simple.

Creating a new database

Now that we have phpMyAdmin installed and set up, the first thing we need to do is create a new database. Point your browser to your new phpMyAdmin install and log in using your MySQL username and password. The resulting screen should give you a dashboard overview of MySQL. Some of you using shared hosting may need to skip this step as you might only be allowed a single database that has already been created for you. To create a new database, find the “Create new database” option, type in the name of your new database in the text field below it, and click the “Create” button. You can ignore the dropdown with the value of “Collation” showing unless you have a specific character encoding you’d like to use.

Create Database

Creating a table

Now that we have our database, we need to create some tables. This is where I find phpMyAdmin saves me the most time. Since I use PostgreSQL a lot as well, I often get some of the CREATE TABLE syntax differences mixed up. Using an interface to create tables saves me a lot of grief.

On the resulting screen after creating the database, there is a small form for creating a new table, with options for the table name and number of fields. If you are coming from the main dashboard page, just click on the name of your database in the left column. In this example, I’m going to create an ‘entries’ table for my blog database. I’m going to need to store an ID number, a title, the body of the entry, and a timestamp, so I need four fields.

Create Table

After entering the name and number of fields, click the ‘Go’ button, and you’ll be presented with a very large form with four rows. Enter the name of the field in first column, select the field type in second column, and enter the field length (if needed) into the third column. At a bare minimum, that is all you need. In my example, I want my ID field to automatically increment each time I save an entry and I want to use it as the primary key in my table, so I select ‘auto_increment’ in the ‘Extra’ dropdown and click the ‘Primary Key’ radio button.

Create Table Form

Create Table Form

Once the form is completed to your satisfaction, click the ‘Save’ button to create your new table. Complete information about table creation option can be found in the MySQL manual.

Inserting Records

Now that we have some tables, let’s stick some data in there. To insert records into a table, you can either click on the name of your database in the left column, then click on the “Insert” icon corresponding to the table you wish to insert into, or click the table name in the left column and then click the “Insert” tab. Doing either will bring up a form where you can enter data.

In my example, I want to create some categories for my blog, but entering them in one at a time would be a pain. Luckily, phpMyAdmin has a second form so I can enter two records at once. It also gives me the option to save and insert again so I don’t have to navigate back to the insert form.

Insert record

Backing up your data (exporting)

Another great feature of phpMyAdmin is that it easily lets you back up an entire database with only a couple clicks. Click on the name of the database you want to back up in the left column, then click the ‘Export’ tab. The resulting screen will give you all kinds of different options, but the defaults should suffice. Make sure you check the “Save as File” checkbox, otherwise it will output all the data to your browser. If you have a fairly large database, you may want to select a “Compression” option (“zipped” will create a ZIP file). Click “Go” to generate and download your backed up data.

Export Database

Importing data

A number of web applications may come with a SQL data file that you’ll need to load into your database. A very easy way to do this in phpMyAdmin is to use the “Import” feature. Click the name of the database you want to import into in the left column, then click the “Import” tab.

All you have to do is find the SQL file on your local disk and use the Import interface to upload the file. phpMyAdmin will report any errors it encounters. You can also use the import feature to restore your database from one of the backups you’ve created.

I’ve only scratched the surface of what phpMyAdmin is capable of, but using it for even the simplest of tasks can save you time and effort in dealing with MySQL. phpMyAdmin is a great tool for both the database newbie and master DBA alike.

6 Responses to “Developer’s Toolbox: phpMyAdmin”

1 Golgotha

Good post for WordPress users. If you are a WordPress blogger you have to at least know these fundamentals of phpMyAdmin.

2 TOMAS

Nice writeup MrSpooky. I’ve had to use phpMyAdmin a couple of times when backing up my WP database and setting up WP locally on my computer. It’s definitely a nice tool to familiarize yourself with in these instances.

3 Jack @ The Tech Teapot

Couldn’t manage without phpMySql. Sure is far more productive than using the command line management tools.

4 s4l1h

Good Post Thank alot !

5 Web Design Media

phpMyAdmin is a marvelous tool to use…
There’s another tool named phpFlashAdmin what you can use.It is much easier, and the GUI looks better. However, it’s not free 🙁

6 Jonathon

I Google-searched for:
phpmyadmin import insert records into table
but could not find what I seek.

How do I import records into a table without replacing the records already in the table?

Thank you.

mulberry sale spyder womens jacket cheap new balance 574 mulberry outlet cheap new balance 574 arcteryx outlet mulberry sale spyder womens jacket mulberry sale spyder womens jacket mulberry outlet mulberry outlet new balance 574

Popular Articles

Top 10 Commentators


Subscribe to this feed! Subscribe by Email!

Random Bits Podcast

You need to download the Flash player from Adobe

Blogs Worth Reading