pass parameter to a select query from previous query

[eluser]Unknown[/eluser]
I'm creating a little management tool for the browser game travian. So I select all the villages from the database and I want to display some content that's unique to each of the villages. But in order to query for those unique details I need to pass the id of the village. How should I do this?

this is my code (controller):

Code:

function members_area()
    {
        global $site_title;
        $this->load->model('membership_model');

        if($this->membership_model->get_villages())
        {
            $data['rows'] = $this->membership_model->get_villages();
            $id = 1;//this should be dynamic, but how?
            if($this->membership_model->get_tasks($id)):
                $data['tasks'] = $this->membership_model->get_tasks($id);
            endif;
        }

        $data['title'] = $site_title." | Your account";
        $data['main_content'] = 'account';
        $this->load->view('template', $data);
    }

and this is the 2 functions I'm using in the model:

Code:

function get_villages()
{
    $q = $this->db->get('villages');

    if($q->num_rows() > 0) {
        foreach ($q->result() as $row) {
            $data[] = $row;
        }
    return $data;
    }
}

function get_tasks($id)
{
    $this->db->select('name');
    $this->db->from('tasks');
    $this->db->where('villageid', $id);

    $q = $this->db->get();

    if($q->num_rows() > 0) {
        foreach ($q->result() as $task) {
            $data[] = $task;
        }
    return $data;
    }
}

and of course the view:

Code:

<?php foreach($rows as $r) : ?>
        <div class="village">
            <h3>&lt;?php echo $r->name; ?&gt;</h3>
            <ul>
                &lt;?php foreach($tasks as $task): ?&gt;
                    <li>&lt;?php echo $task->name; ?&gt;</li>
                &lt;?php endforeach; ?&gt;
            </ul>
            &lt;?php echo anchor('site/add_village/'.$r->id.'', '+ add new task'); ?&gt;
        </div>
    &lt;?php endforeach; ?&gt;

[eluser]SomeFunkyDude[/eluser]
IDK if this is what your looking for, but there's a database helper method called $this->db->insert_id() that lets you get the id of the row you just inserted into a database.

[eluser]Unknown[/eluser]
thanks but no, the information is allready in the database. I would have done the following with regular php. hopefully you get what I mean then:

Code:

$villages = mysqli_query($link, "SELECT * FROM villages");
while($r_villages = mysqli_fetch_object($villages)):
    $tasks = mysqli_query($link, "SELECT * FROM taks WHERE villageid='".$r_villages->id."'");
    while($r_tasks = mysqli_fetch_object($taks)):
        //get all the tasks from the villages
    endwhile;
endwhile;

can't believe how much I need to type to make this work in the framework...

[eluser]mddd[/eluser]
For one thing: why are you reading every row separately and then putting them in an array? You do this in both functions. That is unneccessary. You could write your first function as:

Code:

function get_villages()
{
    return $this->db->get('villages')->result_array();
}

and it would do exactly the same!

The other thing: you could combine both queries into one, using a JOIN. Thereby making only one query to the database.
Something like

Code:

SELECT * FROM villeges JOIN tasks ON tasks.villageid = villages.id

Then you must do a little extra work to find which rows of the results are about the same village, but that is not too bad. You could do:

Code:

function get_villages_with_tasks()
{
$sql = "SELECT * FROM villeges JOIN tasks ON tasks.villageid = villages.id";
$data = $this->db->query($sql)->result_array();
$list = array();
foreach($data as $row)
{
   // add the village to the list if it is not already there
   if (!isset($list[$row['id']]))
   {
      $list[$row['id']] = $row;
      $list[$row['id']]['tasks'] = array(); // make a place to store tasks for this new village
   }
   // now add the task from this row
   $list[$row['id']]['tasks'][] = $row;
}
return $list;
}

This list will be an array of villages, each containing an element 'tasks' containing the villages tasks.
I know that the village will also contain the data for the first task, and the tasks will also contain the village info, but I think that is ok. You could clean it up some more if you wanted to.

[eluser]Unknown[/eluser]

why are you reading every row separately and then putting them in an array?

because I'm new to this and I followed the "codeigniter from scratch" tutorial serie at nettuts

thanks, I'll check the code