MySQL Query Browser and Hosted Databases

Does your website rely on a MySQL database? Are you tired of using a web application my phpMyAdmin in order to run some custom queries and accomplish some simple tasks?

I used to hate using phpMyAdmin to accomplish simple tasks like querying a database, creating a table or doing some basic maintenance work. However, these regular events have become quite simple thanks to the Microsoft Query Analyzer-like application for MySQL, Query Browser.

Query Browser is a great little application available for Windows, Linux and Mac OSX. I’ve been using the program for a while now on both Windows and Linux platforms and it hasn’t disappointed yet. The advantages of using Query Browser rather than the web-based phpMyAdmin are plenty, so here are some of the key points that matter to me:

• Cleaner Interface – Query Browser, at one time, was considered a joke due to it’s poor user interface. However, those days are in the past as you can see in the screen shot here. The query window is separate from the results and, like Query Analyzer, colour coded keywords make quick code debugging possible. There’s nothing worse than typing a complex query too quickly and then seeing that you entered “SLEECT” rather than “SELECT”.
• Great Scripting Interface – Interfaces are very important, as they have the power to keep someone interested in a product, or make something appear so cheap that we look for alternatives. What I really like about this interface is the error-checking that goes on. Should a particular line bomb for any reason, the application will take you to that line and highlight it. This should save people quite a bit of time in the long run if they plan on creating and maintaining complex queries and functions.
• Easier for New Users – People who are not familiar with databases or how to write queries can now drag and drop their criteria into the query window. After a few hours or days of doing this, even the most reluctant of people would see enough of the basic MySQL syntax to know what to type and in what order. This is no excuse for not learning how to work with a database directly, of course, but anything that makes it easier can only be a good thing.
• Query Browser has Very Helpful Inline Help – Without an easy to use help system, most people would give up on directly accessing a database. While it can seem a daunting task, the more skilled we are with all aspects of our websites (or other applications), the more likely we can solve our own problems as they crop up. Tabs in Query Browser’s help panel allow us to choose from Syntax, Functions or Parameters. Given the sheer number of options with database commands, this is a unique innovation that will likely become part of future query interfaces in competing products.

All in all, the MySQL team has certainly come a long way from what they offered back in 2001 when I started working with the product. There are some areas for improvement, of course, but that could be said about any application. I’d really like to see some more customization options so that I can reposition the elements of the screen to what I’ve come to expect and enjoy with Microsoft’s Query Analyzer. However, when it comes right down to it, I can’t complain too much.

Connecting to a Hosted MySQL Database

So, if MySQL Query Browser is so great, how do we connect to our website’s databases with it? Believe it or not, this is remarkably simple.

If you can manage your MySQL databases through a cPanel account, you will first need to add your computer’s internet IP to the “Access Hosts” section of the Manage Databases tab.

From here, you simply add your computer’s internet address (you can use a % as a wild card if you have multiple sequential IPs for any reason) and submit the change. Direct access to the MySQL databases will be immediately granted at this point. One word of warning, though: Do not delete the 192.168.1.% address that you see, as this will create some major headaches about 3 seconds later.

Once you’ve granted access to your computer (or computers, depending on where you want to hammer out some SQL queries), you can connect to the database through MySQL’s Query Browser. The login screen will look something like the image below.

The login is relatively straight forward. Server Host would be our domain (e.g. yourdomain.com), and the username would be a specific MySQL login that you’ve created. If you have multiple MySQL databases on your server, you might want to create a single account that has permission to all of the databases. This will save you the hassle of disconnecting and reconnecting any time you want to change databases.

Default Schema, though not always required, is recommended. This is the default database that you would like to log into. You shouldn’t have to change the port, and the options available in the Details tab will likely not be necessary for most of us, either.

Once all of this is entered, you can hit the big “OK”. Depending on the speed of your connection, you’ll be able to start querying your databases within seconds.

Using the Query Browser

The Query Browser is incredibly easy to use. The top of the screen is dedicated to writing the query, as well as having some over-sized buttons in place for those of us that prefer to use mice rather than keyboard shortcuts (though I prefer keyboard shortcuts as I “grew up” with DOS and even used Windows 3.1 for two years without a mouse). Queries can be executed either by pressing the big “Execute” button, or CTRL+Enter.

To the right you will see a list of all the databases the login can use. Naturally, I’ve removed the names of my databases, but you would see all of yours without problem. The big area is, of course, dedicated to the results. Much like we see in other query editors, this data can be multi-selected and brought into other applications either through a Copy+Paste, or with a drag and drop.

The Unforeseen Consequences of Power

However, the real power of this tool isn’t in the software itself. Instead, it’s in the minds of the people that will use the application. When a person starts working with databases, they start to see the world a little differently. Things become a little more logical, and humans make much less sense. In exchange for this alternate state of mind, we’re often rewarded with the ability to cut through the “garbage data” from our everyday lives and extract just the most relevant and accurate information we have available. This alone makes me want to force the public education system to introduce database management as a required part of the curriculum rather than whatever archaic programming language the high schools are trying to attract kids with.

Of course, this isn’t likely to happen anytime soon.

Do you access your databases on a regular basis? How do you prefer to do it?