Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
The stupid question is the question not asked
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
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.


In reply to input form and MySQL across columns by Da_Skipper

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (5)
    As of 2014-04-19 23:43 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      April first is:







      Results (485 votes), past polls