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/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: need to retrieve records based on a value from another query (/showthread.php?tid=37315) |
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); view Code: foreach($comments as $c): 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 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 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) And you could get around having to use DISTINCT like so: Code: foreach ($steps as $step) 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); view Code: <?php |