CodeIgniter Forums
Join one to many - 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: Join one to many (/showthread.php?tid=56116)

Join one to many - El Forum - 11-27-2012

[eluser]Unknown[/eluser]
I have to tables:

gallery -> id_gallery, name, desc, data

and:

images -> gallery_id, name,path

One gallery can have many images. I need to select all galleries and all images and on view page present all galleries and all images which contains that gallery. How can do this?

I have code like this for now:

Code:
$q = $this->db->select('*')->from('gallery')->join('images','images.gallery_id = gallery.id_gallery')->get();
  return $q = $q->result_array();

And foreach loop in view:

Code:
<?php foreach ($gallery as $gal):  ?>
    <figure>
    <a href="&lt;?php echo IMG ?&gt;galerija/&lt;?php echo $gal['name'] ?&gt;/&lt;?php echo $gal['path'] ?&gt;" rel="galerija[slike]" class="figure_img">
    <img src="&lt;?php echo IMG ?&gt;galerija/&lt;?php echo $gal['naziv'] ?&gt;/thumbs/&lt;?php echo $gal['path'] ?&gt;" >
    <figcaption>&lt;?php echo $gal['name']; ?&gt;</figcaption>
    </a>
    </figure>
    &lt;?php endforeach; ?&gt;

This foreach loop is producing 5 figure tags instead of one (if gallery have 5 images for example). I need to create one figure tag, and inside it I need to create a tag for every image in that gallery. How can I do this?

Join one to many - El Forum - 11-27-2012

[eluser]apodner[/eluser]
Here's a quick & dirty possibility (but the better solution is to set this all up as separate methods in a model):


Code:
$galleryQuery = $this->db->get('gallery');
foreach ($galleryQuery->result_array() as $key => $row) {
    $data[$key] = $row;
    $this->db->where('gallery_id', $row['id_gallery']);
    $imgQuery = $this->db->get('images');
    $data[$key]['images'] = $imgQuery->result_array();  
}
return $data;


View: ( I assume each gallery goes with one figure tag)

Code:
&lt;?php
foreach($data as $gallery) { ?&gt;
    <figure>
        &lt;?php
         foreach($gallery['image'] as $image) {
             /* code for whatever you do with each image in the figure */
         }
         ?&gt;
    </figure>

&lt;?php
}
?&gt;