Category: Data and Databases

Some database tools – phpMyAdmin and HeidiSQL

In my last post I talked about dealing with a .sql file from the command line. This worked well, but there are other ways to interact with your databases that are GUI based, and perhaps easier for someone accustomed to dealing with the GUI interface. The two that I am currently playing with are PHPMyAdmin and Heidi SQL. Both are free to download and use, keeping with my theme of using free tools and utilities whenever I possibly can.

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web. Currently it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges,export data into various formats and is available in 52 languages.

HeidiSQL is an easy-to-use interface and a "working-horse" for web-developers using the popular MySQL-Database. It allows you to manage and browse your databases and tables from an intuitive Windows® interface.

With HeidiSQL you will also be able to
* generate nice SQL-exports
* synchronize tables between two databases
* manage user-privileges
* import text-files
* export table-data as CSV, HTML and XML
* browse and edit table-data using a comfortable grid
* batch-insert ascii or binary files into tables
* write queries with syntax-highlighting (next version will have also code-completion)
* monitor and kill client-processes
* and much more

I have worked with phpMyAdmin before on the web but now have it installed on my laptop along with the Apache Server, MySQL and PHP. I have never used Heidi SQL before so it will be interesting to see what these tools have to offer in terms of working with and manipulating databases.

As I wrote in response to Bo’s comment to my tutorial on working with .sql dump files, phpMyAdmin has been known to fail when importing larger databases, and this is where the command line comes in handy. For some, working from the command line is just preferable to using a GUI. However, not all hosts allow you to have shell access to your account, so it’s a good thing to get accustomed to using both the command line and GUI tools.

One thing about installing phpMyAdmin with PHP5 is that it’s a bit of a chore getting it to talk to MySQL. As I discovered after a lot of head banging, googling, pounding my keyboard, and a swearing, this is primarily a PHP installation problem.

Since v.5 onwards, PHP does not come inbuilt with support for MySQL as a default anymore, therefore if you have trouble connecting to MySQL from phpMyAdmin at your initial installation, chances are that the problem lies in the fact that your PHP is not talking to MySQL. There’s a lot of information in different forums about this, and google is your best friend.

My solution was to delete my manual installation of PHP, and install PHP5 using the msi installer from php.net with its default settings. If you enable all the extensions you will have trouble since some of them require other software to be installed. Your best bet is to install it as it is (selecting your version of the server of course), and then add extensions. You do this by going to “Add/Remove Programs” in Control Panel, choose Change the installation, and enable MySQL, MySQLi, Mcrypt, and Multi-Byte String to run from the hard drive. This step ensures that you don’t need to edit your php.ini file as suggested in many forums, and which you would need to do if you were installing manually. You should also add the extension folder (usually C:/Program Files/PHP/ext) to the PATH. While you are there double check that your installation folder (C:/Program Files/PHP/) is also in the path. Remember that each time you make a change to your PHP install you need to restart your server (Apache in my case) to load the new configuration. And whenever you make changes to your PATH variables, ideally you need to restart your computer for the changes to take effect.

You can test if your PHP install is talking to MySQL by creating and running a PHP file such as the one below, provided kindly to me by Bo.

mysql_connect("localhost", "mysql_username", "mysql_password") or die(mysql_error());
echo "MySQL connection successful.
";
mysql_select_db("mysql_db_name") or die(mysql_error());
echo "Database connection successful.";
?>

(You will need to insert your own values for “mysql_username”, “mysql_password” and “mysql_db_name”).

If your install was successful, you should see something like

MySQL connection successful.
Database connection successful.

After PHP is installed correctly and works with MySQL, you can then install (or run) phpMyAdmin following the instructions given on the web page.

Now that I have finally successfully installed phpMyAdmin, I have all the tools that I need to start doing some serious stuff, and I am going to start writing some code in PHP and also interacting with databases using the three tools that I have at my disposal – phpMyAdmin, Heidi SQL, and the good old command line.

Working with a .sql dump file – a newbie tutorial

I have some .sql dump files that I wanted to open and play with. After googling and doodling, I finally accepted that I was going to have to overcome my innate need for orderly progression and jump into doing stuff with mySQL before actually getting to that chapter in the book!!

So here are the steps I used. I have mySQL Server 5.0 installed on my laptop, and I will go step by step to describe what I did. You will need to use your directory path and file names to do this yourself.

My sql dump file was named directory.sql and was in a folder on my desktop. My mySQL install is under C:\Program Files\MySQL\MySQL Server 5.0. My ultimate goal is to get the data in the sql dump file into csv format. I know there are probably easier ways to do it, and many tools, but I figured going step by step through the method I am going to describe was a good way to entertain myself and learn some MySQL in the process.

So let’s do it:

1. First, of course, I got myself a cold coke.

2. I opened the mySQL command line and entered my root password. I have found that it’s easier to actually work from the Command Line Client in the mySQL menu on my Start menu than it is to actually open the command line and try to navigate to the directory where mySQL is installed.

Start -> All Programs -> mySQL -> mySQL Server 5.0 -> MySQL Command Line Client

If you have set root password, which you always should, the client will ask for your password and then proceed to the mysql> prompt.

2. Next step was to create the table that the data in my sql file would go into. To do this, I first opened the sql dump file in Wordpad and examined it to determine what to name the table. Don’t use notepad for this because it will be much harder to read. If you have Textpad installed it’s even better, but I used Wordpad. I then typed in the command prompt:

mysql> CREATE DATABASE MaryDIR;

3. If this is done correctly with no typos, you will get a success statement to the effect:

Query OK, 1 row affected <0.00 sec>

4. Switch to the database you just created using the USE command.

mysql> USE MaryDir;

You should see the text

Database Changed

5. So far so good. Next step is to determine if your sql file already has the statements to create the tables you need or if you have to do it yourself. In my case, the statements were already there, so it was simply a matter of invoking the sql file to create and populate the tables. For this you use the SOURCE command. You can make it easy and move your sql file to your mySQL directory, but to make it interesting, let’s leave the sql file on the desktop and specify the path:

mysql> SOURCE C:\Documents and Settings\Mueni\Desktop\directory\directory.sql;

Remember you need to replace these paths with your own path and filename.

You will then see on the screen a series of lines as mySQL executes the queries to create and populate your tables.

You can see the tables that have been created using:

mysql> SHOW TABLES;

Once the tables are listed, you can check the details of any particular table using the DESCRIBE command,. So for one of my tables named caterers I typed:

mysql> DESCRIBE Caterers;

So I now have a mySQL database that I can use. The mySQL databases are created, by default, in the data directory. So under C:\Program Files\MySQL\MySQL Server 5.0\data there is a folder titled MaryDir.

If you want a sample file to play with you can download one from the mySQL website here: http://dev.mysql.com/doc/

Next I will explore different ways to view and interact with the database I just created.