php - how to show data from 3 combinational table using codeIgniter -

php - how to show data from 3 combinational table using codeIgniter -

i have 3 database tables, question_set, question , answer. question_set contains set_name , set_id question contains question , question_set_id answer contains answer , question_id

i can not work out way show info in 1 view file, tried joins question info repeating prints because every question has 3 or 4 answer.

just thought point out.

your database design horrible.

it not scale well, , infact not think actually. work.

the way is

three tables

sets => id, name questions => id, set_id, question answers => id, set_id, question_id, answer, points

note in above illustration taking 1 step further, rather each reply beingness true or false, can have assigned point value. hence, reply deemed half right given 5 points, right reply given 10 points, works boolean true, have right reply 1 point, , wrong reply 0 points

anyway.

with above table design in mind.

you do

$this->db->select('s.id set, s.name name, q.id qid, q.question qu, a.id aid, a.answer an, a.points p') ->from('sets s') ->join('questions q', 'q.set_id = s.id') ->join('answers a', 's.set_id = s.id') ->where('s.id', 'set id'); $questions = $this->db->get(); $set = array('questions' => array()); foreach($questions $s){ $set['id'] = $s->set; $set['name'] = $s->name; $set['questions'][$s->qid]['id'] = $q->qid; $set['questions'][$s->qid]['question'] = $q->qu; if(!isset($set['questions'][$s->qid]['answers'])) $set['questions'][$s->qid]['answers'] = array(); $set['questions'][$s->qid]['answers'][] = array( 'id' => $q->aid, 'answer' => $q->an', 'points' => $q->p ); }

so end array looks like

array( 'id' => 1, 'name' => 'my first quiz', 'questions' = array( array( 'id' => 1, 'question' => 'what 1+1+1?', 'answers' => array( array( 'id' => 1, 'answer' => 1, 'points' => 0 ), array( 'id' => 2, 'answer' => 2, 'points' => 0 ), array( 'id' => 3, 'answer' => 3, 'points' => 1 ) ) ), array( 'id' => 2, 'question' => 'what 2+2+2?', 'answers' => array( array( 'id' => 4, 'answer' => 6, 'points' => 1 ), array( 'id' => 5, 'answer' => 2, 'points' => 0 ), array( 'id' => 6, 'answer' => 3, 'points' => 0 ) ) ) ) );

then can do.

echo '<h2>'.$set['name'].'</h2>'; foreach($set['questions'] $q){ echo '<div class="question">'; echo '<h3>'.$q['question'].'</h3>'; echo '<div class="answers">'; foreach($q['answers'] $a){ echo '<label for="a'.$a['id'].'">'.$a['answer'].'<input type="checkbox value="'.$a['id'].'" name="q'.$q['id'].'" /></label><br />'; } echo '</div>'; echo '</div>'; }

php mysql codeigniter