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

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
2,[email protected]
3,07954750000
4,2015-02-15
5,2015-02-22
18,Level 1
20.1,Lesson 1
20.3,Lesson 3
6,10 Week Monday
21.2,Tuesday
21.4,Thursday​

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
2,[email protected]
3,07954750000
4,2015-02-15
5,2015-02-22
6,Level 1
7.1,Lesson 1
7.3,Lesson 3
8,5 Week Monday
9.2,Tuesday
9.4,Thursday​

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,[email protected],07954750000,2015-02-15,2015-02-22,Level 1,10 Week Monday,Lesson 1,Tue:Thu
Psyzar Qwerty,[email protected],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.

 

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

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
    unset($tmpLessonArr);
    unset($tmpWeekdayArr);
    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"; 
    }
}

 

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.

 

no worries