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.