Jump to content


Photo

Create a matrix


  • Please log in to reply
No replies to this topic

#1 ShadowWeaver

ShadowWeaver
  • Members
  • Pip
  • Newbie
  • 1 posts

Posted 24 May 2006 - 03:13 PM

Ok, so I have a mapping table with 3 fields:

project_id, variable_id, symbol_id

The relationships are:

project 1..* variable
variable 1..1 symbol

I want to create a matrix in html like so:

|            | Project 1 | Project 2 | Project 3 | ...
| Variable 1 |  symbol 1 |  symbol 2 |  symbol 3 | ...
| Variable 2 |  symbol 4 |  symbol 5 |  symbol 6 | ...
.
.
.

Currently this data is in an AoA called $results. Each element of $results is a row, containing the elements project_id, variable_id, symbol_id.

I have 3 other tables where the ids in the mapping table correspond to the id field in each of the 3 tables:

Projects -> id, project
Variables -> id, variable
Symbols -> id, symbols

My question is, how do I create a matrix like the one above efficiently?

At the moment I have nested foreach loops which take ages to process and still dont generate the table correctly where a symbol doesnt exist for a variable.

Here's my code...
        $sql = new SQL;
        $sql->connect();
        $results = $sql->query_result($map);

        $check_proj = array();
        $check_var = array();
        $check_sym = array();
        $table_head = "";
        $table_var = "";
        $table_sym = "";
        $count = 0;
        $cresult = count($results);
        $rows = 0;

        foreach ($results as $result)
        {
            $count = count($check_var);

            # foreach mapped row (project_id, variable_id, symbol_id), 
            # if project_id does not exist in $check_proj array, add it,
            # and append the table header string to $table_head.
            if (!in_array($result["project_id"], $check_proj))
            {
                array_push ($check_proj, $result["project_id"]);
                $searched_projects = $sql->query_result("SELECT * FROM projects WHERE id='".$result["project_id"]."'");

                foreach ($searched_projects as $sp)
                {
                    $table_head .= "<th>".$sp["airline"]." ".$sp["ac_type"]." ".$sp["sim_type"]."</th>";
                }
            }

            # if the number of elements in $check var is not zero 
            # i.e. not the first iteration of the loop, add the variable id 
            # to $check_var array, end the row in $table_var string by appending 
            # the $table_sym string and </tr> and start a new row beginning with 
            # the new variable.
            # Otherwise this is the first iteration, so start the first row in 
            # $table_var with the first variable.
            if ($count != 0)
            {
                if (!in_array($result["variable_id"], $check_var))
                {
                    array_push ($check_var, $result["variable_id"]);
                    $searched_vars = $sql->query_result("SELECT * FROM variables WHERE id='".$result["variable_id"]."'");

                    # push table_sym onto array (each element is row)
                    # and clear table_sym
                    $table_var .= $table_sym."</tr>";
                    $table_sym = "";

                    # if new var exists, new row must be created...
                    foreach ($searched_vars as $sv)
                    {
                        $table_var .= "<tr><td>".$sv["variable"]."</td>";
                    }
                }
            }
            else
            {
                if (!in_array($result["variable_id"], $check_var))
                {
                    array_push ($check_var, $result["variable_id"]);
                    $searched_vars = $sql->query_result("SELECT * FROM variables WHERE id='".$result["variable_id"]."'");

                    # if new var exists, new row must be created...
                    foreach ($searched_vars as $sv)
                    {
                        $table_var .= "<tr><td>".$sv["variable"]."</td>";                
                    }
                }                
            }

            # get the symbol for this row and append it to $table_sym string
            # in between <td> tags.
            $searched_syms = $sql->query_result("SELECT * FROM symbols WHERE id='".$result["symbol_id"]."'");
            foreach ($searched_syms as $ss)
            {
                $table_sym .= "<td>".$ss["symbol"]."</td>";
            }

            # if its the last row, close it off
            if ($rows == $cresult-1)
            {
                    $table_var .= $table_sym."</tr>";
            }

            $rows++;

        }
        $sql->close();

        echo "<table border='1'>";
        echo "<tr><th>IOS Symbols</th>";
        echo $table_head;
        echo "</tr>";
        echo $table_var;
        echo "</table><br/>";


Cheers,
Steve




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users