Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

input form and MySQL across columns

by Da_Skipper (Initiate)
on Sep 02, 2012 at 21:41 UTC ( #991331=perlquestion: print w/replies, xml ) Need Help??
Da_Skipper has asked for the wisdom of the Perl Monks concerning the following question:

Question: How to match up input form fields across columns in MySQL database?

Problem is due to form fields must be unique.

The MySQL table:

ID type name qty1 qty2 qty3 qty4 qty5 status
1 Group1 Shoes 15 50 200 500 2500 ok
2 Group1 Tires 10 50 150 400 1000 ok
3 Group1 Stove Top 2 6 20 50 150 ok
4 Group2 other 25 60 150 325 750 banned

An example of the first input form looked something like below except an actual input field would show up where the word "input" is shown and the name would be the same as the MySQL row name. As you can see the name of each field returned is the same because using code

like this

while($ref = $sth->fetchrow_hashref()){



where "qty1" "qty2" etc is used for each MySQL table heading which returns a form with identical names for each field

<input type="text" name="Shoes" size="2"> 15

<input type="text" name="Shoes" size="2"> 50

and so on for each field as shown below

name qty1 qty2 qty3 qty4 qty5
Shoes input 15 input 50 input 200 input 500 input 2500
Tires input 10 input 50 input 150 input 400 input 1000
Stove Top input 2 input 6 input 20 input 50 input 150

the problem is each input field name must be unique so I appended each with a digit starting with the number 1 and incremented using ++. the results works fine as below. Again, substitute the word "input" with the MySQL row name and an input field.

my $number = $i +1; $newname = "$ref->{'name'}"; $newname = $newname . 1;

and then under each input string I added


like this

<input type="text" name="$newname" size="2"> 15


<input type="text" name="$newname" size="2"> 50

Gives a nicely formatted form, each field with a unique name. "name=Shoes1" "name=Shoes2" etc. The probem is, the fields no longer match the name of the row that it came from which causes a problem.

I thought about using the row/column value instead of the row name but, there is still the potential of duplicate form field names, so that wouldn't work.

name qty1 qty2 qty3 qty4 qty5
Shoes input1 15 input2 50 input3 200 input4 500 input5 2500
Tires input1 10 input2 50 input3 150 input4 400 input5 1000
Stove Top input1 2 input2 6 input3 20 input4 50 input5 150

So if the connectivity between the form and the MySQL table worked, (my fault, not perl's) the next bit of code would be to write "if input value is greater than 0, multiply the value by the qty in the MySQL row/column and then add up each subtotal by row. My first attempt returned the input value but stuck the same value into the entire column even when multiple inputs were provided. I think this is because I was using "while fetchrow" again instead of something that parsed for each individual row, not sure.

So this is where I am stumped, I've done this before with other forms but, not across multiple columns, only when working with a single row and single column value. I'm thinking the answer is in arrays but I'm having a brain freeze! Any help is appreciated.

Replies are listed 'Best First'.
Re: input form and MySQL across columns
by jandrew (Chaplain) on Sep 02, 2012 at 22:51 UTC

    I'm not entirely sure I follow your problem but I would say that the issue seems to fall in the set up of your MySQL table

    The columns qty1, qty2, qty3, and qty4 break the Repeating groups across columns restriction of the "first normal form" of relational data bases

    If you accept that this is the problem the fix would be a table that looked something like

    ID type name qty_position qty status 1 Group1 Shoes 1 15 ok 2 Group1 Shoes 2 50 ok

    This solution gets you back to a query with return values in one column. You just have to use conditions on more than one column to SELECT the right data.

    A word of warning - distributing the status column across all of the rows effectively breaks another of the normal form rules. To fix it you would move that value to a separate table. However, If the status is defined at the point of the transaction for each quantity increment then you are fixing something rather than breaking it.

      Thanks for the reply, I agree the table should probably have been structured in a single column right from the beginning. I was hoping to come up with a solution without having to go down that path. And good point on the status column as well. I'll wait until the other Monks reply before I redo the table, I have quite a bit of code already written for inserting and updating the table that works quite well.
Re: input form and MySQL across columns
by philiprbrenan (Monk) on Sep 02, 2012 at 22:59 UTC

    If you name the input fields like this using a character say - that you know will not occur in your item names:

    <input name="Shoes-1" size=2> <input name="Shoes-2" size=2> <input name="Shoes-3" size=2> <input name="Shoes-4" size=2> <input name="Shoes-5" size=2>

    and likewise Tires etc. then it will be easy for you to test whether the form has sent you back "Shoes-2" etc. and if it has, you can use a SQL Update statement to modify just the one column in one row:

    update ... set qty2 = ... where name = "Shoes";
      Naming the input fields with unique names is not an issue, the problem is using multiple input fields on a single row of the MySQL table. I also have working queries to manage the SQL table, again not an issue there. Thanks for your reply.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://991331]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2018-01-22 19:21 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (236 votes). Check out past polls.