Help with array refencing database table.

[eluser]spmckee[/eluser]
Greetings,

This is my first dive into CI and am loving it but running into a small snag. I can get the whole "Works Table" array and pass it on to the view for display but I need to change the values of "task_1, task_2 and task_3" into the names from the "Task Table", which is currently not happening.

Any help is greatly appreciated.

Thanks!

Works Table:
+++++++++++++++++++++++++++++++++++++++++++++++
ID name task_1 task_2 task_3
----------------------------------------------
1 Nike 1 3 3
2 Dell 3 1 5
3 IBM 4 2
+++++++++++++++++++++++++++++++++++++++++++++++


Task Table:
++++++++++++++
ID name
--------------
1 Design
2 Development
3 Research
4 Concepting
++++++++++++++


The Model

Code:

class MOurWork extends My_Model{

    function MOurWork(){
        parent::My_Model();
    }
    
function getAllWorks(){
     $data = array();
     $query = $this->db->query('SELECT * FROM works WHERE active="1"');
     if ($query->num_rows() > 0){
       foreach ($query->result_array() as $row){
         $data[] = $row;    
       }
    }
    $query->free_result();
    return $data;
}
}

The Controller

Code:

<?php
class Our_work extends My_Controller {

  function our_work()
  {
    parent::My_Controller();
  }

  function index()
  {
      $data['main'] = 'our_work';
      $data['works'] = $this->MOurWork->getAllWorks();
      $this->load->view('template', $data);
  }
}
?>

The View

Code:

<div class="works-thumbs-container">
&lt;?php

if (count($works)){
    foreach ($works as $key => $list){
?&gt;

    <div class="works-thumb">
        <a href="#" title="&lt;?php echo $list['desc_short']; ?&gt;">
        <img class="img-web-thumb" alt="&lt;?php echo $list['project_name']; ?&gt; Thumbnail" src="images/works/&lt;?php echo $list[" />
        </a>
            
        <div class="tooltip">
               <p>This is a small Tooltip with the Classname 'Tooltip'</p>
        </div>
            
           <p>
        <ul>
        &lt;?php
                            
        for ($i = 1; $i < 3; $i++)
        {
        $task_key_name = 'task_name_'.$i;
        if ($list[$task_key_name]!='NULL'){ echo '<li>'.$list[$task_key_name].'</li>'; }
}
?&gt;
        </ul>
        </p>
    
        <p>&lt;?= $list['project_name']; ?&gt;</p>
    </div>

&lt;?php
   }
}
?&gt;
</div>

[eluser]kgill[/eluser]
You can do that either in the query using a join or in code. Because of the way you've structured your tables the query is going to be ugly, basically you're going to end up joining the works table to the tasks table 3 times (one for each task) which also means aliasing the tasks table to prevent ambiguous columns. To do it in code, put a 2nd function in your model for getting the tasks, fetch those into an array indexed by the task id; that way you can just dump the task out to the screen using your $list[$task_key_name], e.g. $tasks[$list[$task_key_name]].

[eluser]OwanH[/eluser]
Hey,

I would suggest normalizing your database further so you can have better table relationships. Here are what the normalized tables would look like (I'm arbitrarily assuming the names in your works table are clients):

Client Table:
+++++++++
ID name
—————————
1 Nike
2 Dell
3 IBM
+++++++++

Task Table:
++++++++++++++
ID name
—————————————
1 Design
2 Development
3 Research
4 Concepting
++++++++++++++

Works Table:
+++++++++++++++++++++++++++++++++++++
ID client_id task_id active
—————————————————————————————————————
1 1 1 1
2 1 3 1
3 1 3 1
4 2 3 1
5 2 1 1
6 2 5 1
7 3 4 1
8 3 2 1
+++++++++++++++++++++++++++++++++++++

Your model will then become

Code:

class MOurWork extends My_Model {

    function MOurWork() {
        parent::My_Model();
    }
    
    function getAllWorks(){
        $data = array();
        $query = $this->db->query('SELECT works.id, works.client_id, client.name, works.task_id, task.name FROM works LEFT JOIN client ON (works.client_id=client.id) LEFT JOIN task ON (works.task_id=task.id) WHERE works.active="1"');
        if ($query->num_rows() > 0){
            foreach ($query->result_array() as $row){
                $data[] = $row;    
            }
        }
        $query->free_result();
        return $data;
    }
}

Hope this helps.