PHP Matrix Table

[kelvinfoo89]

Hi Guys, i have a question regarding to create a php dynamic matrix table from survey form

The table is for comparison purpose.

I have a database that keep all the survey question, the feedback from employee.

 

So let me show you the data in my database.

 

Name  questionID   Question              Answer

--------------------------------------------------------------------

Foo      1000          How are you?            I'm Fine

Foo      1000          What's Your name?   My Name is Foo

Ben      1000          How are you?            I'm Feel sick

Ben      1000          How are you?            My name is Ben

 

 

Code that i Currently use:

<table>
    <th>Question</th>
    <?php
    $curr_rows = 1;
    if (sqlsrv_num_rows($stmt) > 0) {
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            echo "<tr>";
            echo '<td name="question[' . $row["questionID"] . ']">' . $row['question'] . '</td>';
            $curr_rows++;
            echo "</tr>";
        }
    } else {
        echo "0 results";
    }
    ?>
    <?php
    while ($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {
        echo '<th name="question[' . $row["questionID"] . ']">' . $row['Employee_Name'] . '</th>';
        echo "<tr>";
        echo '<td name="question[' . $row["questionID"] . ']">' . $row['answer'] . '</td>';
        $curr_rows++;
        echo "</tr>";
        $curr_rows++;
    }
    ?>
</table>

The Output that i expect is:

 

Question                        Foo                          Ben

--------------------------------------------------------------------------

How are you?                 I'm Fine                   I'm Feel sick

What's Your name?        My Name is Foo     My name is Ben

 

 

Please Help, I really need solution. 

 

Thank You

Edited August 11, 2015 by kelvinfoo89

[Barand]

I used MySqli because that's what I have, but it should be easily converted for sqlsrv. Here's the method

/*****************************************************
* first you need to get the column headings
* and create array to store responses for
* each question
******************************************************/
$sql = "SELECT DISTINCT name 
        FROM kelvin
        ORDER BY name";
$names = array();
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    $names[] = $row['name'];
}
$tableHeads = "<tr><th>Question</th><th>" . join('</th><th>', $names) . "</th></tr>\n";
$newArray = array_fill_keys($names,'');  // create blank array
/*****************************************************
* then you process the table data
* storing the answers for each name in the array.
* 
* output the array for each question when the question
* value changes, then start with a new array for the new 
* question
******************************************************/
$sql = "SELECT question, name, answer
        FROM kelvin
        ORDER BY question";
$qarray = $newArray;  // new array to store answers to question
$currq = '';          // store the current question
$tableData = '';
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    if ($row['question'] != $currq) {   // change of question?
        if ($currq) {                   // have we a question yet?
            $tableData .= "<tr><td>$currq</td><td>";
            $tableData .= join('</td><td>', $qarray) . "</td></tr>\n";
        }
        $currq = $row['question'];  // store new question
        $qarray = $newArray;        // reset the array
    }
    $qarray[$row['name']] = $row['answer'];  // store the answer by name
}
// output the stored array for the final question
$tableData .= "<tr><td>$currq</td><td>";
$tableData .= join('</td><td>', $qarray) . "</td></tr>\n";

?>
<table border='1'>
    <?=$tableHeads?>
    <?=$tableData?>
</table>

My data

mysql> SELECT * FROM test.kelvin;
+----+---------+------------+--------------------+--------------------+
| id | name    | questionid | question           | answer             |
+----+---------+------------+--------------------+--------------------+
|  1 | Foo     |       1000 | How are you?       | I'm Fine           |
|  2 | Foo     |       1001 | What is Your name? | My Name is Foo     |
|  3 | Ben     |       1000 | How are you?       | I Feel sick        |
|  4 | Ben     |       1001 | What is Your name? | My name is Ben     |
|  5 | Charlie |       1001 | What is Your name? | My name is Charlie |
+----+---------+------------+--------------------+--------------------+

Results attached

 

[FooKelvin]

Hi Baran, 

 

Firstly, i would say thank you so much!

i have try the code that you provide me. it's look good!

But i have still facing one problem. have a look here.

$sql2 = "SELECT DISTINCT Employee_Name FROM SubmittedFormView ORDER BY Employee_Name";
$names = array();
$stmt2 = sqlsrv_query($conn, $sql2, $names);
while ($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {
     $names[] = $row['Employee_Name']; 
}
$tableHeads = "<tr><th>Question</th><th>" . join('</th><th>', $names) . "</th></tr>\n";
$newArray = array_fill_keys($names,'');  // create blank array 


$sql = "SELECT DISTINCT question, Employee_Name, answer FROM SubmittedFormView ORDER BY question";
$qarray = $newArray;  // new array to store answers to question
$currq = '';          // store the current question
$tableData = '';
$stmt = sqlsrv_query($conn, $sql);
 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
     if ($row['question'] != $currq) {   // change of question?
        if ($currq) {                   // have we a question yet?
            $tableData .= "<tr><td>$currq</td><td>";
            $tableData .= join('</td><td>', $qarray) . "</td></tr>\n";
        }
        $currq = $row['question'];  // store new question
        $qarray = $newArray;        // reset the array
     }
 }
 // output the stored array for the final question
$tableData .= "<tr><td>$currq</td><td>";
$tableData .= join('</td><td>', $qarray) . "</td></tr>\n";
?>
<table border='1'>
    <?php echo $tableHeads?>
    <?php echo $tableData?>
</table>

Here is the output:

 

http://www.photobox.co.uk/my/photo/full?photo_id=20828350194

Edited August 13, 2015 by FooKelvin

[FooKelvin]

Hi Baran, 

 

My apologies to you. I overlook one line of the code.

is working now..Thank You so much!!! 

[FooKelvin]

Is *Barand*. Sorry for the over exited.

By the way, it is possible to have category with sub question?

 

for example,

+-------------------------+----------------------------------+---------------------------+--------------------------+

 |   Category              |        Question                     |                Foo             |                Ben           |

+-------------------------+----------------------------------+---------------------------+--------------------------+

 |  Personal question  | How are you?                    |        i'm fine                |       i'm fine              |

 |                                | What is Your name?          |       my name is foo    |      my name is Ben |

+-------------------------+----------------------------------+---------------------------+--------------------------+

 |  General question   | Do you like php?                |  Yes                           |  Yes                        |

 |                                |  PHP Freaks is awesome? |  Absolutely                 | For Sure                 |

+-------------------------+-----------------------------------+---------------------------+------------------------+

[Barand]

As the structure gets a little more complicated I opted for an alternative method, storing the query results in a multi-dimensional array indexed by category, question, name.

<?php
/*****************************************************
* first you need to get the column headings
* and create array to store responses for
* each question
******************************************************/
$sql = "SELECT DISTINCT name 
        FROM kelvin
        ORDER BY name";
$names = array();
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    $names[] = $row['name'];
}
$tableHeads = "<tr><th>Category</th><th>Question</th><th>" . join('</th><th>', $names) . "</th></tr>\n";
$newArray = array_fill_keys($names,'');  // create blank array
/*****************************************************
* then you process the table data
* storing the answers for each name in the array.
* indexed by categor, question, name
******************************************************/
$sql = "SELECT category, question, name, answer
        FROM kelvin
        ORDER BY category, question";
$qarray = $newArray;  // new array to store answers to question
$currq = '';          // store the current question
$currc = '';          // store the current category
$data = array();
$res = $db->query($sql);

while (list($c,$q,$n,$a) = $res->fetch_row()) {
    if (!isset($data[$c][$q])) {
        $data[$c][$q] = $newArray;
    }
    $data[$c][$q][$n] = $a;  // store the answer by name
}

$tableData = '';
/**************************************************
*  loop through the data array and output the table
***************************************************/
foreach ($data as $cat => $qdata) {
    $kq = count($qdata); // how many question rows?
    $tableData .= "<tr><td rowspan='$kq'>$cat</td> ";
    $k=0;
    foreach ($qdata as $q => $ans) {
        if ($k > 0) $tableData .= '<tr>';
        $tableData .= "<td>$q</td><td>".join('</td><td>',$ans)."</td></tr>";
        ++$k;
    }
}
?>
<table border='1' style='border-collapse: collapse;'>
    <?=$tableHeads?>
    <?=$tableData?>
</table>

[FooKelvin]

Hi Barand,

 

Thank You.

 

I'm not sure this is correct or not.

$stmt2 = sqlsrv_query($conn, $sql2);
while (list($c, $q, $n, $a) = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {
    if (!isset($data[$c][$q])) {
        $data[$c][$q] = $newArray;
    }
    $data[$c][$q][$n] = $a;  // store the answer by name

[Barand]

With list() you need a numerically indexed array, hence my use of fetch_row()

[Barand]

BTW, these lines are redundant in the revised method and can be removed

$qarray = $newArray;  // new array to store answers to question
$currq = '';          // store the current question
$currc = '';          // store the current category

 

[FooKelvin]

Hi, in sqlsrv fetch_row() is sqlsrv_get_field ?

[Barand]

you need

while (list($c, $q, $n, $a) = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_NUMERIC)) {

[FooKelvin]

Hi Barand,

Thank you.

 

I play around with added another column which is "Points". 

Each question have their own points.

 

Am i doing the correct thing?

<?php
/*****************************************************
* first you need to get the column headings
* and create array to store responses for
* each question
******************************************************/
$sql = "SELECT DISTINCT name 
        FROM kelvin
        ORDER BY name";
$names = array();
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    $names[] = $row['name'];
}
$tableHeads = "<tr><th>Category</th><th>Question</th><th>" . join('</th><th>', $names) . "</th></tr>\n";
$newArray = array_fill_keys($names,'');  // create blank array
/*****************************************************
* then you process the table data
* storing the answers for each name in the array.
* indexed by categor, question, name
******************************************************/
$sql = "SELECT category, question, Points, name, answer
        FROM kelvin
        ORDER BY category, question";
$res = $db->query($sql);

while (list($c,$q,$p,$n,$a) = $res->fetch_row()) {
    if (!isset($data[$c][$q][$p])) {
        $data[$c][$q][$p] = $newArray;
    }
    $data[$c][$q][$p][$n] = $a;  // store the answer by name
}

Expected Output:

 

+-------------------------+----------------------------------+---------------------------+--------------------------+--------------------------+

 |   Category              |        Question                     |          Points               |                Foo             |                Ben           |

+-------------------------+----------------------------------+---------------------------+--------------------------+--------------------------+

 |  Personal question  | How are you?                    |                5                 |          i'm fine            |       i'm fine               |

 |                                | What is Your name?          |                4                 |      my name is foo   |      my name is Ben  |

+-------------------------+----------------------------------+---------------------------+--------------------------+--------------------------|

 |  General question   | Do you like php?                |                5                 |             Yes              |           Yes                |

 |                                |  PHP Freaks is awesome? |                5                 |         Absolutely        |         For Sure          |

+-------------------------+-----------------------------------+---------------------------+-------------------------+--------------------------|

[Barand]

No, you are using Points as a key when it should be part of the data belonging to a question.

[FooKelvin]

I don't get your meaning of data belonging to a question. 

After i try for few hours. it's quite confusing, i keep get Array as my result. 

Sorry for keep annoying you. 

[Barand]

Should be

<?php
/*****************************************************
* first you need to get the column headings
* and create array to store responses for
* each question
******************************************************/
$sql = "SELECT DISTINCT name 
        FROM kelvin
        ORDER BY name";
$names = array();
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    $names[] = $row['name'];
}
$tableHeads = "<tr><th>Category</th><th>Question</th><th>Points</th><th>" . join('</th><th>', $names) . "</th></tr>\n";
$newArray = array_fill_keys($names,'');  // create blank array

$newArray=array_merge(array('points'=>''), $newArray); // add points to the start of array for each question
/*****************************************************
* then you process the table data
* storing the answers for each name in the array.
* indexed by categor, question, name
******************************************************/
$sql = "SELECT category, question, points, name, answer
        FROM kelvin
        ORDER BY category, question";
$qarray = $newArray;  // new array to store answers to question
$currq = '';          // store the current question
$currc = '';          // store the current category
$data = array();
$res = $db->query($sql);

while (list($c,$q,$p,$n,$a) = $res->fetch_row()) {
    if (!isset($data[$c][$q])) {
        $data[$c][$q] = $newArray;
    }
    $data[$c][$q][$n] = $a;  // store the answer by name
    $data[$c][$q]['points'] = $p;  // store the points for the question
    
}

$tableData = '';
/**************************************************
*  loop through the data array and output the table
***************************************************/
foreach ($data as $cat => $qdata) {
    $kq = count($qdata); // how many question rows?
    $tableData .= "<tr><td rowspan='$kq'>$cat</td> ";
    $k=0;
    foreach ($qdata as $q => $ans) {
        if ($k > 0) $tableData .= '<tr>';
        $tableData .= "<td>$q</td><td>".join('</td><td>',$ans)."</td></tr>";
        ++$k;
    }
}
?>
<table border='1' style='border-collapse: collapse;'>
    <?=$tableHeads?>
    <?=$tableData?>
</table>

You should not be repeating data like question text, category name and points in every answer record. You should normalize your data so this information is held only once in the correct table. Key values (ids) are the only things that should be repeated in different tables to link the data together.

       +------------------+                                                            +--------------+                   
       | category         |                                                            | user         |                   
       +------------------+                                                            +--------------+                   
       | category_id (PK) |----+                                                   +---| user_id (PK) |                   
       | cat_name         |    |                                                   |   | name         |                   
       +------------------+    |                                                   |   +--------------+                   
                               |                                                   |                                      
                               |     +--------------+                              |                                      
                               |     | question     |                              |                                      
                               |     +--------------+                              |                                      
                               |     | q_id (PK)    |----+                         |                                      
                               |     | questiontext |    |                         |                                      
                               |     | points       |    |                         |                                      
                               +----<| category_id  |    |                         |                                      
                                     +--------------+    |                         |                                      
                                                         |     +-------------+     |                                      
                                                         |     | answer      |     |                                      
                                                         |     +-------------+     |                                      
                                                         |     | ans_id (PK) |     |                                      
                                                         |     | answertext  |     |                                      
                                                         |     | user_id     |>----+                                      
                                                         +----<| q_id        |                                            
                                                               +-------------+          

[FooKelvin]

Thansk Barand. This is super good explanation. 

if let say i the answer is a number, i want to total up each individual answer to get the total no. ,

the good behavior is to use jquery to sum or use php sum array? i not sure whether php allow me to sum array or not.

[Barand]

array_sum

$a = [1,2,3];
echo array_sum($a);  //==> 6

[FooKelvin]

    foreach ($qdata as $q => $ans) {
        if ($k > 0) $tableData .= '<tr>';
        $tableData .= "<td>$q</td><td>".join('</td><td>',$ans)."</td></tr>";
        $dump = var_dump($ans["kelvin foo"]);
        echo array_sum($dump);  
        ++$k;
    }

My output is:

 

int(2) int(2) int(2) int(2) int(2) int(2) int(2) int(2) int(2) int(2)    

 

 

     

[Barand]

OK So now I have to change the database I set up to allow for numeric answers. Why don't you just ask the real problem from the start instead of going round the houses with the dummy questions and answers?

[FooKelvin]

Opps, sorry for that. The problem are real. I have 2 sets of problem, maybe i should create a new post new ask different question? i'm really sorry =(

Edited August 15, 2015 by FooKelvin

[Barand]

    foreach ($qdata as $q => $ans) {

        if ($k > 0) $tableData .= '<tr>';

        $tot = array_sum(array_slice($ans,1)); // sum array excluding points value

        $tableData .= "<td>$q</td><td>".join('</td><td>',$ans)."</td><td>$tot</td></tr>";

        ++$k;

    }

[FooKelvin]

Hi Barand, i think my explanation not clear enough. Instead of left to right to get total, it's top to bottom.

[Barand]

In that case, have separate totals array and accumulate the totals for each user in that,

 

Added lines 25, 47, 64

/*****************************************************
* first you need to get the column headings
* and create array to store responses for
* each question
******************************************************/
$sql = "SELECT DISTINCT name 
        FROM kelvin
        ORDER BY name";
$names = array();
$res = $db->query($sql);
while ($row = $res->fetch_assoc()) {
    $names[] = $row['name'];
}
$tableHeads = "<tr><th>Category</th><th>Question</th><th>Points</th><th>" . join('</th><th>', $names) . "</th></tr>\n";
$newArray = array_fill_keys($names,'');  // create blank array
$totals = array_fill_keys($names, 0);    // create array to store totals
$newArray=array_merge(array('points'=>''), $newArray); // add points to the start of array for each question
/*****************************************************
* then you process the table data
* storing the answers for each name in the array.
* indexed by categor, question, name
******************************************************/
$sql = "SELECT category, question, points, name, answer
        FROM kelvin
        ORDER BY category, question";
$qarray = $newArray;  // new array to store answers to question
$currq = '';          // store the current question
$currc = '';          // store the current category
$data = array();
$res = $db->query($sql);

while (list($c,$q,$p,$n,$a) = $res->fetch_row()) {
    if (!isset($data[$c][$q])) {
        $data[$c][$q] = $newArray;
    }
    $data[$c][$q][$n] = $a;  // store the answer by name
    $data[$c][$q]['points'] = $p;  // store the points for the question
    $totals[$n] += $a;             // accumulate total
}

$tableData = '';
/**************************************************
*  loop through the data array and output the table
***************************************************/
foreach ($data as $cat => $qdata) {
    $kq = count($qdata); // how many question rows?
    $tableData .= "<tr><td rowspan='$kq'>$cat</td> ";
    $k=0;
    foreach ($qdata as $q => $ans) {
        if ($k > 0) $tableData .= '<tr>';
        $tableData .= "<td>$q</td><td>".join('</td><td>',$ans)."</td></tr>";
        ++$k;
    }
}
$tableData .= "<tr><th colspan='3'>Totals</th><td>" . join('</td><td>',$totals) . "</td></tr>\n";
?>
<table border='1' style='border-collapse: collapse;'>
    <?=$tableHeads?>
    <?=$tableData?>
</table>

Edited August 15, 2015 by Barand

[FooKelvin]

Thank You Barand!