Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > multiple row updates in MYSQL using HTML

Reply
Thread Tools

multiple row updates in MYSQL using HTML

 
 
Gary
Guest
Posts: n/a
 
      01-16-2004
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


 
Reply With Quote
 
 
 
 
Erik Tank
Guest
Posts: n/a
 
      01-16-2004
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" <(E-Mail Removed)>
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
>


 
Reply With Quote
 
 
 
 
Gary
Guest
Posts: n/a
 
      01-16-2004

"Erik Tank" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) s.com...
> 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" <(E-Mail Removed)>
> 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


 
Reply With Quote
 
Erik Tank
Guest
Posts: n/a
 
      01-17-2004
On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <(E-Mail Removed)>
wrote:

>
>"Erik Tank" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed) ws.com...
>> 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.
 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      01-19-2004

"Erik Tank" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) s.com...
> On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <(E-Mail Removed)>
> wrote:
>
> >
> >"Erik Tank" <(E-Mail Removed)> wrote in message
> >news:(E-Mail Removed) ws.com...
> >> 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



 
Reply With Quote
 
Joe Smith
Guest
Posts: n/a
 
      01-19-2004
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/
 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      01-20-2004

"Joe Smith" <(E-Mail Removed)> 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


 
Reply With Quote
 
Joe Smith
Guest
Posts: n/a
 
      01-20-2004
Gary wrote:

> "Joe Smith" <(E-Mail Removed)> 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/
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Python and Connecting to MySQL remotely WITHOUT MySQL installedon local computer dave Python 4 11-18-2010 04:19 AM
using mysql client to read a file to create a table in a database in a mysql server julian Ruby 8 04-06-2006 10:29 AM
multiple select but ......php/mysql/html The Legend HTML 0 08-07-2005 12:38 PM
ok I can do a totals row but how about a percentage row after each data row D ASP .Net Datagrid Control 0 05-23-2005 04:10 PM
Cannot Find Row Index after using row filter troutbum ASP .Net Datagrid Control 10 02-19-2004 12:27 AM



Advertisments