multiple row updates in MYSQL using HTML

Discussion in 'Perl' started by Gary, Jan 16, 2004.

  1. Gary

    Gary Guest

    I haver a table of students - Say 100 students that I need to be able to
    update/delete and amend. I know I can do this one student at a time which is
    simple but lets say I want to see all the students on the screen at the same
    time, modify some, mark some for deletion and even have blank fields at the
    end to add a new record.

    In HTML which is generated I label each row and input field with a
    name/number combination i.e <input type=text value=mysqlvalue
    name=field$cnt> where the number ($cnt) is incremented each row and then I
    pass these fields (all of them) to another routine to actually do the
    update.

    With 100 Students displayed I need about 500 variables minimum so I can
    store all new values entered on any of the 100 students and then pass them
    all to another perl script to perform the update.

    There must be a better way - Any pointers or examples anywhere would be
    excellent.

    I basically need maintenance of one table with unlimited rows containing say
    5 fields using Perl/HTML and MySQL.

    Many thanks
    Gary
    Gary, Jan 16, 2004
    #1
    1. Advertising

  2. Gary

    Erik Tank Guest

    Sound like you have right approach.

    Since I don't know the structure of your table I will just assume that
    each record has a unique ID field. I would use this field instead of
    a simple counter value.

    The only other thing that I can think of is to add a checkbox next to
    each row and then you can only update the records that are check. You
    may also want to add a second button that updates each row no matter
    if the checkbox is checked incase you are updating most of the records
    and hitting the check box becomes a pain.


    On Thu, 15 Jan 2004 19:52:33 -0500, "Gary" <>
    wrote:

    >I haver a table of students - Say 100 students that I need to be able to
    >update/delete and amend. I know I can do this one student at a time which is
    >simple but lets say I want to see all the students on the screen at the same
    >time, modify some, mark some for deletion and even have blank fields at the
    >end to add a new record.
    >
    >In HTML which is generated I label each row and input field with a
    >name/number combination i.e <input type=text value=mysqlvalue
    >name=field$cnt> where the number ($cnt) is incremented each row and then I
    >pass these fields (all of them) to another routine to actually do the
    >update.
    >
    >With 100 Students displayed I need about 500 variables minimum so I can
    >store all new values entered on any of the 100 students and then pass them
    >all to another perl script to perform the update.
    >
    >There must be a better way - Any pointers or examples anywhere would be
    >excellent.
    >
    >I basically need maintenance of one table with unlimited rows containing say
    >5 fields using Perl/HTML and MySQL.
    >
    >Many thanks
    >Gary
    >
    Erik Tank, Jan 16, 2004
    #2
    1. Advertising

  3. Gary

    Gary Guest

    "Erik Tank" <> wrote in message
    news:...
    > Sound like you have right approach.
    >
    > Since I don't know the structure of your table I will just assume that
    > each record has a unique ID field. I would use this field instead of
    > a simple counter value.
    >
    > The only other thing that I can think of is to add a checkbox next to
    > each row and then you can only update the records that are check. You
    > may also want to add a second button that updates each row no matter
    > if the checkbox is checked incase you are updating most of the records
    > and hitting the check box becomes a pain.
    >
    >
    > On Thu, 15 Jan 2004 19:52:33 -0500, "Gary" <>
    > wrote:
    >
    > >I haver a table of students - Say 100 students that I need to be able to
    > >update/delete and amend. I know I can do this one student at a time which

    is
    > >simple but lets say I want to see all the students on the screen at the

    same
    > >time, modify some, mark some for deletion and even have blank fields at

    the
    > >end to add a new record.
    > >
    > >In HTML which is generated I label each row and input field with a
    > >name/number combination i.e <input type=text value=mysqlvalue
    > >name=field$cnt> where the number ($cnt) is incremented each row and then

    I
    > >pass these fields (all of them) to another routine to actually do the
    > >update.
    > >
    > >With 100 Students displayed I need about 500 variables minimum so I can
    > >store all new values entered on any of the 100 students and then pass

    them
    > >all to another perl script to perform the update.
    > >
    > >There must be a better way - Any pointers or examples anywhere would be
    > >excellent.
    > >
    > >I basically need maintenance of one table with unlimited rows containing

    say
    > >5 fields using Perl/HTML and MySQL.
    > >
    > >Many thanks
    > >Gary
    > >

    >


    That is scary.

    Asuume I want to have an unlimited number of rows maintained by the user,
    say 200 rows. If there are 5 fields in the row then I would need to declare
    1000 fields just to update the table unless I use some changedrecord type
    field the user can tick. This is ugly ! Is this really the only way ?

    Even with a check box I would still need to pass all of the records anyway
    just to see which they wanted to actually update. Currently I hold the
    previous key value of the row and pass that to the next cgi so I can update
    using WHERE key-field=previous-value, but again it is messy !

    Do you know any examples of a multipe row update using MySQL and Perl and
    HTML ?

    Gary
    Gary, Jan 16, 2004
    #3
  4. Gary

    Erik Tank Guest

    On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <>
    wrote:

    >
    >"Erik Tank" <> wrote in message
    >news:...
    >> Sound like you have right approach.
    >>

    <.. SNIP ..>
    >Asuume I want to have an unlimited number of rows maintained by the user,
    >say 200 rows. If there are 5 fields in the row then I would need to declare
    >1000 fields just to update the table unless I use some changedrecord type
    >field the user can tick. This is ugly ! Is this really the only way ?

    TIMTOWTDI - This is Perl so I am sure that there is a slick way to get
    this done but ugly is at least a functional second best :).
    >
    >Even with a check box I would still need to pass all of the records anyway
    >just to see which they wanted to actually update. Currently I hold the
    >previous key value of the row and pass that to the next cgi so I can update
    >using WHERE key-field=previous-value, but again it is messy !


    >
    >Do you know any examples of a multipe row update using MySQL and Perl and
    >HTML ?

    Sorry but I don't know of any off the top of my head.

    >
    >Gary
    >


    I believe that my unfamiliartiy with what you are doing (overall
    design, thought, functionality, etc) is probably hampering any input I
    give you the most. IMHO the best way to find a good solution is to
    have someone that can sit next to you and the two of you throw design
    ideas back and forth - the person you do it with doesn't even need to
    be a programmer just someone who can think critically and outside of
    the box.
    Erik Tank, Jan 17, 2004
    #4
  5. Gary

    Gary Guest

    "Erik Tank" <> wrote in message
    news:...
    > On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <>
    > wrote:
    >
    > >
    > >"Erik Tank" <> wrote in message
    > >news:...
    > >> Sound like you have right approach.
    > >>

    > <.. SNIP ..>
    > >Asuume I want to have an unlimited number of rows maintained by the user,
    > >say 200 rows. If there are 5 fields in the row then I would need to

    declare
    > >1000 fields just to update the table unless I use some changedrecord type
    > >field the user can tick. This is ugly ! Is this really the only way ?

    > TIMTOWTDI - This is Perl so I am sure that there is a slick way to get
    > this done but ugly is at least a functional second best :).
    > >
    > >Even with a check box I would still need to pass all of the records

    anyway
    > >just to see which they wanted to actually update. Currently I hold the
    > >previous key value of the row and pass that to the next cgi so I can

    update
    > >using WHERE key-field=previous-value, but again it is messy !

    >
    > >
    > >Do you know any examples of a multipe row update using MySQL and Perl and
    > >HTML ?

    > Sorry but I don't know of any off the top of my head.
    >
    > >
    > >Gary
    > >

    >
    > I believe that my unfamiliartiy with what you are doing (overall
    > design, thought, functionality, etc) is probably hampering any input I
    > give you the most. IMHO the best way to find a good solution is to
    > have someone that can sit next to you and the two of you throw design
    > ideas back and forth - the person you do it with doesn't even need to
    > be a programmer just someone who can think critically and outside of
    > the box.


    The problem is Perl/HTML is incredibly restrictive and you cannot for
    instance update the SQL database without calling another cgi script and
    passing all the required fields for update. At least I think not. It would
    be good if I could create a button that said 'Update' and it actually allowe
    d me to run more of the cgi in the running script rather than calling
    another.

    Anyway I have written it now using lots and lots fo variables and
    restriction the number of rows that can be added and amended at one time.

    Many thanks
    Gary
    Gary, Jan 19, 2004
    #5
  6. Gary

    Joe Smith Guest

    Gary wrote:

    > With 100 Students displayed I need about 500 variables minimum so I can
    > store all new values entered on any of the 100 students and then pass them
    > all to another perl script to perform the update.


    Instead of using 500 variables, you can use a hash of arrays.
    (Five keys in the hash, each pointing to arrays with 100 elements.)

    <input type="text" name="student[0]" size="20">
    <input type="text" name="stu_id[0]" size="20">
    <input type="checkbox" name="delete[0]" value="1">

    <input type="text" name="student[1]" size="20">
    <input type="text" name="stu_id[2]" size="20">
    <input type="checkbox" name="delete[1]" value="1">

    Then use something like this when looping through the form keys:

    if ($key =~ /(.*?)\[(\d+)\]/) {
    $data{$1}[$2] = $value;
    } elsif ((\w+)(.*?)/) {
    $data{$1}{$2} = $value;
    } else {
    warn "Unparsable input: key=$key value=$value";
    }

    For convenience:
    my @students = @{$data{student}} or warn;
    my @stu_ids = @{$data{stu_id}} or warn;
    my @deletes = @{$data{delete}} or warn;

    -Joe

    --
    I love my TiVo - http://www.inwap.com/u/joe/tivo/
    Joe Smith, Jan 19, 2004
    #6
  7. Gary

    Gary Guest

    "Joe Smith" <> wrote in message
    news:0uROb.85309$5V2.115114@attbi_s53...
    > Gary wrote:
    >
    > > With 100 Students displayed I need about 500 variables minimum so I can
    > > store all new values entered on any of the 100 students and then pass

    them
    > > all to another perl script to perform the update.

    >
    > Instead of using 500 variables, you can use a hash of arrays.
    > (Five keys in the hash, each pointing to arrays with 100 elements.)
    >
    > <input type="text" name="student[0]" size="20">
    > <input type="text" name="stu_id[0]" size="20">
    > <input type="checkbox" name="delete[0]" value="1">
    >
    > <input type="text" name="student[1]" size="20">
    > <input type="text" name="stu_id[2]" size="20">
    > <input type="checkbox" name="delete[1]" value="1">
    >
    > Then use something like this when looping through the form keys:
    >
    > if ($key =~ /(.*?)\[(\d+)\]/) {
    > $data{$1}[$2] = $value;
    > } elsif ((\w+)(.*?)/) {
    > $data{$1}{$2} = $value;
    > } else {
    > warn "Unparsable input: key=$key value=$value";
    > }
    >
    > For convenience:
    > my @students = @{$data{student}} or warn;
    > my @stu_ids = @{$data{stu_id}} or warn;
    > my @deletes = @{$data{delete}} or warn;
    >
    > -Joe
    >
    > --
    > I love my TiVo - http://www.inwap.com/u/joe/tivo/



    Can these arrays be passed between HTML / CGI scripts like regular
    variables.

    Also could you comment the code above a bit - I do not use Perl a whole lot
    and it would save me time working out exactly what it does.

    Gary
    Gary, Jan 20, 2004
    #7
  8. Gary

    Joe Smith Guest

    Gary wrote:

    > "Joe Smith" <> wrote in message
    > news:0uROb.85309$5V2.115114@attbi_s53...
    >
    >>Gary wrote:
    >>
    >>
    >>>With 100 Students displayed I need about 500 variables minimum so I can
    >>>store all new values entered on any of the 100 students and then pass

    >
    > them
    >
    >>>all to another perl script to perform the update.

    >>
    >>Instead of using 500 variables, you can use a hash of arrays.
    >>(Five keys in the hash, each pointing to arrays with 100 elements.)
    >>
    >> <input type="text" name="student[0]" size="20">
    >> <input type="text" name="stu_id[0]" size="20">
    >> <input type="checkbox" name="delete[0]" value="1">
    >>
    >> <input type="text" name="student[1]" size="20">
    >> <input type="text" name="stu_id[1]" size="20">
    >> <input type="checkbox" name="delete[1]" value="1">
    >>


    > Can these arrays be passed between HTML / CGI scripts like regular
    > variables.


    Arrays cannot be passed that way but information can. That is:
    1) Get original data into a Perl array (or do one row at a time).
    2) Convert the data to something the browser can understand.
    3) Send it as part of an HTML form that the user can modify.
    4) Get the form data back when user submits form.
    5) Parse the form, putting data values into Perl variables.
    Note: Use arrays or hashes, not gazillion scalars.
    6) Process the data, either as an entire array, or one row at
    a time. The latter is much better for reporting what
    was successfully modified and what was not.

    Here is something from step 2:

    <input type="text" name="student[0]" size="20">

    That is HTML generated by a CGI or ASP or PHP or anything that
    can query a database, get a bunch of rows, then write the information
    for each row as an <INPUT> item in an HTML form. To keep things
    simple, I deliberately did not include things like <TR>...</TR> that
    would make the resulting HTML look good. Note that the argument
    for name="" is not a Perl variable, but rather an identifier that
    can be used to store the returned value back into a suitable Perl
    variable (which could be an element of an array or hash).

    >>Then use something like this when looping through the form keys:
    >>
    >> if ($key =~ /(.*?)\[(\d+)\]/) {
    >> $data{$1}[$2] = $value;
    >> } elsif ((\w+)(.*?)/) {
    >> $data{$1}{$2} = $value;
    >> } else {
    >> warn "Unparsable input: key=$key value=$value";
    >> }
    >>
    >>For convenience:
    >> my @students = @{$data{student}} or warn;
    >> my @stu_ids = @{$data{stu_id}} or warn;
    >> my @deletes = @{$data{delete}} or warn;


    > Also could you comment the code above a bit - I do not use Perl
    > a whole lot and it would save me time working out exactly what
    > it does.


    The following assumes you've already used CGI.pm to create $query.

    @names = $query->param; # Get a list of the form element names.
    # In this example, it will be ('student[0]', 'student[1]',
    # 'stu_id[0]', 'stu_id[1]', 'delete[0]', 'delete[1]')
    # but not necessarily in any particular order.
    foreach $key (@names) {
    $value = $query->param($key); # Get the value the brower sent back

    # Look for things like $key='student[0]' $value='Joe Smith'
    # Also accept things like $key='course{teacher}' $value='Gary'

    if ($key =~ /(.*?)\[(\d+)\]/) {
    $data{$1}[$2] = $value; # Store into hash of arrays
    } elsif ((\w+)(.*?)/) {
    $data{$1}{$2} = $value; # Store into hash of hashes
    }
    }

    That last bit could be written as:

    $key =~ /^student\[(\d+)\]/ and $student[$1] = $value;
    $key =~ /^stu_id\[(\d+)\]/ and $stu_id[$1] = $value;
    $key =~ /^delete\[(\d+)\]/ and $delete[$1] = $value;
    $key =~ /^course(\{.*?\})/ and $course{$1} = $value;

    The program doing the CGI parsing ends up with three 100-element
    arrays (plus a hash) instead of 300+ variables.

    You can then loop through the arrays and execute SQL statements
    one student at a time. When done, send back an HTML page listing
    how many of the requested changes were successful.
    -Joe

    --
    I love my TiVo - http://www.inwap.com/u/joe/tivo/
    Joe Smith, Jan 20, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dave
    Replies:
    4
    Views:
    1,350
    Steve Holden
    Nov 18, 2010
  2. troutbum

    Cannot Find Row Index after using row filter

    troutbum, Jan 22, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    10
    Views:
    459
    Andre
    Feb 19, 2004
  3. D
    Replies:
    0
    Views:
    197
  4. julian
    Replies:
    8
    Views:
    438
    Avatar
    Apr 6, 2006
  5. Xavier Noria

    MySQL gem always updates

    Xavier Noria, Jul 10, 2006, in forum: Ruby
    Replies:
    0
    Views:
    100
    Xavier Noria
    Jul 10, 2006
Loading...

Share This Page