- About Us
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 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
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.
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
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
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.
Ben Martin has been working on filesystems for more than 10 years. He completed his Ph.D. and now offers consulting services focused on libferris, filesystems, and search solutions.