Quickly put data from MySQL to the Web with DrasticGrid

With DrasticGrid, you can easily put a MySQL database table or view on a Web page, complete with editing, sorting, and pagination capabilities, and support for adding and deleting records.

DrasticGrid is one of three controls from DrasticData — the others are a tag cloud and Google Maps controller, both of which also get their data from a MySQL database.

DrasticGrid is not packaged for Ubuntu, Fedora, or openSUSE. In this article I’ll use a 64-bit Fedora 9 machine and version 0612 of DrasticTools. The commands shown below install DrasticTools, and thus DrasticGrid, on your Web server for all Web projects on that server to use. These commands assume that your DocumentRoot is /var/www/html; see this list of DocumentRoot paths of various distributions and adjust the path accordingly. Moving the example PHP files out of the DocumentRoot is not strictly necessary, but on a production machine it is good practice.

# cd /var/www/html
# unzip /.../DrasticTools0612.zip
# cd ./DrasticTools/
# mkdir -p  /usr/local/php/DrasticTools
# mv Doc.txt License.txt /usr/local/php/DrasticTools
# mv Example* /usr/local/php/DrasticTools
# cd /var/www/html
# chown -R  root.apache  DrasticTools
# chmod -R o-rwx DrasticTools
# chmod -R g-w DrasticTools

All of the DrasticGrid examples require the mootools JavaScript package. You can download the core and more JavaScript files. These downloads are created for you when you select which components you want in each package. I created each JavaScript file with all options. Copy the two JavaScript files to /var/www/html/DrasticTools/js.

Shown below is the MySQL database schema used for the first example. The table has more fields than will conveniently fit across the screen. I have deliberately selected many data types, including enum, date, and time types, to see how much (or little) DrasticGrid helps the user to input correct values.

mysql> create table folks ( 
   name varchar( 100 ), 
   birthday date, 
   yeartest year, 
   gender enum('m','f'),
   realtest real,
   numtest numeric(10,3),
   timetest time,
   tstest timestamp,
   dttest datetime,
   waffle text, 
   blobtest blob,
   id int AUTO_INCREMENT, 
   primary key(id) );

mysql> insert into folks values ( 
    'sam',  '1956-01-02', 2008, 'm',
    5.6, 5.6, '23:00', 
    '1986-01-02 23:00',
    '1956-01-02 23:00',
    'some nice and long waffle could go here',
    'some nice blob', default );

mysql> insert into folks values ( 
    'sally', '1963-02-21', 1999, 'f', 
    6.3, 6.3, '09:00', 
    '1983-2-21 09:00',
    '1962-2-21 09:00',
    'lets party', 'lets party blob', default );

mysql> insert into folks values ( 
    'susan', '1948-8-14',  1949, 'f', 
    4.8, 4.8, '13:00', 
    '1988-8-14 13:00',
    '1948-8-14 13:00',
    'something', 'somethingblob', default );

Now that we have a database with some values, we’ll need a directory to serve PHP files to a Web browser. I’ll put these PHP files (shown below) in the DrasticGridTest directory under DocumentRoot. I can then call it to demonstrate using DrasticGrid from another Web project on the same server.

# mkdir /var/www/html/DrasticGridTest
# chown ben.apache /var/www/html/DrasticGridTest
# chmod +s /var/www/html/DrasticGridTest

The PHP file that shows the folks database table to the user in a DrasticGrid is shown below with the interesting parts in bold.
You have to make this PHP file yourself, though you can copy and paste the example into new applications, modify the database location and authentication information, and then add additional options to the options array or the creation of the DrasticGrid control.

This example first defines the full path on the server where DrasticGrid is installed, then it sets the parameters needed to get to the database. In this case I am using the database called test on localhost and the folks table as created above. The options array lets you customize how the grid appears, what columns are visible and edited, whether you can delete or add new records, and how the data should be sorted, among other things. In a more real-world example, you would likely limit the view to only six or eight columns so that the user is not overwhelmed with data for each record.

After the initial PHP code, some JavaScript files are included, and a small amount of JavaScript code creates a drasticGrid object and assigns it to the HTML div by passing the DIV element’s ID to the drasticGrid JavaScript object constructor.

<?php
define("PATHDRASTICTOOLS", "/var/www/html/DrasticTools/");
$server="localhost";
$user="ben";
$pw="";
$db="test";
$table="folks";

include (PATHDRASTICTOOLS . "drasticSrcMySQL.class.php");

$options = array (
        "add_allowed" => false,
        "delete_allowed" => false,
        "editablecols" => array("name", "birthday", "yeartest", "gender",
		"realtest", "numtest", "timetest", "tstest", "dttest", 
		"waffle", "blobtest" ),
        "sortcol" => "name",
        "sort" => "a"
);
$src = new drasticSrcMySQL($server, $user, $pw, $db, $table, $options);
?>

<html>
<head>
<link rel="stylesheet" type="text/css" href="/DrasticTools/css/grid_default.css"/>
<title>What is The Grid?</title>
</head>
<body>


<script type="text/javascript" src="/DrasticTools/js/mootools-1.2-core.js"></script>
<script type="text/javascript" src="/DrasticTools/js/mootools-1.2-more.js"></script>
<script type="text/javascript" src="/DrasticTools/js/drasticGrid.js"></script>


<div id="thegrid"></div>
<script type="text/javascript">
var thegrid = new drasticGrid('thegrid', {pathimg:"/DrasticTools/img/", pagelength:10});
</script>

</body>
</html>

The resulting grid rendered by Firefox is shown in the screenshot below. The square to the left of each row will have a red cross in it if you set delete_allowed=true in the options that are passed to the drasticSrcMySQL constructor. If you set add_allowed, then after all the rows you’ll see a little star to the left of a blank row. Clicking on the star creates a new record.

Clicking on the small pencil icon to the right of each cell allows you to edit that cell’s value. When you are done editing a cell, press Enter to submit the value. The cell reverts back to not editing, but shows you the value in red instead of black. Over a short period of time the red fades to black to indicate that the value has been committed to the MySQL database and the pencil icon returns.

To be as nasty as I could to DrasticGrid, after loading the Web page and all the data from the folks table, I stopped the MySQL database on the server and started editing cells in the browser window. The cells still faded from red to black and there was no indication that the updated cell value could not be placed into the database. I am not sure why the error committing data to the MySQL database (when it was stopped) was not being reported back to the grid control. If I stopped Apache instead of MySQL, then the Web interface would not allow me to finish an edit until I restarted Apache again. So a break in the link between the Web browser and the Apache server was detected and reported to the user, but a break between the Apache server and the MySQL server was not detected. Given that the link to the browser should be the more volatile link, it is good that it is reported correctly, but this test shows that if a sysadmin stops the MySQL server for any reason you might loose some cell edits.

DrasticGrid comes with specific handling of bool, enum, email, and Web URL datatypes. If you are storing URLs in text fields in MySQL varchar data types, you’ll need to tell DrasticGrid that those fields are URLs and not plain text. By letting DrasticGrid know more information about the type of the data stored in the varchar field as shown below, you can display those columns as clickable items. Unfortunately there is no explicit support for editing colors, date and time, or currency fields in the current release, so you only get a string editing box for those types. Each cell type is only referenced a few times in the drasticGrid.js file, so you could hook up your own JavaScript calendar object to DrasticGrid for editing date fields fairly easily.

<script type="text/javascript">
var thegrid = new drasticGrid('thegrid', 
	pathimg:"/DrasticTools/img/", pagelength:10,
        columns: [
        {name: 'name'},        
        {name: 'contact, type: DDTYPEMAILTO, width: 140},
        {name: 'www', type: DDTYPEURL, width: 180},
        ]});
</script>

When creating the JavaScript drasticGrid object, you can also pass in an array showcolsnot: new Array("foo", "id") in order to hide these columns from the grid. This would go right after the pagelength:10 option.

Final words

Keyboard navigation is one area in which DrasticGrid is lacking. Being able to move the current cell cursor around the DrasticGrid with the arrow keys and press F2 to start editing a cell would make larger edits more slipstreamed. Aside from adding more specific editors for cell data types, allowing you to edit a cell that contains a foreign key would be a valuable addition to DrasticGrid.

If you want to edit a table from a MySQL database on a Web site, DrasticGrid is well worth a look. It handles all the AJAX code and putting the values into the MySQL database. You also get the ability to sort by columns by clicking on their header fields, and the ability to page through a lage table of 100,000 records by only downloading a page of 15 records at a time. To skip to a given page in a huge table you can simply drag the slider at the side of the table.