Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
Greetings, esteemed monks!

I have this page which is to display the contents of a database, and, if desired, edit one of the records. The database consists of a table which is a resource, which is defined as a name, a link, and availability, and a table which indicates which matrix locations the resource goes in.

For some reason, when editing one of the rows, DBI returns 1, meaning one row is affected in the UPDATE statement, but in the table print out below, the change isn't being reflected. I've gone over this several times to no avail. Can anyone with a fresh perspective take a look and help?

Apologies for the long code, you pretty much have to see the whole thing.

#!/usr/bin/perl -w use strict; use CGI; use CGI::Carp qw(warningsToBrowser fatalsToBrowser); use DBI; use autodie; #Total hack to use a module surrepetitiously installed on a GoDaddy ac +count use lib 'SOMEDIR'; use Text::CSV; use lib 'SOMEOTHERDIR'; use Regexp::Common; #use Data::Table; $\="\n"; my $db="SOMEDB"; my $dbun="SOMEUN"; my $dbpw = 'SOMEPW'; my $dbhost=""; my $res_tbl="resource_list"; my $loc_tbl="matrix_locations"; my $drh = DBI->install_driver("mysql") || print "install_driver failed +: $DBI::errstr <br/>"; my $dsn = "DBI:mysql:database=$db;host=$dbhost"; my $dbh = DBI->connect($dsn,$dbun,$dbpw) || print "DB connect failed: +$DBI::errstr <br/>"; my $res_select_stmt = "Select * from $res_tbl order by id"; my $loc_select_stmt = "Select location from $loc_tbl where resource_id + = ?"; my @data_elements = ('id','name','link','availability','locations'); my @avail_options = ('Free','Free w/Membership','Available for Purchas +e'); my $q = CGI->new; # create new CGI object print $q->header; print $q->start_html(-dtd=>'html',-title=>'Procedural Harm Affinity Gr +oup Resource Data Base Display and Edit'); # start the HTML #Here we are going to have two steps: #1. UPDATE the master resource table with the new data #2. Delete everything out of the locations table, and re-insert the n +ew values. #Note: I know this isn't very efficient (we should check if anything +changed before doing either) #But this is to limit my coding time, which is at a premium. #we'll also be using this as a flag my $id_to_update=0; my %params = $q->Vars; #Have to be careful here, there should only ever be one param that mat +ches this. foreach my $param (keys %params){ if ($param =~ /^Submit(\d+)/){ $id_to_update=$1; } } #OK I totally forgot that I didn't just concatenate the ID to the end +of the field name, I put an underscore in there. if ($id_to_update){ #first, update the resources table my $update_res_stmt = "UPDATE $res_tbl SET name=?, link=?, availab +ility=? WHERE id=?"; my $updated_name = $params{"name_$id_to_update"}; my $updated_link = $params{"link_$id_to_update"}; my $updated_avail = $params{"availability_$id_to_update"}; if ($updated_link =~ $RE{URI}){ print $q->p("Updating resource list item with name=$updated_na +me, link=$updated_link, availability=$updated_avail for id=$id_to_upd +ate"); my $sth = $dbh->prepare($update_res_stmt) or warn "prepare UPD +ATE statement failed: $dbh->errstr"; my $rv = $sth->execute($updated_name,$updated_link,$updated_av +ail,$id_to_update) || warn "Execute update failed: $dbh->errstr"; $sth->finish; print $q->p("Resources table updated with return value '$rv'." +); }else{ print $q->p("Error: Please make sure resource_link is a valid + URL. This includes the http:// part as well as the domain and path. +"); } #next, insert the locations #oh man, I forgot the id for this param. BAD CODER print $q->p("Locations param is '",$params{"locations_$id_to_updat +e"},"'"); my @locations = split /,/ , $params{"locations_$id_to_update"}; my $locs_legit_flag=1; my $offending_loc; foreach my $loc (@locations){ #print "Checking matrix location $loc"; if ($loc !~ m/^([A-Za-z])(\d+)$/){ $locs_legit_flag = 0; $offending_loc = $loc; } } if (! scalar @locations){ print $q->p("Somehow, the list of matrix locations has no loca +tions in it. Please try again."); }elsif ($locs_legit_flag){ #clear out the locations related to that resource ID from the +locations table my $delete_loc_stmt = "DELETE from $loc_tbl where resource_id= +?"; print $q->p("Clearing out location table records with resource +_id=$id_to_update"); my $sth = $dbh->prepare($delete_loc_stmt) or warn "prepare DEL +ETE statement failed: $dbh->errstr"; $sth->execute($id_to_update) || warn "Execute DELETE failed: $ +dbh->errstr"; print $q->p("Locations table cleared."); $sth->finish; #OK time to insert now my $loc_insert_stmt = "insert into $loc_tbl (resource_id, loca +tion) values (?,?)"; $sth = $dbh->prepare($loc_insert_stmt) or warn "prepare INSERT + statement failed: $dbh->errstr"; @locations = map( uc($_), @locations ); #trying to use a hash to remove duplicates #this one liner is from Effective Perl Programming via PerlMon +ks @locations = sort keys %{ { map { $_, 1 } @locations } }; foreach my $loc (@locations){ $sth->execute($id_to_update,$loc) || print "execute statem +ent failed: $DBI::errstr <br/>"; } $sth->finish; }else{ #This means that $locs_legit_flag was cleared #We did this whole escapeHTML thing in so re-doi +ng it here #try to build the string ahead of time rather than interpolati +ng, so that the default call by CGI on escapeHTML runs properly my $msg = "One of the matrix locations you entered, namely '$o +ffending_loc', is invalid. All matrix locations must be a letter plus a 1-2 digit number, e.g. N9 or B19." +; #well, maybe it's not default and has to be called explicitly. $msg = CGI::escapeHTML($msg); print $q->p($msg); } } print $q->start_form; print $q->h3("Aggregate view of Procedural Harm database edit"); my $select_sth = $dbh->prepare($res_select_stmt) || print "prepare sta +tement failed: $DBI::errstr <br/>"; $select_sth->execute() || print "execute statement failed: $DBI::errst +r <br/>"; my $results = $select_sth->fetchall_arrayref(); $select_sth->finish(); my $tblstring= join '</th><th>', @data_elements; print "<table border ='1'><tr><th>$tblstring</th></tr>"; #print '<tr><td>'; foreach my $row (@$results){ print '<tr><td>'; my $id = $row->[0]; #set baseline so we get the ID my @newrow = @$row; my $i; if ( $q->param("Edit$id") ){ #start at 1 so as to skip the ID field taken care of properly for ($i = 1; $i < ((scalar @data_elements) - 1); $i++ ){ #if it's availability, let's keep it a radio group if ($data_elements[$i] eq 'availability'){ $newrow[$i] = $q->radio_group(-name=>"$data_elements[$ +i]_$id", -values=>\@avail_options, -default=>$avail_options[0], -linebreak=>'true', #-labels=>\%labels, #-attributes=>\%attributes ); }else{ $newrow[$i] = $q->textarea(-name=>"$data_elements[$i]_ +$id", -default=>$row->[$i], -rows=>5, -columns=>50); } } #whoops, was modifying $row when I needed the original values +later. #BAD Coder! BAD! #@$row = @newrow; print join '</td><td>' , @newrow; }else{ print join '</td><td>' , @$row; } print '</td><td>'; $select_sth = $dbh->prepare($loc_select_stmt) || print "prepare st +atement failed: $DBI::errstr <br/>"; $select_sth->execute($id) || print "execute statement failed: $DBI +::errstr <br/>"; my @locs; while (my $ref = $select_sth->fetchrow_arrayref()){ push @locs , $ref->[0]; } $select_sth->finish(); my $locs_for_cell = join ',' , @locs; if ( $q->param("Edit$id") ){ #dafuq was I thinking here? This is clearly the locations tex +tarea #I had it as $data_elements[$i+1 but I guess $i got incremente +d one last time to fail the conditional in the loop #I guess I'll leave it in case I add fields to the table/data +elements to the array? print $q->textarea(-name=>"$data_elements[$i]_$id", -default=> +$locs_for_cell); print $q->submit(-name=>"Submit$id",-value=>"Submit edits for +record $id"); }else{ print $locs_for_cell; print $q->submit(-name=>"Edit$id",-value=>"Edit row $id"); } print '</td></tr>'; } print '</table>'; print $q->end_form; #$select_sth->finish(); $dbh->disconnect(); print $q->end_html;

In reply to Mystified by why my UPDATE statement is not reflected in my database display by OfficeLinebacker

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others scrutinizing the Monastery: (6)
    As of 2016-07-01 09:36 GMT
    Find Nodes?
      Voting Booth?
      My preferred method of making French fries (chips) is in a ...

      Results (409 votes). Check out past polls.