Converting multi-line CSV to multi-line CSV (with conditions)

Discussion in 'Programming/Scripts' started by psyzar, Mar 1, 2015.

  1. psyzar

    psyzar New Member

    I have a web form which stores each submission in a MySQL dbase table (containing the submissions of various other forms too) with 1 row per form field so each form generates multiple rows in the dbase table (not my design!); table description as follows:
    Field - Type
    id - bigint(20) unsigned
    lead_id - int(10) unsigned
    form_id - mediumint(8) unsigned
    field_number - float
    value - varchar(200)​

    I query this table and extract the "field_number" and "value" field values to generate a CSV file in the following format showing a single form submission's data (where the form_id = 8):
    1,Psyzar Qwerty
    18,Level 1
    20.1,Lesson 1
    20.3,Lesson 3
    6,10 Week Monday
    You will note that the field_numbers are not sequential. The ones containing a decimal point denote fields where multiple options are selected.
    I rewrite this output, by renumbering the field_numbers to:
    1,Psyzar Qwerty
    6,Level 1
    7.1,Lesson 1
    7.3,Lesson 3
    8,5 Week Monday

    I need to reformat the above rewritten lines to the following format (note the single set of lines above should generate multiple lines if more than 1 lesson is indicated by entries in field_number 7.x, and the multiple selections in field_number 9.x are grouped as a colon-sep field at the end of each line):
    Psyzar Qwerty,,07954750000,2015-02-15,2015-02-22,Level 1,10 Week Monday,Lesson 1,Tue:Thu
    Psyzar Qwerty,,07954750000,2015-02-15,2015-02-22,Level 1,10 Week Monday,Lesson 3,Tue:Thu​

    I have been able to get close by using a FOR-loops, generating multiple temporary files and multiple IF statements. I am sure there is a better way and guidance on getting their would be much appreciated. If a solution can be provided even better. The box running the script does have PHP installed.

    Thank you.
  2. sjau

    sjau Local Meanie Moderator

    Here's my approach... it's pseudo code:

    make an array from "select distinct form_id from table"
    foreach (unique_form_id_array as $val)
        make another array from "select field_number, value from table where form_id = current_array_value ORDER BY field_number ASC"  --> assign the field_number as array key and the "value" as array value
        foreach (current_form_array as $val -> $key)
            if ($key >=7 && $key <8)
               $tmpLessonArr[] = $val;
            } elseif ($key >= 9) {
               $tmpWeekdayArr[] = $val;
            } else {
               $tmpArr = $val;
        $tmpStr1 = implode(',', $tmpArr);
        $tmpStr2 = implode(':', $tmpWeedayArr);
        foreach($tmpLessonArr as $val)
            $output .= $tmpStr1 . ',' . $val . ',' . $tmpStr2 . "\n"; 
  3. psyzar

    psyzar New Member

    Thank you @sjau, sorry it has taken me so long to reply. I haven't tried you approach yet and will do so in the next rewrite of the piece. In the mean time I made progress by simplifying my spaghetti code.

  4. sjau

    sjau Local Meanie Moderator

    no worries :)

Share This Page