Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

MySQL and Perl CGI

by PyrexKidd (Monk)
on May 14, 2011 at 20:13 UTC ( #904878=perlquestion: print w/replies, xml ) Need Help??
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 | -----------------------------------

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.

Replies are listed 'Best First'.
Re: MySQL and Perl CGI
by GrandFather (Sage) on May 14, 2011 at 23:09 UTC

    A solution depends rather on a few important factors:

    1. Are you already using session management?
    2. Will there be more than one "session" active at a time?
    3. Can you add a unique uneditable record ID to each editable table?
    4. 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
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).


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:

    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).

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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://904878]
Approved by planetscape
Front-paged by planetscape
[shmem]: perl -lE '$_="odd mornin";($o=$_)=~ s/^|$/g/g;say$o'
[Eily]: Hi monks o/
[choroba]: 3 PM in Vietnam, BTW
Discipulus maybe a wrong impression..
[karlgoethebier]: Discipulus: Workshops are for weenies ;-)
[Discipulus]: here 43C high umidity, tiger mosquitos, violence in the street.. everywhere is vietnam
[karlgoethebier]: OK, i'm socially unacceptable
[karlgoethebier]: Discipulus: Were the hell are you today?
[Discipulus]: ..or suitable for the presidence ;=)
[Discipulus]: at Caput Mundi

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (8)
As of 2017-06-27 08:11 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (600 votes). Check out past polls.