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

Hi Monks!
I need some advice, I have this big questionnaire with about 200 questions divided into 4 forms. I would like to once the first form gets filled to go into to the database table without waiting for the rest of the forms to be completed. I know I could pass parameters from form to form and at the end submit the entire data to the database, I am trying to avoid that if it is possible. Is there a way to submit the information more than once to the some row in a database table? It seems that when I submit the first part (first form) to the database table and submit the second or any after that a new record gets created instead of been part of the some first row from the first form
Thanks in advance for the help!

Replies are listed 'Best First'.
Re: Perl and Database Input
by davorg (Chancellor) on Aug 08, 2006 at 13:47 UTC

    The first form needs to insert a record. The second and subsequent forms need to update the record.

    The hard thing is ensuring that the correct row gets updated. The code that handles the first form (the insertion) needs to generate a unique identifier which is inserted into the database record and also passed back in the HTML form for the second (and subsequent) forms.

    --
    <http://dave.org.uk>

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

[reply]
Re: Perl and Database Input
by marto (Cardinal) on Aug 08, 2006 at 13:50 UTC
    You have not posted any code or described the database schema you are using. It sounds as though you are doing two record inserts rather than updating the existing row, adding values of the user input from other forms. Of course you would need to keep track of users, perhaps using sessions, so that you would know which row to update. Perhaps posting your code and detailing the database would help to provide a better answer :)

    Please read the PerlMonks FAQ and How do I post a question effectively? if you have not already done so.

    Hope this helps

    Martin
[reply]
Re: Perl and Database Input
by jfluhmann (Scribe) on Aug 08, 2006 at 13:51 UTC
    What about after inserting information from the first form, you capture the 'id' for that row and only pass it along to the other forms. The other forms would use UPDATE....WHERE id = '$id' instead of INSERT.
[reply]
Re: Perl and Database Input
by cdarke (Prior) on Aug 08, 2006 at 13:52 UTC
    Whether a new record gets created or not will depend on the key, and your SQL. You should just be able to carry out an UPDATE on an existing row.
    You should consider what happens if the whole transaction is aborted, for example if the user navigates away after only filling a single page. At what point do you commit?
[reply]
Re: Perl and Database Input
by TomDLux (Vicar) on Aug 08, 2006 at 15:32 UTC

    What you should do is create the entry when you process the first form and update the entry when you process the additional forms.

    However, that can provide a humunguous security hole for people to explore your database.

    Let's say you create the record, as id 1234, and then stick hidden field 1234 into each of the additional forms, to enable updating the records. Then a malicious child might look at the page source, alter the hidden field to values in the range 1..1233, and view information about existing members, or update the data in undesirable ways. It would not be good if someone updates possitive account balances to negative, or vice versa.

    Since you say these are questionaires, people should only be on the four pages for a few minutes, no more than an hour or two. I would suggest having one table, for incomplete interviewees, and another, possibly even in a different database, for complete ones.

    When someone fills out a form, they get a random number between 1 and a gazillion as a key. When they fill out the additional pages, look up the key in the key_to_memberId table, obtaining the id for their incomplete record, which can now be updated. Random hunting will have limited success, since the key_to_memberId table is very sparsely populated.

    Once an hour, a different script checks for complete interview records, and moves them to a different table, in a different database. Now complete records are safe from accidental or malicious web access. At the same time, any incomplete questionaires older than a couple of hours can be deleted, to protect them from snoopers. Don't forget to trim obsolete entries from key_to_memberId.

    Now records are only exposed to web probing for a limited time, and access is controlled.

    Don't forget to verify the key returning from the web page contains only digits, no '123; rm -rf'.

    --
    TTTATCGGTCGTTATATAGATGTTTGCA

[reply]
Re: Perl and Database Input
by barrycarlyon (Beadle) on Aug 08, 2006 at 15:26 UTC

    Personally i would do four tables, one for each form/section and just link the displaying of the data using something like a has_a

    >
    Barry Carlyon barry@barrycarlyon.co.uk
[reply]

Back to Seekers of Perl Wisdom