How to Loop the following question and answer?

[FooKelvin]

Hi All,

 

I have tried few weeks. but I still unable to get my expected answer. Please Help.

 

 

As attachment are my data and expected table to be shown in HTML format.

loopanswer.zip

[mac_gyver]

it's not entirely clear, since there's no context or explanation in the attached spread-sheet, what the data is and which of it is even relevant.

 

i'll venture some guesses -

 

1) The data is the result set/rows from an sql query? if so, if you store that data into an array in your code and use var_export() on that array, you can paste valid php code that we could use as test data.

 

2) Expr2 is the user/employee id and the Expr3 is the text answer you want to display?

 

if so, here's a way of producing the output -

 

1) query for and retrieve the question id and question text, in the order that you want to display them (in case they are not to be displayed by the id ordering), separately from the main data retrieval query. store the question id/text into an array, using the question_id as the array index and the question text as the array value. you would leave out the question text in the main data retrieval query.

 

2) when you retrieve the main data, loop over it and pre-process/pivot it and store it into a multi-dimensional array. the first array dimension/index would be the employee_id. the second array dimension/index would be question_id - $data[employee_id][question_id][] = 'text answer to display'; // the last [] is to accommodate multiple replies to any question.

 

3) to produce the output, produce the table header by outputting the first column, then loop over the array from item #1 above. to produce the data section, loop over the pre-processed data. this will give you the employee_id and the array of replies for that employee id, with the reply array index being the question_id. loop over the array question id's, from item #1, to get the question_id's in the order that you are displaying the questions/replies. use the question_id to reference the correct reply for the current employee id. this will give you an array of replies for that question_id. loop over or implode() this array to output the text in each table cell.

 

see the following simplified example - 

// some made up questions
$q = array();
$q[1] = 'q1';
$q['2A'] = 'q2 a';
$q['2B'] = 'q2 b';
$q[3] = 'q3';
$q[4] = 'q4 mult';

// some made up data
$data = array();
$data[1][1][] = 'e1 q1';
$data[1]['2A'][] = 'e1 q2 a';
$data[1]['2B'][] = 'e1 q2 b';
$data[1][3][] = 'e1 q3';
$data[1][4][] = 'e1 q4 1';
$data[1][4][] = 'e1 q4 2';

$data[2][1][] = 'e2 q1';
$data[2]['2A'][] = 'e2 q2 a';
$data[2]['2B'][] = 'e2 q2 b';
$data[2][3][] = 'e2 q3';
$data[2][4][] = 'e2 q4 1';
$data[2][4][] = 'e2 q4 2';


// produce table header
echo "<table>\n";
echo "<tr><th>Employee ID</th>";

// loop over array of questions for the header
foreach($q as $question)
{
    echo "<th>$question</th>";
}
echo "</tr>\n";

// produce table data
foreach($data as $employee_id=>$replies)
{
    // $replies is an array with the question_id as the array index
    echo "<tr><td>$employee_id</td>";
    
    // loop over the question id's in the order they are being displayed
    foreach(array_keys($q) as $q_id)
    {
        // is there a reply for this quesiton_id
        if(isset($replies[$q_id]))
        {
            // there is a reply for this question
            // there can be multiple replies to a question - separate them with a <hr> in the table cell
            echo "<td>";
            echo implode('<hr>',$replies[$q_id]); // note: this works correctly if there is a single reply, the result is just the single array element by itself
            echo "</td>";
        }
        else
        {
            // no reply, output whatever you want for this case
            echo "<td>n/a</td>";
        }
    }
    echo "</tr>\n";
}
echo "</table>\n";

[FooKelvin]

 

it's not entirely clear, since there's no context or explanation in the attached spread-sheet, what the data is and which of it is even relevant.

 

i'll venture some guesses -

 

1) The data is the result set/rows from an sql query? if so, if you store that data into an array in your code and use var_export() on that array, you can paste valid php code that we could use as test data.

 

2) Expr2 is the user/employee id and the Expr3 is the text answer you want to display?

 

if so, here's a way of producing the output -

 

1) query for and retrieve the question id and question text, in the order that you want to display them (in case they are not to be displayed by the id ordering), separately from the main data retrieval query. store the question id/text into an array, using the question_id as the array index and the question text as the array value. you would leave out the question text in the main data retrieval query.

 

2) when you retrieve the main data, loop over it and pre-process/pivot it and store it into a multi-dimensional array. the first array dimension/index would be the employee_id. the second array dimension/index would be question_id - $data[employee_id][question_id][] = 'text answer to display'; // the last [] is to accommodate multiple replies to any question.

 

3) to produce the output, produce the table header by outputting the first column, then loop over the array from item #1 above. to produce the data section, loop over the pre-processed data. this will give you the employee_id and the array of replies for that employee id, with the reply array index being the question_id. loop over the array question id's, from item #1, to get the question_id's in the order that you are displaying the questions/replies. use the question_id to reference the correct reply for the current employee id. this will give you an array of replies for that question_id. loop over or implode() this array to output the text in each table cell.

 

see the following simplified example - 

// some made up questions
$q = array();
$q[1] = 'q1';
$q['2A'] = 'q2 a';
$q['2B'] = 'q2 b';
$q[3] = 'q3';
$q[4] = 'q4 mult';

// some made up data
$data = array();
$data[1][1][] = 'e1 q1';
$data[1]['2A'][] = 'e1 q2 a';
$data[1]['2B'][] = 'e1 q2 b';
$data[1][3][] = 'e1 q3';
$data[1][4][] = 'e1 q4 1';
$data[1][4][] = 'e1 q4 2';

$data[2][1][] = 'e2 q1';
$data[2]['2A'][] = 'e2 q2 a';
$data[2]['2B'][] = 'e2 q2 b';
$data[2][3][] = 'e2 q3';
$data[2][4][] = 'e2 q4 1';
$data[2][4][] = 'e2 q4 2';


// produce table header
echo "<table>\n";
echo "<tr><th>Employee ID</th>";

// loop over array of questions for the header
foreach($q as $question)
{
    echo "<th>$question</th>";
}
echo "</tr>\n";

// produce table data
foreach($data as $employee_id=>$replies)
{
    // $replies is an array with the question_id as the array index
    echo "<tr><td>$employee_id</td>";
    
    // loop over the question id's in the order they are being displayed
    foreach(array_keys($q) as $q_id)
    {
        // is there a reply for this quesiton_id
        if(isset($replies[$q_id]))
        {
            // there is a reply for this question
            // there can be multiple replies to a question - separate them with a <hr> in the table cell
            echo "<td>";
            echo implode('<hr>',$replies[$q_id]); // note: this works correctly if there is a single reply, the result is just the single array element by itself
            echo "</td>";
        }
        else
        {
            // no reply, output whatever you want for this case
            echo "<td>n/a</td>";
        }
    }
    echo "</tr>\n";
}
echo "</table>\n";

Hi Mac,

Thanks.

 

I tested the array part. But I am stuck in the array id.

 

I print_r your array and this is what i get:

Array ( [1] => q1 [2A] => q2 a [2B] => q2 b [3] => q3 [4] => q4 mult )

This is my print_r:

Array ( [0] => Do you like our signature Noodles? [1] => If No,Why? [2] => Do you love our food? [3] => What caused you enter to our caf�? [4] => Why have you decided to not dine in our caf�? [5] => What did you like most about our food? [6] => What did you dislike most about our food? [7] => Which caf� you prefer to go other than us? [8] => Do you have any further comments? [9] => Would you consider returning to our caf� in next 2 weeks? )

here is my source code:

 $sql2 = "SELECT DISTINCT [question_id],q_text 
        FROM [RSA].[dbo].[feedbug]
        ORDER BY [question_id]";
    $ques = array();
    $stmt2 = sqlsrv_query($conn, $sql2, $ques);
    while ($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {
        $ques[] = $row['q_text'];
    }
    print_r($ques);

[mac_gyver]

change this -

$ques[] = $row['q_text'];

to this -

$ques[$row['question_id']] = $row['q_text'];

[FooKelvin]

 

change this -

$ques[] = $row['q_text'];

to this -

$ques[$row['question_id']] = $row['q_text'];

 

Thanks. Is working now.

 

The second part of array, am i doing thing correctly?

it seem that nothing to print out.

 

Here is my code.

$sql = "SELECT [question_id], [Expr3],[Expr2],
        FROM [RSA].[dbo].[feedbug]
        ORDER BY [Expr2]";
$stmt1 = sqlsrv_query($conn, $sql);
$data = array();
while (list($qid,$e3,$e2,$fb) = sqlsrv_fetch_array($stmt1, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$qid][$e3])) {
        $data[$qid][$e3]= $newArray;
    }
    $data[$qid][$e3][$e2]= $fb;  
        print_r($fb);
}

[FooKelvin]

 

Thanks. Is working now.

 

The second part of array, am i doing thing correctly?

it seem that nothing to print out.

 

Here is my code.

$sql = "SELECT [question_id], [Expr3],[Expr2],
        FROM [RSA].[dbo].[feedbug]
        ORDER BY [Expr2]";
$stmt1 = sqlsrv_query($conn, $sql);
$data = array();
while (list($qid,$e3,$e2,$fb) = sqlsrv_fetch_array($stmt1, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$qid][$e3])) {
        $data[$qid][$e3]= $newArray;
    }
    $data[$qid][$e3][$e2]= $fb;  
        print_r($fb);
}

Okay, i found an error in the SQL. Here is the latest version:

$sql = "SELECT [question_id], [Expr3],[Expr2]
        FROM [RSA].[dbo].[feedbug]
        ORDER BY [Expr2]";
$data = array();

$stmt = sqlsrv_query($conn, $sql);
while (list($c, $q,$p) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$p][$c])) {
        $data[$p][$c]= $newArray2;
    }
    $data[$p][$c]= $q;
}
print_r($data);
    ?>

Output:

Array ( [M01] => Array ( [1] => Yes [02A ] => Yes [02B] => [3] => Newly Open [4] => Bad service [5] => Chicken Chop [6] => No [7] => [8] => No [9] => Yes ) [M02] => Array ( [1] => Yes [02A ] => Yes [02B] => [3] => Newly Open [4] => Dirty [5] => The Mushroom soup [6] => No [7] => [8] => No [9] => Yes ) )

I hope i am doing the correct thing..

 

Edited October 17, 2016 by FooKelvin

[mac_gyver]

I hope i am doing the correct thing..

 

 

what do you mean, 'you hope.' that's why you test code, to confirm if it produces the correct result. that would tell you if the code is correct or if you must go back and find what it is doing wrong.

[FooKelvin]

Hi Mac,

 

Thanks for your help. 

 

In case somebody need it.

    $sql2 = "SELECT DISTINCT [question_id],q_text 
        FROM [RSA].[dbo].[feedbug]
        ORDER BY [question_id]";
    $ques = array();
    $stmt2 = sqlsrv_query($conn, $sql2, $ques);
    while ($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {
        $ques[$row['question_id']] = $row['q_text'];
    }

$sql = "SELECT [question_id], [Expr3],[Expr2]
        FROM [RSA].[dbo].[feedbug]
        ORDER BY [Expr2]";
$data = array();

$stmt = sqlsrv_query($conn, $sql);
while (list($c, $q,$p) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$p][$c])) {
        $data[$p][$c]= $newArray2;
    }
        $data[$p][$c][]= $q; 
}

This is working perfectly using Mac's method.