multiple row updates in MYSQL using HTML

G

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=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
 
E

Erik Tank

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.
 
G

Gary

Erik Tank said:
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.

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
 
E

Erik Tank

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.

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.
 
G

Gary

Erik Tank said:
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 :).

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


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
 
J

Joe Smith

Gary said:
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
 
G

Gary

Joe Smith said:
Gary said:
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


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
 
J

Joe Smith

Gary said:
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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,755
Messages
2,569,536
Members
45,013
Latest member
KatriceSwa

Latest Threads

Top