Create a Web-based interface for MySQL databases in a flash with phpMyEdit

If you’ve ever worked with MySQL databases, you are probably familiar with phpMyAdmin, a PHP-based tool that allows you to create and manage MySQL databases via a browser. It is an indispensable tool for anyone building a PHP/MySQL-based Web application. But while phpMyAdmin eases the task of creating and managing the back end of your Web application, it is of no help when it comes to designing a Web-based interface. To simplify creating PHP-based front ends, try phpMyEdit, an ingenious piece of software that can generate a functional Web interface in a matter of minutes — no PHP programming skills required. Although phpMyEdit hides the complexity of generating a PHP-based interface, it still offers an easy-to-use yet powerful mechanism to customize virtually any aspect of the created front end.

Let’s start with creating a simple front end to an even simpler MySQL database, consisting of a single table and three fields: ID, Notes, and Source. Download the latest release of phpMyEdit, unpack it, and move the resulting folder into the document root of your Web server. Point your browser to http://serveraddress/path/to/phpMyEdit/phpMyEditSetup.php. If phpMyEdit works properly, you should see a database connection form. Fill out the required fields and press the Submit button. phpMyEdit then displays a list of the fields in the specified database table, and you have to select a so-called identifier (a primary key in MySQL terminology). phpMyEdit then asks you to provide the name and title you want to use for the generated page. Here you can also decide whether the page should have a header/footer and use a basic CSS stylesheet by ticking the appropriate check boxes. Once you’ve pressed Submit, phpMyEdit generates the page and writes it to the phpMyEdit folder. It also conveniently displays the generated code, which you can copy and paste into a text editor for additional tweaking.

Now point your browser to the created page, and you should have a basic front end to your MySQL database. Using it, you can create, modify, delete, view, sort, and filter records. While the default interface is functional as it is, phpMyEdit offers settings you can tweak to make the interface better suit your needs. You can find detailed instructions for all these tasks in the program’s documentation.

Let’s start with something simple, like changing the number of records per page. To do this, open the generated .php file in a text editor, locate the $opts['inc'] = 15; line, and replace the default value with the number you want. If you want to display all the records in the table on one page, set the option to -1.

Another thing you might want to change is user permissions. By default, anyone can access and modify the data in the database. If you want to give visitors read-only access, you can do that by modifying the $opts[‘options’] option. Permissions here are specified by using or omitting appropriate letters. For example, if you want to disallow users to modify and delete records in the table, all you have to do is to remove the C and D letters from the default ACPVDF string, so the option looks like this:

  $opts['options'] = 'APVF';

By default, the created page sorts records by their identifier, but you can also change that by modifying the $opts[‘sort_field’] option. For example, if you want to sort records by the Notes field, the option will look like this

  $opts['sort_field'] = array('Notes');

You can also specify more advanced sorting based on several fields. The following sorting settings sort records by both Notes and Source fields:

  $opts[sort_field] = array(Notes, Source);

Since phpMyEdit supports SQL, you can use it to manipulate the data using SQL queries. Let’s say you have a bibliography database, where the author’s name is stored in two separate fields: Firstname and Lastname. Using a simple SQL statement you can create a new Author field that concatenates the data from the Firstname and Lastname fields:

  $opts[fdd][Author][sql] = CONCAT(Firstname, , , Lastname);

The appearance of the front end is controlled by a simple CSS stylesheet, and you can tweak it to your heart’s content to achieve the results you want. You can also modify the overall look of the interface another way without fiddling with CSS: phpMyEdit allows you to customize the navigation buttons using the $opts[navigation] option. This option defines where on the page the navigation buttons appear (U — above the table, D — below the table) and the button type to use (B — default buttons, T — text links, G — graphics links). Using these parameters, you can specify the appearance of the navigation buttons. The following settings display the graphics buttons below the table:

  $opts[navigation] = DG;

These are just a few simple examples of what you can do with phpMyEdit, and there is much more to it. phpMyEdit also supports such advanced features such as triggers, CGI variables, JavaScript functions, and extensions.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, US, British, German, and Danish computer magazines.