Various services on web hosts use Structured Query Language (SQL) databases. The storage is much more compact than plain text “flat files”, can handle vastly more data than personal computer spreadsheets, and have built in utilities for organizing and retrieving data. For Unix/Linux (or *NIX) hosts and many Windows based servers, one of the most popular is MySQL. MySQL is “open source”, which means customized setups are possible, and anyone can contribute code suggestions.
Examples of applications which might use MySQL databases are auto responders for visitor inquiries, blogs (such as those using WordPress), internet stores with shopping carts, affiliate link tracking systems, forums, automated user support systems, various content management systems (CMS), and so forth.
Anyone can register a domain name and rent virtual space on an Internet Service Provider (ISP), and avoid the pains of setting up server hardware. Depending on the ISP company and the type of hosting (”shared server” or “Virtual Private Server VPS”), that person can have different levels of access to behind-the-scenes environment. On one, I had to ask Tech Support to set up most stuff and to download backups. On others, I can install scripts (provided the instructions are good enough), set permissions and some other environmental stuff, robot.txt to control web crawlers, .htaccess for various system functions, do my own downloads and uploads, and so forth.
Most of the time, installing applications which use MySQL does not require that the domain owner go inside MySQL, other than to define databases and set users with passwords. However, sometimes it is instructive to browse a few tables to see how they are set up. Less often, the user can want to read and/or edit the content of records. I have done such editing when the application (e.g., a store system) is slow and klutzy for doing such work through the administrator’s control panel. Imagine a spreadsheet in which you could only access one record at a time, and then only a cell at a time.
So far, my domain experience has been on *NIX systems. They typically have a control panel for administering the MySQL databases, then another for going inside. For the latter, it is typical to have some version of phpMyAdmin. Current versions are reasonably easy to use, but that has not always been the case.
phpMyAdmin still is not the perfect editing interface. For example, in browse mode, cells are usually displayed only up to some character limit. Also, there can be awkwardness at entering HTML codes.
Back in September 2002, I discovered a great tool for dealing with MySQL databases, called MySQL Data Manager (MDM), by Ioannis Livassov of Athens, Greece. This is a Perl based CGI script which helps in its own setup. “Select All” mode resembles a spreadsheet, for browsing. “Search/Modify” mode makes it easy to search any of the defined fields, similar to a spreadsheet and much easier than with phpMyAdmin. For example, one can retrieve all records of a certain vendor or category or product ID having same front characters, or product descriptions with some key phrase. In case the whole topic makes you nervous, play with the trial version before paying the modest price of $37, which is discounted in quantity. A limitation apparently set by MySQL is that browse mode does not permit cell editing. However, in search mode, one can select one or all of the ten records displayed on one page, then sequentially edit and save. phpMyAdmin has a similar limitation, but cannot select more than one record at a time for editing.
MDM has literally saved me weeks of work on store edits, compared to working through that store’s admin panel. An alternative, for the brave or desperate, is to export an MySQL backup to personal computer with phpMyAdmin, modify with a plain text editor, then import back to phpMyAdmin. If you screw up badly, re import the un modified file or have Tech Support backlevel to their latest saved copy. I use “Export type = replace” to not create duplicate records which can cause duplicate displays.
Copyright 2008 by Donald A. Miller, PhD / SoftWareProgs.com,
See “S/W Store” and “Specials, Limited” for good deals on software.
Post a Comment