Display results of MYSQL in PHP properly

Question

I'm building a simple quiz PHP app and I have an SQL query that returns the following results:

question_id | question_title | answer_title
   1        |   your name?   |   michael
   1        |   your name?   |   samuel
   2        |   your age?    |    20
   2        |   your age?    |    21
   2        |   your age?    |    23

Now I want to display each question with its answers in format like this:

<div class="question">
     <div>your name?</div>
     <div class="answer">michael</div>
     <div class="answer">samuel</div>
</div>

<div class="question">
     <div>your age?</div>
     <div class="answer">20</div>
     <div class="answer">21</div>
     <div class="answer">22</div>
</div>

I'm using PDO to display MYSQL results. The problem if I loop through the MYSQL results, a question will be displayed multiple times as It's returned in each row. Also don't know how to add the closing DIV tag for the question class after all answers for a certain question are displayed.

Thanks

Styphon · Accepted Answer · 2015-07-17 12:18:05Z

1

What you need to do is group all the answers for each question in a multiple dimension array. Try something like this:

$conn = new PDO($dsn, $user, $pass);
$query = ''; // query to run.
$answers = array();
foreach ($conn->query($query) as $row)
{
    $answers[$row['question_id']][] = $row['answer_title'];
}

Then you can do a foreach inside a foreach:

foreach ($answers as $question => $arr)
{
    echo '<tr><td>Question: '.$question.'</td><td>Answers: ';
    foreach ($arr as $i => $answer)
    {
        echo ($i > 0 ? ', ' : '').$answer;
    }
    echo '</td></tr>';
}

answered Jul 17, 2015 at 12:18

Styphon

10.5k9 gold badges54 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Michael Samuel Over a year ago

the array is printing numbers without the question title or answer title

Styphon Over a year ago

What numbers? I can't see your screen remember. Surely you should be able to work out what you need to put in the array from what I've given you if it's not 100% right.

Michael Samuel Over a year ago

the first array is not printing the text in rows...seems like it is printing only keys

Styphon Over a year ago

That'll be because I stored it by the question ID. Can't you think of a way to get the question to output? It's fairly simple,

score 1 · Accepted Answer · 2015-07-17 12:53:27Z

1

You can use a class and function, as such:

<?php
class GetInfo {
    public static function GetData() {
        $link = new mysqli();
        $link->real_connect($hostName, $username, $password, $databaseName);

        $rs = $link->query("SELECT * FROM `table`;");
        print "<table>";
        while ($result = mysqli_fetch_assoc($rs)) {
            print "<tr>";
                print "<td>" . $result['questionColumn'] . " = " . $result['answerColumn'] . "</td>";
            print "</tr>";
        }
        print "</table>";
    }
}
?>

And then call it as so:

GetInfo::GetData();

I hope this helps you :)

(Sorry it's not PDO, I don't know PDO very well so it would be wrong of me to assume :) )

EDIT

<?php
class GetInfo {
    public static function GetData() {
        $link = new mysqli();
        $link->real_connect($hostName, $username, $password, $databaseName);

        $rs = $link->query("SELECT * FROM `table`;");
        print "<table>";
        $last = "";
        while ($result = mysqli_fetch_assoc($rs)) {
            print "<tr>";
            $printing = ($last != $result['questionColumn'] ? $result['questionColumn'] : "";
                print "<td>" . $printing . " = " . $result['answerColumn'] . "</td>";
            print "</tr>";
            $last = $result['questionColumn'];
        }
        print "</table>";
    }
}
?>

edited Jul 17, 2015 at 12:53

answered Jul 17, 2015 at 11:54

anon

6 Comments

Michael Samuel Over a year ago

This still doesn't solve the problem. It will display the same question multiple times. I want it to display a question only once and all answers under it and and the question/answer combo wrapped in a question class

anon Over a year ago

Using this will remove that because of the fetch assoc, each result shows once, that or you have the same question/answer combo in which use use SELECT DISTINCT ... in your query

Michael Samuel Over a year ago

SELECT DISTINCT will make the query display only one answer per question not all the answers

anon Over a year ago

Using mysqli_fecth_assoc steps through each row one at a time, no rows are traversed ore than once - meaning, the only way this would produce the same results is if you have more that one of each result in the database. Using mysqli_fetch_assoc will do the code in the while almost like saying: foreach ($row as $key=>$value) { print "{$key} = {$value}"; }. mysqli_fetch_assoc never uses the same row twice [see here][php.net/manual/en/mysqli-result.fetch-assoc.php]

Michael Samuel Over a year ago

I'm not sure about this as I use PDO not MYSQLi. Also I don't have multiple rows. Each row has a different answer but multiple rows belong to the same question. So I can't skip rows. Hope it's clear

|

Krishnadas V R · Accepted Answer · 2015-08-03 14:07:31Z

<?php
$qArr =  array(
    array(
        "id" => "1",
        "title" => "your name",
        "ans" => "michael"
    ),
    array(
        "id" => "1",
        "title" => "your nam?",
        "ans" => "samuel"
    ),
    array(
        "id" => "2",
        "title" => "your age",
        "ans" => "20"
    ),
    array(
        "id" => "2",
        "title" => "your ag?",
        "ans" => "21"
    ),
    array(
        "id" => "2",
        "title" => "your age",
        "ans" => "22"
    )
); 

$flagLast = false;
$prevQuestionId = "";

foreach($qArr as $infoArr) {
    $questionId = $infoArr['id'];
    $title = $infoArr['title'];
    $ans = $infoArr['ans'];

    if($prevQuestionId != $questionId) {
        if($flagLast) {
            echo '</div>';
        }
        echo '<div class="question">';
        echo '<div>' . $title . '</div>';
        echo '<div class="answer">' .  $ans .  '</div>';
        $prevQuestionId = $questionId;
    }
    else {
        echo '<div class="answer">' .  $ans .  '</div>';
        $flagLast = true;
    }
} 
echo '</div>';

?>

Krishnadas V R · Accepted Answer · 2015-07-17 12:00:54Z

-1

Here you can fetch the records Order by question id. Also you have to define one PrevQuestionID outside the loop.

Check if the PrevQuestionID is not the Current QuestionId then Print the Question and assigned the Current QuestionId to PrevQuestionID.

This will work for you

answered Jul 17, 2015 at 12:00

Krishnadas V R

11 bronze badge

2 Comments

Michael Samuel Over a year ago

can you explain with code? I'm really fed up with this. Either question one not getting printed at all or one answer is shifted to another question. I can't really get it to work well

Krishnadas V R Over a year ago

Please check the Post i have added and its working code. Instead of DB am fetching from an array. Please note that while fetching the record fetch it in order by question id

Collectives™ on Stack Overflow

Display results of MYSQL in PHP properly

4 Answers 4

4 Comments

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related