multiple row updates in MYSQL using HTML

[Gary]

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=mysqlvalu e
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]

Re: multiple row updates in MYSQL using HTML

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" <reachus@netlin k.info>
wrote:
[color=blue]
>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=mysqlvalu e
>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
>[/color]

[Gary]

Re: multiple row updates in MYSQL using HTML


"Erik Tank" <erik@jundy.com > wrote in message
news:08b1359e7e 64dd0a2c57fbf7f 0a93ac6@news.te ranews.com...[color=blue]
> 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" <reachus@netlin k.info>
> wrote:
>[color=green]
> >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[/color][/color]
is[color=blue][color=green]
> >simple but lets say I want to see all the students on the screen at the[/color][/color]
same[color=blue][color=green]
> >time, modify some, mark some for deletion and even have blank fields at[/color][/color]
the[color=blue][color=green]
> >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=mysqlvalu e
> >name=field$cnt > where the number ($cnt) is incremented each row and then[/color][/color]
I[color=blue][color=green]
> >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[/color][/color]
them[color=blue][color=green]
> >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[/color][/color]
say[color=blue][color=green]
> >5 fields using Perl/HTML and MySQL.
> >
> >Many thanks
> >Gary
> >[/color]
>[/color]

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

[Erik Tank]

Re: multiple row updates in MYSQL using HTML

On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <reachus@netlin k.info>
wrote:
[color=blue]
>
>"Erik Tank" <erik@jundy.com > wrote in message
>news:08b1359e7 e64dd0a2c57fbf7 f0a93ac6@news.t eranews.com...[color=green]
>> Sound like you have right approach.
>>[/color][/color]
<.. SNIP ..>[color=blue]
>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 ?[/color]
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 :-).[color=blue]
>
>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 ![/color]
[color=blue]
>
>Do you know any examples of a multipe row update using MySQL and Perl and
>HTML ?[/color]
Sorry but I don't know of any off the top of my head.
[color=blue]
>
>Gary
>[/color]

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.

[Gary]

Re: multiple row updates in MYSQL using HTML


"Erik Tank" <erik@jundy.com > wrote in message
news:b00849e3dd d985a54b2a115d7 84adc9d@news.te ranews.com...[color=blue]
> On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <reachus@netlin k.info>
> wrote:
>[color=green]
> >
> >"Erik Tank" <erik@jundy.com > wrote in message
> >news:08b1359e7 e64dd0a2c57fbf7 f0a93ac6@news.t eranews.com...[color=darkred]
> >> Sound like you have right approach.
> >>[/color][/color]
> <.. SNIP ..>[color=green]
> >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[/color][/color]
declare[color=blue][color=green]
> >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 ?[/color]
> 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 :-).[color=green]
> >
> >Even with a check box I would still need to pass all of the records[/color][/color]
anyway[color=blue][color=green]
> >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[/color][/color]
update[color=blue][color=green]
> >using WHERE key-field=previous-value, but again it is messy ![/color]
>[color=green]
> >
> >Do you know any examples of a multipe row update using MySQL and Perl and
> >HTML ?[/color]
> Sorry but I don't know of any off the top of my head.
>[color=green]
> >
> >Gary
> >[/color]
>
> 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.[/color]

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

[Joe Smith]

Re: multiple row updates in MYSQL using HTML

Gary wrote:
[color=blue]
> 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.[/color]

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/

[Gary]

Re: multiple row updates in MYSQL using HTML


"Joe Smith" <Joe.Smith@inwa p.com> wrote in message
news:0uROb.8530 9$5V2.115114@at tbi_s53...[color=blue]
> Gary wrote:
>[color=green]
> > 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[/color][/color]
them[color=blue][color=green]
> > all to another perl script to perform the update.[/color]
>
> 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/[/color]


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

[Joe Smith]

Re: multiple row updates in MYSQL using HTML

Gary wrote:
[color=blue]
> "Joe Smith" <Joe.Smith@inwa p.com> wrote in message
> news:0uROb.8530 9$5V2.115114@at tbi_s53...
>[color=green]
>>Gary wrote:
>>
>>[color=darkred]
>>>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[/color][/color]
>
> them
>[color=green][color=darkred]
>>>all to another perl script to perform the update.[/color]
>>
>>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">
>>[/color][/color]
[color=blue]
> Can these arrays be passed between HTML / CGI scripts like regular
> variables.[/color]

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).
[color=blue][color=green]
>>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;[/color][/color]
[color=blue]
> 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.[/color]

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{te acher}' $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/