kelvinfoo89 Posted August 11, 2015 Share Posted August 11, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 11, 2015 Share Posted August 11, 2015 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 2 Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 13, 2015 Share Posted August 13, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 13, 2015 Share Posted August 13, 2015 Hi Baran, My apologies to you. I overlook one line of the code. is working now..Thank You so much!!! Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 13, 2015 Share Posted August 13, 2015 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 | +-------------------------+-----------------------------------+---------------------------+------------------------+ Quote Link to comment Share on other sites More sharing options...
Barand Posted August 13, 2015 Share Posted August 13, 2015 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> Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 13, 2015 Share Posted August 13, 2015 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 13, 2015 Share Posted August 13, 2015 With list() you need a numerically indexed array, hence my use of fetch_row() Quote Link to comment Share on other sites More sharing options...
Barand Posted August 13, 2015 Share Posted August 13, 2015 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 Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 13, 2015 Share Posted August 13, 2015 Hi, in sqlsrv fetch_row() is sqlsrv_get_field ? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 13, 2015 Share Posted August 13, 2015 you need while (list($c, $q, $n, $a) = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_NUMERIC)) { Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 14, 2015 Share Posted August 14, 2015 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 | +-------------------------+-----------------------------------+---------------------------+-------------------------+--------------------------| Quote Link to comment Share on other sites More sharing options...
Barand Posted August 14, 2015 Share Posted August 14, 2015 No, you are using Points as a key when it should be part of the data belonging to a question. Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 14, 2015 Share Posted August 14, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 14, 2015 Share Posted August 14, 2015 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 | +-------------+ 1 Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 15, 2015 Share Posted August 15, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2015 Share Posted August 15, 2015 array_sum $a = [1,2,3]; echo array_sum($a); //==> 6 Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 15, 2015 Share Posted August 15, 2015 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) Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2015 Share Posted August 15, 2015 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? Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 15, 2015 Share Posted August 15, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2015 Share Posted August 15, 2015 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; } Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 15, 2015 Share Posted August 15, 2015 Hi Barand, i think my explanation not clear enough. Instead of left to right to get total, it's top to bottom. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2015 Share Posted August 15, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted August 16, 2015 Share Posted August 16, 2015 Thank You Barand! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.