CodeIgniter: Displaying data from two tables in a view - use one loop?

Question

I'd like some help please. I have these two database tables, with relationship 1-n (one album has many images):

  albums            images
----------       -------------
album_id            img_id
title               album_id
description         img_file

Now what I'm trying to do is to show all the albums with their images in my view, something like this, see the 2nd screenshot (portfolio).

This is what I have in my Controller:

// fetch all album data 
$albums = $this->album_model->get();

// fetch images for each album
foreach ($albums as $album ) {
 $images = $this->albumimage_model->get_by('album_id', $album->album_id);
}

$data = array(
 'albums'  =>  $albums, 
 'images'  =>  $images, 
);
$this->load->view('album_listing', $data);

If i loop through the album data in my view, I get this:

<?php foreach($albums as $album): ?>
<div class="album">
<p><?php echo $album->title; ?></p>
<p><?php echo $album->description; ?></p>
<div>
<?php endforeach; ?>

So far so good, but now here comes the headache: I want to display the images that belong to each album, so basicle something like this:

<?php foreach($albums as $album): ?>
<div class="album">
    <p><?php echo $album->title; ?></p>
    <p><?php echo $album->description; ?></p>

    // show all images of the album
    <div class="images"><img src="uploads/<php? echo $album->title;  ?>.'/'.<php? echo $image->image_file;  ?> " /></div>

<div>
<?php endforeach; ?>

How can I accomplish that, according to the CodeIgniter practices???

Some additional info: 1. I have also enable the CodeIgntier's Profiler and I've noticed that the running queries are incremented like this. Whouldn't this increase the memmory in case I have many albums (50 - 100 albums with images)???

I also tried to do this using just one query with JOIN, something like this:

$albums = $this->db->select('albums.*') ->select('images.img_id, images.img_file') ->join('images', 'images.album_id = albums.album_id') ->order_by('albums.album_id') ->get('albums') ->result_array();

The problem with this is when I loop through the data (lets say I have two albums in my database), I get the data twice on my page, like :

album1 - images of album 1
album1 - images of album 1
album2 - images of album 2
album2 - images of album 2

Are you using jamierumbelow's base model?

W Kristianto
– W Kristianto

2013-12-20 09:26:47 +00:00
Commented Dec 20, 2013 at 9:26 — W Kristianto
– W Kristianto, Commented Dec 20, 2013 at 9:26
No I use this codeigniter.tv/videos

ltdev
– ltdev

2013-12-20 09:28:32 +00:00
Commented Dec 20, 2013 at 9:28 — ltdev
– ltdev, Commented Dec 20, 2013 at 9:28

Amit bhalani · Accepted Answer · 2017-08-03 06:56:12Z

1

$query = $this->db->get('albums');
$albums = $query->result_array();

$query = $this->db->get('images');
$images =$query->result_array();

foreach ($albums as $album)
{

    foreach ($images as $image)
    { 
        if($image['album_id'] == $album['album_id']){
            $data[$album['album_id']]['images'] = $image['album_id'];
        }
    }
}

var_dump($data);

edited Aug 3, 2017 at 6:56

Amit bhalani

3111 bronze badges

answered Dec 20, 2013 at 9:21

sas

2,5971 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

ltdev Over a year ago

I've tested this but it returns the album data many times. See waht I've writen in my post above please

sas Over a year ago

I have edited please check. and use ->get() instead of ->get('albums')

ltdev Over a year ago

When you loop through this query, will alway return many times the same album data. Its the same with what I've done.

sas Over a year ago

can I have a look on you table data

ltdev Over a year ago

I have posted my table structure on the post. please review

|

Damaged Organic · Accepted Answer · 2013-12-20 09:14:40Z

0

If you want to get rid of duplicate entries try to use SELECT DISTINCT in your query, which serves for similar purposes. Also, I don't really understand what do you mean by:

running queries are incremented like this

answered Dec 20, 2013 at 9:14

Damaged Organic

8,4776 gold badges64 silver badges90 bronze badges

4 Comments

ltdev Over a year ago

the SELECT DISTINCT where should I test it? in the case of the JOIN query or with the 2 queries??

Damaged Organic Over a year ago

It's when you get your data twice, with JOIN.

ltdev Over a year ago

return a mysql error: Column 'album_id' in order clause is ambiguous

Damaged Organic Over a year ago

It means that you are trying to order by a column name that is used in more than one table; but in your statement it's ok: order_by('albums.album_id'). You should use it like $this->db->distinct().

Bon Boon · Accepted Answer · 2013-12-20 09:41:27Z

0

You Can Do Something Like Following

Controller -

// fetch all album data <br>
$data['albums']= $this->album_model->get_albums();
// fetch all images<br>
$data['images']=  = $this->album_model->get_images();
$this->load->view('album_listing', $data);<br>

View (album_listing.php)

foreach ($albums as $album)
{

    foreach ($images as $image)
    { 
        if($image->album_id == $album->album_id)
        echo "Album - ".$album->album_id.", Title".$album->title.", Description".$album->description." Image ID - ".$image->img_id."Img File - ".$image->img_file;
    }
}

In Alternate In your Model

    public function get_images(){
    { 
       $sql ="select a.album_id, a.description, i.img_id, i.img_file from album a, image i where a.album_id = i.album_id"
       $query = $this->db->query($sql);
       $data   =   array();
       foreach ($query->result() as $row) {
        $data[] = $row;
       }
    return $data;
    }

Controller

 $data['albums_img']= $this->album_model->get_images();
 $this->load->view('album_listing', $data);

View

 foreach ($albums_img as $album)
 { 

    echo "Album - ".$album->album_id.", Title".$album->title.", Description".$album->description." Image ID - ".$album->img_id."Img File - ".$album->img_file;
 }

Hope this will be helpful for you.

edited Dec 20, 2013 at 9:41

answered Dec 20, 2013 at 9:22

Bon Boon

11 bronze badge

2 Comments

sas Over a year ago

this will increase time and space complexity. because multiple query will run where as you can do it by single query :)

ltdev Over a year ago

I'm trying to follow CI's practices so album_model matches the albums table, and image_model matches the images table

Collectives™ on Stack Overflow

CodeIgniter: Displaying data from two tables in a view - use one loop?

3 Answers 3

18 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

18 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related