Jump to content

PHP Matrix Table


kelvinfoo89

Recommended Posts

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 by kelvinfoo89
Link to comment
Share on other sites

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

 

post-3105-0-76000300-1439292030_thumb.png

  • Like 2
Link to comment
Share on other sites

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 by FooKelvin
Link to comment
Share on other sites

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                 |

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

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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          |

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

Link to comment
Share on other sites

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        |                                            
                                                               +-------------+          
  • Like 1
Link to comment
Share on other sites

    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)    

 

 

:confused:  :confused:  :confused: 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.