Welcome Guest, Not a member yet? Register   Sign In
need to retrieve records based on a value from another query

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


$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);


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;
  // display actual comment
  print 'message: '.$c->message;

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

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?



// 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;

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

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?

Messages In This Thread
need to retrieve records based on a value from another query - by El Forum - 01-05-2011, 12:33 PM

Theme © iAndrew 2016 - Forum software by © MyBB