CODEWITHSUNDEEP
phpmysql
Michael Samuel
Michael Samuel

Reputation: 3920

Display results of MYSQL in PHP properly

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

Upvotes: 0

Views: 87

Answers (4)

Krishnadas V R
Krishnadas V R

Reputation: 1

<?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>';

?>

Upvotes: 0

anon
anon

Reputation:

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>";
    }
}
?>

Upvotes: 1

Styphon
Styphon

Reputation: 10447

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>';
}

Upvotes: 1

Krishnadas V R
Krishnadas V R

Reputation: 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

Upvotes: -1

Related Questions