Gathering data BASED on already gathered data?

[eluser]rxcjack[/eluser]
I'm building a "todo" list of sorts that I can expand on later. I have a database called "tasks" and two tables called "todo_lists" and "todo_entries."

I have /localhost/tasks/todo/ listing the todo lists (title and description)through a foreach but I'd like to obviously have the actual todo entries associated with each list below the respective list. I'm having a bit of trouble figuring out how I would do that as far as gathering the data for each entry, and then displaying them under the right list. The todo_entries table has id,list,status,text. Obviously "list" is the respective list in todo_lists that the entry goes with.

How would I go about gathering the data properly and displaying the right entries under the right list?

[eluser]tonanbarbarian[/eluser]
2 main ways to do this

the way i do not like doing (too many queries) is that in the foreach loop that displays the todo lists you run a query - one for each list, that finds the associated entires.
and of course i prefer to do this in the controller rather than the view

the other way that i prefer is to write your original query to join the tables and retrieve all of the data in one go
then when you loop through the data you detect the change of list and display accordingly

[eluser]rxcjack[/eluser]
Could you provide a code example? This is what I have in my view at the moment for displaying the lists.

Code:

<?php foreach($result as $row):?>
<dl>
<dt><strong>&lt;?=$row->name?&gt;</strong></dt>
<dd>&lt;?=$row->description?&gt;</dd>
</dl>
<br />
<ul>
    &lt;!-- For loop goes here --&gt;
    <li>EACH ENTRY HERE</li>
    &lt;!-- For loop ends here --&gt;
&lt;?php endforeach;?&gt;

[eluser]kgill[/eluser]
What you want is a join, join your todo_lists table to the todo_entries table using the list id. If it's possible to have an entry in todo_lists without an entry in todo_entries then you want a left outer join instead. This will return data like so:

Code:

list1   entry1
list1   entry2
list1   entry3
list2   entry1
list3   entry1
list3   entry2

The fact that you get repeating values in the first column makes people think this is somehow wrong but that's exactly what you want. You just need to alter your logic a bit to deal with them.

Code:

// instead of
foreach ($foo as $bar) {
  echo $bar['list_item'];
  echo $bar['list_entry'];
}

// you need
$curr_item = null;
foreach ($foo as $bar) {
  if ($bar['list_item'] <> $curr_item) {
    echo $bar['list_item'];
    echo $bar['list_entry'];
    $curr_item = $bar['list_item'];
  } else {
    echo $bar['list_entry'];
  }
}

So basically each time the value in list_item changes you print the list item otherwise you just print the entry - hope that makes sense.

- K

[eluser]rxcjack[/eluser]
Unfortunately I'm new to this and that didn't make a lot of sense. Where and how exactly would I adapt that to my situation?

[eluser]kgill[/eluser]
What exactly are you confused about, joins or the code I posted? Joins: read up on them and if you still don't get it after you've researched them then ask for help. The code: that's just an example of how to deal with the fact that the some of the data for each list will appear multiple times.

Queries with joins return data like the example I showed up there, but most people want their data like this:

Code:

list1  entry1
       entry2
       entry3
list2  entry1
       entry2
list3  entry1
       entry2

which isn't going to happen - hence the need to rewrite your logic a bit to deal with how data does come back. I just noticed you're using objects and not arrays so that might be part of what is confusing you, $row->name would be the same as $row['name'] if you were using arrays. As for adapting it to your situation, that's your job - the best way to learn this stuff is to try it yourself and experiment to see what comes out when you do something.