Welcome Guest, Not a member yet? Register   Sign In
A foreach inside a foreach?
#1

[eluser]skooter[/eluser]
Hi, just started learning Codeigniter Smile

I have 2 tables in my database. One contains questions and one contains possible answers for the questions. Now I want to list all questions with their belonging answers, like so:

Quote:Q) How are you?
A) Fine
A) Okay
A) Bad

Q) Where are you from?
A) Earth
A) Mars
A) Jupiter

How would I do that? To be more specific this is how it could be done in old fashion PHP:

Code:
$res_questions = mysql_query("...");
while ($row_question = mysql_fetch_array($res_questions))
{
  echo $row_question["Question"]; // Qutput quesitons

  $res_answers = mysql_query("SELECT ID, Answer FROM tblAnswers WHERE QuestionID=".$row_question["ID"]);
  while ($row_answers = mysql_fetch_array($res_answers))
  {
    echo $row_answers["Answer"]; // Output answers
  }
}
#2

[eluser]TheFuzzy0ne[/eluser]
I would probably have a model method that does both returns a nicely formatted array for you. That way, you should only need to query the database twice.

It's hard to give a more specific answer, as I don't know what data you need on your page, such as the question or answer IDs.

Are you actually using CodeIgniter? Because in CodeIgniter, database queries go through $this->db.
#3

[eluser]skooter[/eluser]
I am indeed using Codeigniter Smile With the code above I was just trying to prove my point. Anyway, I made this which seems to make my very simple question look complicated in Codeigniter. Also there's a problem:

Model:
Code:
$options_questions = array('SurveyID' => $input_id);
$query_questions = $this->db->getwhere('Questions', $options_questions);

if ($query_questions->num_rows() > 0)
{
    foreach ($query_questions->result() as $row_question)
    {
        $survey['question'][$row_question->ID] = $row_question->Question;
        
        $options_answers = array('QuestionID' => $row_question->ID);
        $query_answers = $this->db->getwhere('Answers', $options_answers);

        if ($query_answers->num_rows() > 0)
        {
            foreach ($query_answers->result() as $row_answer)
            {
                $survey['answer'][$row_question->ID][] = $row_answer->Answer;
            }
        }
    }
}

View:
Code:
<ul>
    &lt;?php foreach($survey['question'] as $question_id => $question): ?&gt;
    <li>&lt;?=$question?&gt;
        <ul>
            &lt;?php foreach($survey['answer'][$question_id] as $answer): ?&gt;
            <li>&lt;?=$answer?&gt;</li>
            &lt;?php endforeach;?&gt;
        </ul>
    </li>
    &lt;?php endforeach;?&gt;
</ul>

... When there's no answers to errors is thrown - both pointing at the second foreach in the view:
1) Message: Undefined offset: 2
2) Message: Invalid argument supplied for foreach()

I understand why the show up but not how to fix it - of course putting an "if" inside the view is no-go.

Also is this really the way to do it or are there an easier option?
#4

[eluser]TheFuzzy0ne[/eluser]
Why can you not use if statements in your view?

Here's how I'd suggest you do it (with only two queries).

Code:
$survey = array();
$where_in = array();

$this->db->where('survey_id', $input_id);
$questions = $this->db->get('Questions');

foreach ($questions->result() as $question)
{
    $survey['question'][$question->ID] = $question->Question;
    $where_in[] = $question->ID;
}

$this->db->where_in($where_in);
$answers = $this->db->get('Answers');

foreach ($answers as $answer)
{
    $survey['answer'][$answer->QuestionID] = $answer->Answer;
}
Simple, no? It's a little less code, and a little less complicated IMHO.

You'll need to modify it a bit, but hopefully you can see the concept. I don't fully understand the dynamics of your app, for example, how many questions you have, and what parameters define which questions you get, but if your questions need to be filtered, you can do that with a where clause.
#5

[eluser]kgill[/eluser]
Using a loop to build the other query and running a separate query for every question to get the answers is inefficient when you can grab the data in one go. Join the questions and answers tables (outer join if you can have questions with no answers) in your select and then fetch it back in one giant array.

Your array will return data that's more or less in this format:
Code:
question               answer
--------------------- ---------
"How are you?",        "Fine"
"How are you?",        "Okay"
"How are you?",        "Bad"
"Moo?",                NULL
"Where are you from?", "Earth"
"Where are you from?", "Mars"
"Where are you from?", "Jupiter"

Now all you need to do is code logic to display that properly:
Code:
// example-code follows
$question = '';
foreach (q_an_a as $data) {
  if ($data['question'] <> $question) {
    echo 'Q) ' . $data['question'];
    $question = $data['question'];
  }
  if (isset($data['answer'])) {
    echo 'A) '. $data['answer'];
  }
}
So each time the question changes you print it out, if there's an answer you print it out as well, and that will stop the running 13 queries just to get 12 questions and their answers.
#6

[eluser]skooter[/eluser]
Quote:Why can you not use if statements in your view?
Well I thought the hole idea with views was to keep most logic out of them and just plain and simple Smile

Quote:I don’t fully understand the dynamics of your app ...
It's like a backend for creating surveys. So, in theory there can be an unlimited no. of questions and answers or there can be non.

Thanks guys. You gave me something to work with.
#7

[eluser]kgill[/eluser]
The idea of a view is to present your information, so you want to keep application logic out of it but presentation logic belongs there. Looping through an array to display it on the screen is fine, the same goes for if statements which decide whether or not something should display.
#8

[eluser]skooter[/eluser]
Okay so this is what I ended up with (which works):

Model:
Code:
$survey = array();
$where_in = array();

$this->db->where('SurveyID', $id);
$questions = $this->db->get('Questions');

foreach ($questions->result() as $question)
{
    $survey['question'][$question->ID] = $question->Question;
    $where_in[] = $question->ID;
}

$this->db->where_in('QuestionID', $where_in);
$answers = $this->db->get('Answers');

foreach ($answers->result() as $answer)
{
    $survey['answer'][$answer->QuestionID][$answer->ID] = $answer->Answer;
}

View:
Code:
<ul>
    &lt;?php foreach($survey['question'] as $question_id => $question): ?&gt;
    <li>&lt;?=$question?&gt;
        &lt;?php if (!empty($survey['answer'][$question_id])): ?&gt;
        <ul>
            &lt;?php foreach($survey['answer'][$question_id] as $key => $answer): ?&gt;
            <li>&lt;?=$answer?&gt;</li>
            &lt;?php endforeach;?&gt;
        </ul>
        &lt;?php endif;?&gt;
    </li>
    &lt;?php endforeach;?&gt;
</ul>

Thanks again Smile




Theme © iAndrew 2016 - Forum software by © MyBB