MySQL and Perl CGI

PyrexKidd has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am working on an application that is a Perl CGI front end to a database. Essentially what I am doing is querying the DB to generate a table in the web page. I would like to be able to edit the DB fields directly from the web page. i.e. I have a table:

-----------------------------------
| Name  |  Number  |   Address    |
-----------------------------------
| Bob   |  12      |  1212 street |
-----------------------------------
[download]

I would like to be able to click then name to change it, then submit the changes to the db.

My first thought was to cache the initial query results, cache the 'updated' changes, compare them, and dynamically create an UPDATE query. In practice this is not... a good way to do this.

Can any one recommend a good strategy to acheive the desired results?

thanks in advance for an assistance.

Comment on MySQL and Perl CGI Download Code

Replies are listed 'Best First'.
Re: MySQL and Perl CGI by GrandFather (Saint) on May 14, 2011 at 23:09 UTC
A solution depends rather on a few important factors: Are you already using session management? Will there be more than one "session" active at a time? Can you add a unique uneditable record ID to each editable table? Can you depend on JScript being enabled for the client? If you are using session management then caching the last query then diffing against that to drive the database update seems ok, but may become interesting if multiple access is possible. If you have unique record IDs you can put those in hidden fields on the web page so you can easily match up records. If you have JScript available you can use it to post before and after versions of the edited records. You could put all the original data into hidden fields that get posted back along with the edited fields. There are many options and trade-offs of different sorts with each one. True laziness is hard work	[reply]
Re: MySQL and Perl CGI by danb (Friar) on May 14, 2011 at 22:17 UTC
In practice this is not... a good way to do this. Why not? I've been doing exactly that in my custom record editor for the past half-dozen years or so (I always use it with PostgreSQL, though compatibility with MySQL is maintained). If you can require javascript, then doing an ajax record editor would be better, because you can detect changes through form events rather than the equivalent of "diff -u".(In fact, they is already a variety of self-contained AJAX viewer/updater systems with that feature.) A much simpler way to do it is to just update every record all the time, even if the user changed nothing, but it's a criminal waste of database resources (particularly if you allow editing a few dozen rows per screen). --Daniel	[reply]
Re: MySQL and Perl CGI by trwww (Priest) on May 15, 2011 at 03:07 UTC
Just a tip, but there are tools that make what you want very easy to do by just running a few commands on the command line. For what you are trying to do, there is a tool called Gantry that is awesome: http://search.cpan.org/~tkeefer/Gantry/lib/Gantry/Docs/QuickStart.pod Of course, you have to learn how to use that tool also, and in the long run, developing an app by yourself is going to be a good learning experience. But if you are trying to just get this up and running quickly, investing some time in learning something like gantry is going to be valuable. So some google searches on "perl CRUD" and you'll find some tools that will make quick work of putting a web frontend on your database (CRUD stands for Create, Retrieve, Update, Delete).	[reply]
Re: MySQL and Perl CGI by Anonymous Monk on May 15, 2011 at 01:04 UTC
This kind of app is what's known as "CRUD" = Create Read Update Delete. And there are just so many already-existing tools out there for doing that, you shouldn't have to write your own.	[reply]


go ahead... be a heretic
	PerlMonks