Gimme theUsual please

(from Get It Done With MySQL, Chapter 12; for recent upgrades and documentation see here)

Once you're a regular at the lunch hangout, you can ask your favourite waitress for the usual, with a side of something special for today, and she'll know just what you mean.

Once you've been writing database-driven applications for just a few months, you notice that no matter what the database is, no matter who the client is, there's are certain modules you usually have to write. A table-browsing interface with user-friendly navigation and pagination. An interface for inserting brand new rows or rows that start as copies of existing data. An interface for editing and deleting rows. An interface for writing, testing, editing and executing custom queries, for saving these custom queries for repeated use, and for selecting such saved queries for execution from a simple dropdown control.

Long ago we came to call this collection theUsual— the set of capabilities we and our clients usually need for browsing data, getting data into tables, editing data, getting data back out again. CRUD for short.

The developer's golden rule is, what you always have to do, you should never have to do. We wanted an app that ... …

• provides a fast and perfectly general standalone maintenance interface, out of the box
• has a small footprint,
• developers can use to maintain any database/table they have privileges for, and
• can be plugged into any database-driven application under development, temporarily or permanently, to manage any table(s).

So our first books and products were built round this idea, and we've ported it to other languages and backends. In Chapter 22 of our book we did up a version for Connector/Net and MySQL. Here we implement it for PHP and MySQL.

PHP  turns out to be nearly perfect for implementing theUsual: it's a code generator, it's light on its feet, it's forgiving with respect to user interfaces, and millions of web developers are familiar with it. 

Why prefer TheUsual/PHP to PhpMyAdmin for some jobs? It's smaller, it's faster, its UI is more intuitive, its datagrid interface is less geeky and easier to use, it does cascading, editable master-detail browsing, it runs Google Charts directly from your data, it implements named, saved queries, its backup is one-click, and it displays a more useful database overview.

How should it work in PHP? Host, username and password can be specified from an include file or POSTed from a login form. Without host, username and password, theUsual stops cold. Otherwise, across the top of the page, the user sees database and table selectors, where MySQL's security settings quietly filter out items for which the user has no privilege. Once the user selects a database and table, build a default query to retrieve all that table's rows and columns. Execute the query, put up the result in a paginated HTML table with prettified column headers. Enable sorting on those column headers. If the table has a primary key, treat the table as editable and paint Add, Edit, Copy and Del buttons in each data row. Paint a statusbar where the user can ...

• set the number of rows per page, or
• move to the first/previous/next/last page, or
• find a key value, or
• open a textbox to compose a custom query, or
• open a textbox to write any SQL command, or
• specify a detail table to browse below the present table in a master-detail view, or
• start all over again from the beginning, or
• exit.

Fig 1: theUsual, starting page

Not possible to write for the general case, you say? Then let's start at the end of the story, with how theUsual looks when it runs. Fig 1 shows the opening screen. Once the user has selected a database she has privileges for, she sees a table selector as in Fig 1, and if the database has a table named views, then a selector for these 'views', which were our workaround for the absence of usable views in MySQL before about version 5.0.7. With current versions of MySQL, of course, proper Views are available from the Table selector, since that's how MySQL implements them.

Fig 2: theUsual, table and view selectors

If the user selects a view, theUsual retrieves and executes it. If the user selects a table, theUsual builds a default query, then paints an HTML table with Edit/Ins/Copy/Del buttons, and a statusbar with links for navigation, pagination, finding key values, writing a query, resetting the page, or exit, as in Fig 2. Clicking on a column header in the query result table sets ORDER BY to that column; clicking on it again reverses the order. Running the mouse over a row turns its background colour to pale blue. Finding a row on a key value sets its background yellow. Clicking Edit, Copy or Ins turns the current row into a data input Form with appropriate column value initialisations, and clicking Del deletes the row if the user confirms.

Fig 3: theUsual, browsing a query result

When a user clicks on Custom Query, or selects a View and then clicks Edit View, the top part of theUsual, above the query result table, looks like Fig 4. The user can edit a query, save it, or execute it.

Fig 4: theUsual, editing a View

No-one wants to wait minutes or hours for a query to return thousands or millions of rows. Even if execution speed can be much improved, it's rarely useful to try to browse a million rows at once. How do we paginate queries? With the MySQL LIMIT clause, which specifies the starting row and the number of rows to retrieve. When you give the theusual.php a new query, it asks the server for a count of rows that will be retrieved by the query (shorn of any ORDER BY or LIMIT clause), then with first execution it sets the LIMIT clause to 0,n where n is the number set by the user in the Rows/Page statusbar control. The default is 10, and can be changed in the statusbar. When the user clicks on Next in the statusbar, theUsual.php increments n by the rows/page value, and reloads the page. If the user clicks Previous , n is decremented by rows/page. If the user clicks First, the LIMIT clause goes back to 0,n , and if the user clicks Last , the LIMIT clause adjusts to retrieve the last n rows. The values are passed to the page as GET values, and when the page reloads, they are saved in the PHP $_SESSION[] array, whose contents persist across page loads and reloads. Here is the part of the form that gives the user paging options in the statusbar, and sets the form to reload the page, passing in the user's new setting: 

echo "<form action='" . $thispage . "' method='GET'>\n";

echo "  <p id='infohdr'>" . $updmsg . "Rows " . ($firstrow+1) . "-" .

    ((($page+1)*$pagelen < $total) ? (($page+1)*$pagelen) : ($total)) .

     " of $total.&nbsp;&nbsp;Rows/page:&nbsp;\n";

echo "  <select name='pagelen' onChange='javascript:submit();'>\n";

// SHOW PAGELENS, HIGHLIGHT SELECTED ITEM IF ANY

for( $i = 0; $i < 5; $i++ ) {

  $sel = ( $pagelen == $pagelens[$i] ) ? "id='sel' selected" : "";

  printf( "    <option %s value='%s'>%s\n", $sel,

          $pagelens[$i], $pagelens[$i] );

}

echo "  </p>\n  </select>\n";

// PAGINATION: PREV/FIRST

if ( $page > 0 ) {

  echo "&nbsp;<a id='infohdr' href='$thispage?page=0'>

       First $pagelen</a>&nbsp;\n";

  echo "&nbsp;<a id='infohdr' href=\"$thispage?page=".

       ($page-1)."\">Previous $pagelen</a>&nbsp;\n";

}

// PAGINATION: NEXT/LAST

if ( (($page+1)*$pagelen) < $total ) {

  echo "&nbsp;<a id='infohdr' href=\"$thispage?page=".

       ($page+1)."\">Next $pagelen</a>&nbsp;\n";

  echo "&nbsp;<a id='infohdr' href=\"$thispage?page=".

       ($maxpage)."\">Last $pagelen</a>&nbsp;\n";

}

and here is the code at the top of theUsual.php that picks up the page and pagelen values on load and saves them to $_SESSION[] elements:

// DATA PAGE SPECIFIED?

if( isset( $_GET['page'] )) {

  $page = $_GET['page'];

  $_SESSION['page'] = $page;

} elseif( isset( $_SESSION['page'] )) {

  $page = $_SESSION['page'];

} else {

  $page = 0;

  $_SESSION['page'] = $page;

}

// PAGELEN SPECIFIED?

if( isset( $_GET['pagelen'] )) {

  $pagelen = $_GET['pagelen'];

  $_SESSION['pagelen'] = $pagelen;

} elseif( isset( $_SESSION['pagelen'] )) {

  $pagelen = $_SESSION['pagelen'];

} else {

   $pagelen = 10;

   $_SESSION['pagelen'] = $pagelen;

How much time does it take to retrieve the last ten rows of a million-row table using LIMIT? On decent hardware, for the first hit a few seconds, for subsequent hits about 0.05 seconds. Further optimisation would take us into OLAP territory, and is left as an exercise for the reader.

How about teaching theUsual to browse a detail table if the browsed table has one? It's no harder than pagination. This query finds the child tables of table tbl in database db :

SELECT
  c.table_schema,u.table_name,u.column_name,u.referenced_table_schema,
  u.referenced_table_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
  USING( constraint_schema, constraint_name )
WHERE c.constraint_type='FOREIGN KEY'
  AND u.referenced_table_schema='db' AND u.referenced_table_name='tbl'
ORDER BY c.table_schema,u.table_name;

So, we add a detail table SELECT control to the statusbar, initialise it with an empty item labelled [None] , and populate it from the results of the above query. When a user selects a table from this control, theUsual builds a query for that detail table, based on the current master table row, and populates a second HTML data table with the results. But, you object, HTML has no concept of a current row! We have to add logic for that to the master browser, also logic to coordinate the detail query. All in all, about 200 lines.

How many thousands of lines of PHP code did this much of theUsual need? One-and-a-half. Original source is in Appendix E ( theUsual.php), about 800 lines in a main program module, about 700 lines in theUsual-funcs.php, and three little utilities (mysqlins.php , mysqlupd.php, mysqldel.php), about 25 lines each. Since, we have added instant graphical charting of query results, database backup, a print feature, more user settings, and many refinements. It's still a tiny codebase. And it executes much faster than a Visual Studio version of TheUsual compiled from C#. That's PHP for you.

How do we take advantage of PHP to simplify the coding? Two ways, mainly.

First there are many functions and classes to parse queries and browse data.

Second, theUsual is a state machine. HTML is stateless, and aside from the $_SESSION[] array, PHP is too, mainly. But $_SESSION[] can remember anything across invocations of any set of pages, so by teaching theUsual how to GET and POST state changes to itself and its utility pages, and by defining $_SESSION[] elements which specify theUsual 's state, and updating those elements when theUsual reloads after a user's action choice, theUsual can easily manage its own state.

To get a flavour of this, here is the initialisation code for host, username and password variables, from near the top of theUsual.php:

// IF USER CLICKED RESET, SAVE HOST/USER/PSWD, NULL OTHER SESSION VARS

if( isset( $_GET['reset'] )) {

  $host = $_SESSION['host'];

  $user = $_SESSION['user'];

  $pswd = $_SESSION['pswd'];

  $_SESSION = array();

  // HOST/USER/PSWD POSTED FROM LOGIN PAGE?

} elseif( isset( $_POST['host'] )

          && isset( $_POST['user'] )

          && isset( $_POST['pswd' ] )) {

  $host = $_POST['host'];

  $user = $_POST['user'];

  $pswd = $_POST['pswd'];

} elseif( !empty( $_SESSION['host'] )

          && !empty( $_SESSION[ 'user'] )

          && !empty( $_SESSION['pswd' ] )) {

  $host = $_SESSION['host'];

  $user = $_SESSION['user'];

  $pswd = $_SESSION['pswd'];

}

// IF HOST/USER/PSWD NOT SET, READ INCLUDE FILE FOR THEM

if( empty( $host ) || empty( $user ) || empty( $pswd )) {

  require "theusual-login.php";

}

// SAVE SESSION COPIES OF HOST/USER/PSWD

$_SESSION['host'] = $host;

$_SESSION['user'] = $user;

$_SESSION['pswd'] = $pswd;

There are interesting twists. For example, how does theUsual decide whether there is an updatable table in the query or view? As a first pass at this, it extracts what looks like the name of the main table from the FROM clause, then for each column of that table, examines the PHP object returned for it by mysql_fetch_field() to discover if the column  has a PRIMARY or UNIQUE key. If it does, the table is updatable; otherwise not. The code that does that is just a few lines:

// EDITABLE TABLE MUST HAVE A PRIMARY OR UNIQUE KEY

$colobjs = array();

$tables = array();

for( $i = 0; $i < $cols; $i++ ) {

  if ( $colobj = mysql_fetch_field( $result )) {

    $colobjs[$i] = $colobj;

    if( !empty( $colobj->table )) {

      if( !in_array( $colobj->table, $tables )) {

        array_push( $tables, $colobj->table );

      }

      if( strcasecmp( $colobj->table, $_SESSION['table'] ) == 0 ) {

        // $TABLE IS EDITABLE IFF THERE IS A PRIMARY OR UNIQUE KEY

        if ( empty( $_SESSION['key'] )) {

          if ( $colobj->primary_key || $colobj->unique_key ) {

            $_SESSION['key'] = $colobj->name;

            $_SESSION['keycolnum'] = $i;

            break;

          }

        }

      }

    }

  }

}

$may_edit = !empty( $_SESSION['key'] ) && ( count( $tables ) > 0 );

Here is another twist of interest. HTML permits specification of exactly one FORM action. So far as PHP is concerned, a FORM's action is a black box. But sometimes theUsual needs to choose FORM actions dynamically. How does a PHP program choose the correct FORM action from a multitude of possible actions? There is nothing to stop us from adding in a little JavaScript, which can set a FORM's action to anything we please. So to permit setting a FORM's action at runtime based on the user's choice and theUsual's state, we write a bit of JavaScript in theUsual's HTML header, for example

<script language='javascript'>

function view_submit( f, s ) {

  f.action = s;

  f.submit();

}

</script>

and we declare the form with a basic bit of PHP code like this:

echo "<form name=… action=\"view_submit(this.form,'viewupd.php')\">\n";

JavaScript, which is client-side, cannot call PHP code, which is server-side. But PHP can generate HTML script which calls JavaScript. It's just another simple way of driving browser behaviour from the current state of a database-driven webapp.

Per page: Rows/page dropdown appears on the dashboard when the query is pageable. Possible values are 5,10,20,50,100,500. To change these, edit $pagelens in theusual.php.
Prev, Top, Next, Last: Previous, top, next, last rows-per-page rows.
Detail table: Dropdown of tables in the current database with a foreign key referencing the current table.
Custom query: Put up form for custom query.
Charts: Menu of Google Visualization charts that could be drawn for the current query.
Print: Print the current data display.

Browse ...: Browse objects and their properties in the current database, backup the current database .

Settings: Toggle text/blob formatting, stripping of HTML tags from input queries, and use of information_schema for metadata (the more databases and tables there are on the server, the slower information_schema is; the first information_schema access can take minutes on servers with dozens of databases and thousands of tables).
Exit: Exit.

For TheUsual's change history and more info on these options see here.

The command-line MySQL client is fine as far as it goes, but some tasks are very awkward in its command-line window. MySQL WorkBench is a huge, resource-hungry application to load just to run a few queries. The PhpMyAdmin data edit interface is geeky, and PHPMyAdmin can be flakey with stored procedures and functions. We wondered if theUsual/PHP could be taught to run multiple queries including DDL and stored routine calls.

But PHP has its problems too. The problems executing stored routines under the mysql API are fortunately in the past, and the mysqli library's mysqli_multi_query() can call multiple stored routines without problems, but it is hard-coded to delimit statements with semicolons, so a CREATE PROCEDURE | FUNCTION | TRIGGER query with multiple semicolons makes mysqli_multi_query() fall over.

Version 1.14 of theUsual/PHP solved these problems well enough to support multiqueries; see multiquery()and the functions it calls in theusual-funcs.php. It first explodes the user query string into blocks delimited by a user-defined delimiter if found. Then within each block, it executes each delimited query in turn, correctly handling DDL queries and calls to stored routines, and displays results.

Here is what the solution looks like in action:

We hope you find TheUsual/PHP useful. For more info see the readme