What is the likelihood that any given field in any given row of your MySQL table contains a comma as data? And what is the likelihood that any given field contains a tab character? Why not use "Tab-Separated-Values" (tsv -- a recognized file type acceptable as input to Excel, etc), so you can skip all the comma / double-quote noise (with the possibility of escaped double-quotes in data)?

Why does your source table not have separate fields for voice and fax phone numbers? (That is, why should you need to store two copies of all the other information for a given individual, just so you can manage both a voice and a fax phone number?)

And while I'm asking about stuff other than your stated problem, don't you think it's a bit problematic to have two columns with the same label? (You have two columns labeled "username", but these seem to have different values; likewise for two columns labeled "name".)

As for answering the particular question you posed, it's only complicated by the fact that the input data is somewhat brain-damaged: apparently, you need to merge two table rows of mostly redundant data so that you can get a single record of output with two different phone numbers (one for voice, one for fax).

So, you keep all the fields when the row contains a "0" in the fax_flag field -- load them into a hash keyed by column name (but do something so that you have "username1" and "username2", and "name1" and "name2" as hash keys, or else you will lose information), and put together the value for to be stored with "voice_phone" as a hash key. Then when you get the corresponding row with "1" in the fax_flag field, just add a "fax_number" key/value to the hash, and output the desired result from the hash.

(If the rows are not ordered "cooperatively" -- i.e. if the "fax_flag=1" row could be before or long after the corresponding "fax_flag=0" row -- you'll need a HoH, with the first-layer hash keyed by an appropriate unique-ID-per-individual. Output will need to be done when you've processed the 2nd row for a given individual, whichever row that happens to be.)

In reply to Re: Manipulating Data by a Field Identifier by graff
in thread Manipulating Data by a Field Identifier by rycher

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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, details, 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, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            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.