473,698 Members | 2,174 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

multiple row updates in MYSQL using HTML

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
Jul 19 '05 #1
7 5653
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" <re*****@netlin k.info>
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=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


Jul 19 '05 #2

"Erik Tank" <er**@jundy.com > wrote in message
news:08******** *************** *******@news.te ranews.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" <re*****@netlin k.info>
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 issimple but lets say I want to see all the students on the screen at the sametime, modify some, mark some for deletion and even have blank fields at theend 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 Ipass 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 themall 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 say5 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
Jul 19 '05 #3
On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <re*****@netlin k.info>
wrote:

"Erik Tank" <er**@jundy.com > wrote in message
news:08******* *************** ********@news.t eranews.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.
Jul 19 '05 #4

"Erik Tank" <er**@jundy.com > wrote in message
news:b0******** *************** *******@news.te ranews.com...
On Fri, 16 Jan 2004 14:26:11 -0500, "Gary" <re*****@netlin k.info>
wrote:

"Erik Tank" <er**@jundy.com > wrote in message
news:08******* *************** ********@news.t eranews.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

declare1000 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 anywayjust 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 updateusing 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

Jul 19 '05 #5
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/
Jul 19 '05 #6

"Joe Smith" <Jo*******@inwa p.com> wrote in message
news:0uROb.8530 9$5V2.115114@at tbi_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
Jul 19 '05 #7
Gary wrote:
"Joe Smith" <Jo*******@inwa p.com> wrote in message
news:0uROb.8530 9$5V2.115114@at tbi_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{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/
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2258
by: Comcast | last post by:
Greetings, I am writing a very simple script that pulls a resultset out of a database. These rows are then made into form variables where the user has the option to update multiple entries, hit submit and the system updates the appropriate fields. I am using the convention of "variable" in the input form, it allows me to loop through all the results and update the database. This solution is working fine, but I hate the idea that I'm...
2
12742
by: RJ | last post by:
We currently send product releases to our customers, and often have to include scripts that need to be ran on the Oracle databases (also do it for SqlServer customers, but we use a different set of scripts for that). Some of the Sql scripts can be quite long, and so we break them out to their own script files and call them all from one main ..sql file, using sqlplus to execute it all. The admin would run the file like:
0
1328
by: Paul DuBois | last post by:
The MySQL manual section that discusses how to run multiple MySQL servers on a single machine now includes information for Windows. I'd appreciate it if people who try these instructions would report to me any problems or things that seem unclear. The instructions are here: http://www.mysql.com/doc/en/Multiple_servers.html
0
1711
by: Stefan Kuhn | last post by:
Hi everybody, I want to establish replication between multiple (currently three) servers. Updates/inserts/deletes may happen on all of them. The idea I came up with was to do a "circle"-replication. The problem with this is, that if one server fails the rest may not get updates (until the server is back, obviously). Does anybody have an idea how to establish such a replication in a fail-save manner ? Thanks, Stefan
1
2034
by: Sergio | last post by:
Hi everybody, I have the following scenario. Several computers with shared disk in a LAN. Each of these computer has a MySQL server that serves several databases. I have several clients that communicate with a Java process that I have in each computer to answer queries for a given database (it connects to the MySQL server). I also have a Java process that updates continuously the databases; this process executes only in the local...
1
1880
by: Craig Stadler | last post by:
Using mysql 4.0.23- What is the best way to execute several (hundreds of) inserts and updates? Rather than issuing tons of individual inserts and updates, can I send the strings to a text file and then have mysql do them all?? IE : query.txt insert into table1 (col1,col2) values (1,2);
7
1645
by: Martin Woolley | last post by:
Hi, could someone explain the following please? I have a Java application (app A) which "polls" a MySQL database table for records with a certain column set to 0. It runs continuously, utilising the same commection repeatedly. The other day, I had a support call raised, whereby there were clearly suitable records in the database, but my application was not selecting them.
4
16754
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from customers; I can execute each statement individually but get the 'you have an error in
19
3472
by: davidgordon | last post by:
Hi, I need some pointers/help on how to do the following if it possible: In my access db, I have the following: Tables: Products, Sub-Assembly, Product-Pack Table, Products
0
9148
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8884
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7708
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6515
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5857
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4358
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4611
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3034
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2319
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.