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.
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.
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.
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.
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.
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.
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.