Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

mysql: edit mutliple forms with one submit button

by Zecho (Hermit)
on Jan 12, 2003 at 01:37 UTC ( #226177=perlquestion: print w/replies, xml ) Need Help??

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

OK I know how to manage mysql tables well enough, but I need something different..

I need to be able to print a list of entries which contain different fields. No problem there... but say it's a user list, and I want to edit each users permissions.

Pedro - Montoya - pedro@motoya.net - dept 7 - admin [] #<~ checkbox Lisa - Brannigan - lisa@hotmail.com - dept 6 - admin [x] ...... [submit]
How do I update each row with one submit button? Or even better... Should I assign all of the variables in each line to a hash and then loop through each hash when I execute the update statement?

Replies are listed 'Best First'.
Re: mysql: edit mutliple forms with one submit button
by pfaut (Priest) on Jan 12, 2003 at 02:11 UTC

    Take all of the fields associated with one database record and give them all the same suffix. You can use a counter to generate suffixes. Add an additional hidden field to hold the number of records on the form. For example (using the data you provided):

    <input type="hidden" name="userid_1" value="1"> <input type="text" name="firstname_1" value="Pedro"> - <input type="text" name="lastname_1" value="Montoya"> - <input type="text" name="email_1" value="pedro@motoya.net"> - <input type="text" name="department_1" value="dept 7"> - Admin <input type="checkbox" name="admin_1"> <br> <input type="hidden" name="userid_2" value="2"> <input type="text" name="firstname_2" value="Lisa"> - <input type="text" name="lastname_2" value="Brannigan"> - <input type="text" name="email_2" value="lisa@hotmail.com"> - <input type="text" name="department_2" value="dept 6"> - Admin <input type="checkbox" name="admin_2" checked> <input type="hidden" name="count" value="2">

    Process the returned data with something like this.

    my $q = new CGI; my $cnt = $q->param("count"); # validate for numeric and value in rang +e for my $sfx (1..$cnt) { my $userid = $q->param("userid_$sfx"); my $first = $q->param("firstname_$sfx"); my $last = $q->param("lastname_$sfx"); my $email = $q->param("email_$sfx"); my $dept = $q->param("department_$sfx"); my $admin = $q->param("admin_$sfx"); # validate all values above and update user record }

    Since you're presenting many records to the user at one time, you will probably have to update all of your database records since there won't be any way to determine which were modified. You may be able to detect modifications with some javascript. If so, I would add a second hidden field for each record whose value could be changed by the javascript to indicate that the record needs an update.

    --- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';
Re: mysql: edit mutliple forms with one submit button
by Zaxo (Archbishop) on Jan 12, 2003 at 02:13 UTC

    You could encode the field names for each record with the corresponding primary key from the database, e.g. ...name="firstname.10123" default="Pedro" ... name="firstname.12139" default="Lisa"...

    If the checkbox indicates a record to be changed, grep over $query->param for the common part of the checkbox names to extract the primary keys to look for.

    This will make for large queries. It may be worthwhile to cook up some javascript to weed out unchanged fields on the client side.

    Caution, that is very insecure. It is only suitable for ssl connections with authentication for admin. If just anybody could use this, the keys could be forged with ease.

    After Compline,
    Zaxo

Re: mysql: edit mutliple forms with one submit button
by Trimbach (Curate) on Jan 12, 2003 at 03:10 UTC
    It's worth noting that MySQL will optimize UPDATEs so that it won't update a field to a value that's already stored in that field... UPDATE table SET name="John Smith" WHERE id=1 will turn into a no-op if name is already set to "John Smith".

    Unless your user list is very very long it might be a good idea to just update every field for every record every time "submit" is pressed. I suspect the performance impact will be minimal, and you don't have to mess with JavaScript or anything hinky like that.

    Gary Blackburn
    Trained Killer

      UPDATE table SET name="John Smith" WHERE id=1 will turn into a no-op if name is already set to "John Smith".

      For very flexible values of no-op.

      — Arien

Re: mysql: edit mutliple forms with one submit button
by Zecho (Hermit) on Jan 12, 2003 at 01:41 UTC
    change
    Pedro - Montoya - pedro@motoya.net - dept 7 - admin [] #<~ checkbox Lisa - Brannigan - lisa@hotmail.com - dept 6 - admin [x]
    to
    [Pedro] - [Montoya] - [pedro@motoya.net] - [dept 7] - admin [] #<~ che +ckbox [Lisa] - [Brannigan] - [lisa@hotmail.com] - [dept 6] - admin [x]
    [Pedro] etc. would be textfields that can be editied at the same time..
Re: mysql: edit mutliple forms with one submit button
by Zecho (Hermit) on Jan 12, 2003 at 01:54 UTC
    and btw, I'm not freeloading.. nor asking for you to produce code for me... I am simply seeking suggestions on which avenue to take... I don't mind doing my own work :)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://226177]
Approved by chromatic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2020-11-28 23:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?