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

Hi, dealing in CGI, CGI.pm, and MySQL, I have managed to insert stuff and read SQL returns and currently I have a form where a user can edit existing records but I'm stumped on how to go about updating these records in MySQL? A table has about 10 fields in it (all text with one monetary column).

mr. scary
  • Comment on best method to update mysql records over the web

Replies are listed 'Best First'.
Re: best method to update mysql records over the web
by frankus (Priest) on Jun 28, 2002 at 08:24 UTC
    When updating data on a database from a form, general practice is to take the data from form and pass it to a update SQL statement.
    use CGI; use DBI; sub update_mytable($$$$$) { my ($db,$id,$name,$rank,$serialno)=@_; # Use late binding, good practice for some DBs; doesn't hurt others my $sql = "update mytable set name = ?, rank = ?, serialno =? where +id= ?"; my $sth; # Wrap db activities in eval so non-fatal eval($sth = $db->prepare($sql) ); eval( $sth->execute($name,$rank,$serialno,$id); ) unless $@; # return $@?$@:1; } my $q = new CGI; my $d = new DBI(....); my $id = $q->param('id'); ... my $serialno = $q->param('serialno'); update_mystable($d,$id,$name,$rank,$serialno);

    Is that what you meant to ask? Try looking at Merlyn's page or Ovid's page for hints on CGI tips.

    --

    Brother Frankus.

    ¤

Re: best method to update mysql records over the web
by Juerd (Abbot) on Jun 28, 2002 at 13:52 UTC
Re: best method to update mysql records over the web
by krisahoch (Deacon) on Jun 28, 2002 at 14:37 UTC
    Mr Scary, Here is how I do it, and I am assuming that you have a database connection.
    use strict; use diagnostics; ############################################################ # Using the \$STATEMENT varible makes it easier # for me to prepare it for 'quotes' that are needed # by SQL (MySql) and helps me resolve my variables safely. # ---------------------------------------------------------- my $STATEMENT = "UPDATE SUPPORTDESK.CONTACT_PERSON SET SALUTATION=$S +ALUTATION,FIRST_NAME=$FIRST_NAME,MIDDLE_INITIAL=$MIDDLE_INITIAL,LAST_ +NAME=$LAST_NAME,NOTES=$NOTES WHERE CONTACT_PERSON.CONTACT_PERSON_ID=$ +CONTACT_PERSON_ID"; ############################################################ # Prepare the statement for database execution. This inserts # quotes where needed # ---------------------------------------------------------- # $DBACCESS is the database connection handle '$dbh' my $COMMAND = $DBACCESS->prepare($STATEMENT); #Executed the statment. $COMMAND->execute; ############################################################ # Kill the statement handle so that you can safely # disconnect from the database. # ---------------------------------------------------------- $COMMAND->finish;
    Word of caution. I use InnoDB style database tables which autocommit. Autocommitting means once you put information there, it is permanent. No rolling back, and the information is flushed to the disk. If you don't autocommit then the Statement will be more like this... my $STATEMENT = "BEGIN; UPDATE SUPPORTDESK.CONTACT_PERSON  SET   SALUTATION=$SALUTATION,FIRST_NAME=$FIRST_NAME,MIDDLE_INITIAL=$MIDDLE_INITIAL,LAST_NAME=$LAST_NAME,NOTES=$NOTES WHERE CONTACT_PERSON.CONTACT_PERSON_ID=$CONTACT_PERSON_ID; COMMIT"; I hope this helps. Kristofer Hoch