![[ t ]](/images/structural/icon_twitter.gif){kind=icon}
The Quandary of the Single Table Web Site
What do you do with sites that need a single table of data updated? These are sites which are totally static, except for this one thing…
For instance, we have a client for which we built a static site. But they have a page which lists all the locations where you can buy their stuff. This list changes a lot. They don’t want to keep calling (and paying) us to update this, so they want an interface where they can manage the list themselves. The list couldn’t be more simple — if you implement in SQL, it’s a flat, five-column database table.
This happens a lot — the sites are all static except for one element, be it a list of locations, events, the ubiquitous “Latest News,” etc. Some common themes
- The table is self-contained — it has no foreign keys to any other table (indeed, it’s the only table in the database). It is an island of data unto itself.
- The “objects” often extend past the “page” or “post” model (more fields are needed), so this precludes using a blogging system.
- You can do the display logic in some other manner. All you need is a way for the users to administer the data.
- Access is binary. One password gives the bearer access to the whole thing.
So what do you do? The idea is to do something as simple and fast as possible, with as little repetitive coding as you can get away with.
The most obvious solution is to build a little interface, but this goes against our stated goals. You have to program and debug a bunch of stuff for one little deal, which is a drag. Writing the same authentication and validation crap over and over gets a little tiring.
BUT, if you generalize it and re-use it, then you’re in better shape. Instead of hard-coding the fields, pull them out of an ini file that contains their SQL field name, display title, data type, interface type, validation regex, etc. You’d need to refine it a bit, but you could dial it in tightly.
Related to the above, there are PEAR modules that strive to do just this. DB_DataObject with HTML_QuickForm, for instance. Propel for PHP5 is good too. But these are generally overkill, and they require a lot of code included on the backend.
I have great luck with PHPRunner and ASPRunner — we’ve talked about both of them before. These are little code-genning tools that spit out set of files to keep a table updated.
The latest versions of these two tools are fantastic — they have WYSIWYG support and file uploads. They’re so handy, in fact, that it’s just as easy to have them manage 10 tables as it is to have them manage one.
Additionally, I’ve been in contact with the developer, and he’s planning on developing an event model as well, so there’s an enourmous amount of functionality you could build with these. See this email exchange between he and I for some of the possibilities.
How about phpMyAdmin? Do you just let them loose with that?
Development frameworks like QCodo or Ruby would seem to make the maintenance of single tables pretty simple as well.
Do you even do it in a database? If you’re doing addresses (like in my example), could you give them a text file format and limited FTP access? Name on one line; then address on the next; then city, state, and zip; then skip a line before the next one.
(I will admit to a crude hack for data I maintain myself: using PHP’s ini file format. I started using the parse_ini function, but then switched to one someone had written that was a little more robust. It gives you very simple access to a two-dimensional data set.)
So, how do you handle it?
What This Links To
QCodo: Rails for PHP
Qcodo - PHP Development Framework: I think you're going to see that phrase a lot in the future: "Rails for [insert language here]." I had heard that Cake was "Rails for PHP," but after watching some demos, I think the QCodo guys have actually obtained that title. It…
Rails Blurs the Lines
I've been working with Rails for a few weeks now and it's making "install" vs. "build" decisions much harder -- (We interrupt this post to get two things out of the way: Yes, Rails is as good as everyone is saying. And yes, that pisses me off too. I worked so hard…
PHPRunner
PHPRunner - PHP code generator for your MySQL database: A few weeks ago, I talked about ASPRunner, which is a code-generation engine to build ASP pages to manage ODBC databases. At the end of that post, I whined that I wanted something similar for PHP/MySQL. Well... PHPRunner creates set of PHP pages…
Comments
I usually write a little desktop app (usually in REALbasic) to talk to the database and give them that. Quicker and easier to write and debug than a web app, and usually quicker for them to use.
you could install a very simple CMS/blogging system - i'm thinking Wordpress here - and just integrate a page or two into the theme for the rest of your site. Then let them log into Wordpress and edit pages or stories in there.
you could install a very simple CMS/blogging system - i’m thinking Wordpress here
This is great for things that fit that data model, but what about things that don't? What if they want to maintain a list of Supreme Court cases, complete with about 10 extra fields of data.
(I can always manage to bring this topic up, can't I?)
I have updated the post with some "ground rules," just to save everyone from commenting on the same things over and over.
You could use phpcodegenie for building a crud interface and it's done with a little styiling (if you want to).
or maybe qcodo, i saw the example video and it seems to generate code from the tables themselves.
however, code generation it's the way to go for.
Django with its auto-generated admin interface might be a good solution. It's at http://www.djangoproject.com, and if you skim pages 1 and 2 of the tutorial you'll see it in action. I'm using it for sites that non-technical users need to keep updated, and it's saved me tons of hours writing CRUD pages. It can be kind of a pain to config the webserver, though.
I have great luck with PHPRunner and ASPRunner — we’ve talked about both of them before. These are little code-genning tools that spit out set of files to keep a table updated.
"You are not authorized to view this page"
I fixed the links. Sorry.
I would get over the "I need a database for any kind of active content"
I would go for a text file and plug it somehow (name the method) in the required page.
How they want to maintain it is the key point.
On the easiest (and crappiest) way would be to have a place holder on the page and they can export their own tables from Excel or Word to html and upload it to the server.
- From Excel to CVS and process it with PHP leaving a nice formatting
- A single table maintenance page
- XML and XSLT
- ....
Not a big deal.. depends on how much developer time can afford...
CGI anyone? :)
What if they want to maintain a list of Supreme Court cases, complete with about 10 extra fields of data.
Sounds like that application falls a bit outside your stated goals here. See http://kalsey.com/2005/12/thedangerofdoingtoo_much/
How about Macromedia Contribute? Cheap, easy to use and very flexible. Build the template once and hand the editing tool over to the client.
Sounds like that application falls a bit outside your stated goals here.
No it's not. One table, several columns (plaintiff, defendant, year, ruling, vote, who wrote the opinion, etc.), many rows. This is exactly what I'm talking about.
Yes, you can get much more complicated with it, but -- for the purposes of this example -- let's not.
they can export their own tables from Excel or Word to html and upload it to the server.
We've talked about using Office for content management like this in a couple places:
I do like your "export from Word then include the file" method. That could be a winner for a lot of things.
A simple approach for small amounts of data is to store the PHP array using PHP's serialize, and then unserialize it as needed, store it in session. when the session times out the array goes away.
Everyone is going to yell when I say this, but if there are not a huge number of records you could also use XML. This presumes that the developer knows there way around XML, XSL, XPath, XQuery; which I do, so no problem.
Yeah, PHP serialization is handy. But, this just addresses the data storage. How about the interface? No matter how you store the database, you still have to build an interface.
When I've had to do this sort of job, normally I've just saved the data in a text file either using XML (if I had a parser on hand or felt like throwing a small one togeather) or a standard "config-style" format. The interface, then, is very simple: a simple form which has the fields to update and a password box for simple authentication. The password is hardcoded (and encrypted) into the PHP file that updates the text file. That PHP file doesn't care what the fields are - it just takes the field name, takes the content, and uses that to construct the data file, without caring what they are (although wit enogh security to make sure that we're not getting any nasty stuff sneaking in). The whole job normally takes about 15 minutes to code - maybe 30 if I haven't had my caffine yet.
I wouldn't bother with a database, as it is too much like overkill, as is a complex authentication system. And I like phpMyAdmin, but I would never let most of my clients near it. :)
Several Wiki systems allow you to create and publish a static site from wiki editable contents . (For example, twiki has a plugin for this.) But installing a wiki at customer site is not usually an option - especially when all they want is a static site.
However, website provider could use wiki to produce the site and create a static version. With some additional enhancements, this static version could also include some standard HTML interfaces (along with relevant scripts) to update structured parts of web pages.
Such a publishing feature would further enhance value of wiki systems...
I should add - a few times, I've stord the data directly in the HTML file. By using specialised comments tags, you can parse the HTML file to grab the data. The have a PHP interface (normally a single file) allow editing of the HTML page. It is a tad odd, but it has the advantage that clients can edit the HTML in Dreamweaver if they want, and so long as the comments remain intact the interface will still work for updating. You also don't have to manage a separate data file, and the page doesn't need to be parsed when requested. It was probably a dumb idea, but it was fun, especially when extended dramatically, and worked well pre-PHP.
- There are 23 comments on this entry.
- 20 comments have been displayed above.
Comments are Closed
Thanks to all who participated.
- Why Ad Blocking Kinda Sucks by Mark, 1 hour, 13 minutes ago
- EPiServer Goes Public by cmadler, 7 hours, 18 minutes ago
- Why Ad Blocking Kinda Sucks by Brade, 8 hours, 54 minutes ago
- Why Ad Blocking Kinda Sucks by Dan Hudlow, 17 hours, 52 minutes ago
- MousePath by Corey V., 1 week, 2 days ago
- Show 5 more »
- Video Games as Cultural Artifacts by Deane in 2007
- Mobile Software Pricing Sucks by Deane in 2007
- Columbine BMW On The Auction Block. Again. by Dave in 2006
- End Of The Line by Dave in 2006
- More Macs In Space by Dave in 2006
- Show 5 more »