combining tables in PHP

[j_p36]

Ok the problem I am having is difficult to explain. I'm working on a project for work and I am allowing people to upload a variable number of tables (up to 5 maybe 10 later). Then I am taking the uploaded tables and normalizing them (I'm fairly sure all the code written here does what I want).

Ok to store the normalized tables, since there is a variable number, I use a 3 dimensional array such as $data[$a][$r][$c] where $a=array number (because all the normalized uploaded tables are stored here), $r=row number, and $c=column number. The normalized tables contain row names and column names also.

Now to the hard part: Next i need to create one table that is all the separate tables merged into one. If more than one table has overlapping data (a value with matching column AND row name) the value of the overlapping data from each table needs to be averaged. If one of the values is zero it is not included in the average. Also, if there is a column without matching data for a row or a row without matching data for a column then the blank is filled with a 0.

example

table 1
....1...2 (ignore the periods, just used to line the column names up)
a 93 87
b 73 27

table 2
...2....3
a 10 90
c 35 21

table 3
....2
a 53

final table
....1...2...3
a 93 50 90
b 73 27 0
c 0...35 21

Please help before I lose my mind trying to figure out how to do this

P.S. the column names and row names are not this simple in my actual tables, they are more like HK/12/93/76 and such

P.S.S. I'm open to any suggestions on how to implement this.

[j_p36]

bump

[LethargicCoder]

Give this a try. There are plenty of output statements to see what's going on. Please excuse the jumping between HTML, print_r and echo :D

<?php
/*
table 1
....1...2 (ignore the periods, just used to line the column names up)
a 93 87
b 73 27

table 2
...2....3
a 10 90
c 35 21

table 3
....2
a 53

final table
....1...2...3
a 93 50 90
b 73 27 0
c 0...35 21

RESULT of $combined
Array ( [a] => Array ( [1] => 93 [2] => 50 [3] => 90 )
        [b] => Array ( [1] => 73 [2] => 27 [3] => 0 )
        [c] => Array ( [1] => 0 [2] => 35 [3] => 21 ) )

*/

$data = array();
$data['table 1']['a'][1]=93;
$data['table 1']['a'][2]=87;
$data['table 1']['b'][1]=73;
$data['table 1']['b'][2]=27;

$data['table 2']['a'][2]=10;
$data['table 2']['a'][3]=90;
$data['table 2']['c'][2]=35;
$data['table 2']['c'][3]=21;

$data['table 3']['a'][2]=53;

$arrays = array();
$rows = array();
$cols = array();


print_r("Table Keys<br />");
print_r(array_keys($data));
    echo "<br />\n";
    echo "<br />\n";

foreach(array_keys($data) as $key => $a)
{
    $arrays[]=$a;
    ?>
    <br />
    Keys(Rows) for Table: <?php echo $a; ?><br />
    <?php
    print_r(array_keys($data[$a]));
    echo "<br />\n";

    foreach(array_keys($data[$a]) as $rkey => $r)
    {
        if(!in_array($r,$rows))
        {
            $rows[]=$r;
        }
        ?>
        <br />
        Keys(Cols) for Table: <?php echo $a; ?>, Row: <?php echo $r; ?><br />
        <?php
        print_r(array_keys($data[$a][$r]));
        echo "<br />\n";

        foreach(array_keys($data[$a][$r]) as $ckey => $c)
        {
            if(!in_array($c,$cols))
            {
                $cols[]=$c;
            }
        }
    }
}

echo "<br />\n";
print_r($arrays);
echo "<br />\n";
print_r($rows);
echo "<br />\n";
print_r($cols);
echo "<br />\n";
echo "<br />\n";

$combined = array();

foreach($rows as $r)
{
    echo "Row: $r<br />\n";
    foreach($cols as $c)
    {
        $tot = 0;
        $cnt = 0;

        echo "Col: $c<br />\n";
        foreach($arrays as $a)
        {
            echo "Table: $a<br />\n";
            if(isset($data[$a][$r][$c]))
            {
                $val = $data[$a][$r][$c];
                if ($val > 0)
                {
                    $tot += $val;
                    $cnt++;
                }
            }
        }
        $avg = 0;
        if($cnt>0)
        {
            $avg=$tot/$cnt;
        }
        $combined[$r][$c]=$avg;
    }
}

echo "<br />\n";
print_r($combined);
?>

[j_p36]

Thanks I will try this out sometime next week and get back to you