input form and MySQL across columns

[Da_Skipper]

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()){

and

$ref->{'name'}

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

$newname++;

like this

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

$newname++;

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

[jandrew]

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.

[Anonymous Monk]

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.

[philiprbrenan]

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";

[Anonymous Monk]

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.