CodeIgniter Forums
need to retrieve records based on a value from another query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: need to retrieve records based on a value from another query (/thread-37315.html)



need to retrieve records based on a value from another query - El Forum - 01-05-2011

[eluser]Brad K Morse[/eluser]
UPDATE 2:15pm: I am now thinking I can do it this way

controller

Code:
$data['comments'] = $this->model->getComments($id);

foreach($data['comments']['comment_id'] as $comment_id):
  $data['comment_info']['comment_id'] = $comment_id;
  $data['comment_info']['rank'] = $this->model->getStepRank($comment_id);
endforeach;

view

Code:
foreach($comments as $c):
  foreach($comment_info as $i):
    // display all the step ranks for the comment
    if($c->comment_id == $i->comment_id) {
       print 'rank: '.$i->rank;
    }
  endforeach;
  // display actual comment
  print 'message: '.$c->message;

  // display author
  print 'author: '.$r->author;
endforeach;

So it would print out like this:

Quote:rank: 4 5 8 9 message: this is a test message author: mark twain

UPDATE 1:55pm: Just started writing this down on paper to make more sense of how this could be done.

Would it possible to use a two-dimensional array, to store multiple arrays, to be looped thru in the view and print if the conditional is true?

example:

Code:
<?php

// this would be generated dynamically with the other query
$ranks = array(
    0    =>    array(
        'comment_id'    => 1,
        'step_id'            => 4
    ),
    
    1    =>    array(
        'comment_id'    => 1,
        'step_id'            => 5
    ),
    
    2    =>    array(
        'comment_id'    => 1,
        'step_id'            => 8
    ),
    
    3    =>    array(
        'comment_id'    => 1,
        'step_id'            => 9
    )
);

foreach($comments as $c):
    foreach($ranks as $r):
        if($c->comment_id == $r->comment_id) {
            print $r->rank;
        }
    endforeach;
endforeach;
?>

A user submits a comment, the user can relate it to multiple steps.

I have a query that retrieves all the comments made about a step, based on the step id.

Since a comment can relate to multiple steps, I used DISTINCT, to return the comment once, otherwise you will see the same comment multiple times (based on how many steps it relates to).

I print this information onto the webpage into a tubular format and one of the columns is called step rank, which is stored within STEPS.RANK

I can not use that in the distinct select statement, otherwise it will list each comment * the total of steps it relates to, so I took that part out, so it doesn't display the same comment multiple times.

I have a table that relates the comment and what steps it relates to.

So when the user entered the comment, say the user related it to step 4, 5, 8, 9

Then it would get inserted into the RELATIONSHIP (fields: comment_id, step_id) table as

Code:
1 | 4
1 | 5
1 | 8
1 | 9

I know I will have to take the comment_id from the DISTINCT query, pass it into another query, which will grab every step_id from the RELATIONSHIP table where comment_id equals the comment_id from the first query.

The DISTINCT query returns all the rows at once. So how would I be able to take the comment_id from that query, pass it into another query, to compare all the comment_id's of the RELATIONSHIP, to get the step_id's, then compare it to STEPS.id, to return STEPS.rank into any array, which will then be printed into the view as a list of ranks for each comment row or would I need three queries to accomplish this?


need to retrieve records based on a value from another query - El Forum - 01-05-2011

[eluser]Victor Michnowicz[/eluser]
I don't know exactly what you are trying to do. But this info may help you out a bit. I would suggest creating your arrays with array keys matching your entry IDs:

[3]
['author'] => 'bob',
['title'] => 'my comment'
[12]
['author'] => 'tom',
['title'] => 'my other comment'
[22]
['author'] => 'sue',
['title'] => 'a comment'

instead of:

[0]
['id'] => 3,
['author'] => 'bob',
['title'] => 'my comment'
[1]
['id'] => 12,
['author'] => 'tom',
['title'] => 'my other comment'
[2]
['id'] => 22,
['author'] => 'sue',
['title'] => 'a comment'

This allows you to add in your steps kinda like this

Code:
foreach ($steps as $step)
{
    $comments[$step['comment_id']['steps'][] = $step['step_id'];
}

And you could get around having to use DISTINCT like so:

Code:
foreach ($steps as $step)
{
    $comments[$step['comment_id']['steps'][$step['step_id']] = $step['step_id'];
}

So now your array will look like this:

[3]
['author'] => 'bob',
['title'] => 'my comment',
['steps']
[0] => 4,
[1] => 5,
[2] => 19
[12]
['author'] => 'tom',
['title'] => 'my other comment',
['steps']
[0] => 23,
[22]
['author'] => 'sue',
['title'] => 'a comment',
['steps']
[0] => 2,
[1] => 65,


need to retrieve records based on a value from another query - El Forum - 01-05-2011

[eluser]Brad K Morse[/eluser]
Your reply definitely helped!

I think it is working now, will confirm in the morning.

controller
Code:
$data['comments'] = $this->report_model->getCommentsNotDuplicateByGoal($id);

foreach($data['comments'] as $c):
  $data['comment_info'][$c->SP_COMMENTS_ID] =    $this->report_model->getStepRank($c->SP_COMMENTS_ID);
endforeach;

view

Code:
<?php
foreach($comments as $r):

  foreach($comment_info[$r->SP_COMMENTS_ID] as $c):
    print '['.$c->SP_STEPS_RANK.'] ';
  endforeach;

endforeach;
?>