+- 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: Help with array refencing database table. (/showthread.php?tid=14637)
Help with array refencing database table. - El Forum - 01-09-2009
[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">
<?php
if (count($works)){
foreach ($works as $key => $list){
?>
<div class="works-thumb">
<a href="#" title="<?php echo $list['desc_short']; ?>">
<img class="img-web-thumb" alt="<?php echo $list['project_name']; ?> Thumbnail" src="images/works/<?php echo $list[" />
</a>
<div class="tooltip">
<p>This is a small Tooltip with the Classname 'Tooltip'</p>
</div>
<p>
<ul>
<?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>'; }
}
?>
</ul>
</p>
<p><?= $list['project_name']; ?></p>
</div>
<?php
}
}
?>
</div>Help with array refencing database table. - El Forum - 01-09-2009
[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]].
Help with array refencing database table. - El Forum - 01-09-2009
[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.