Develop picture album using codeigniter, query to select images?

91
April 30, 2018, at 01:28 AM

I am developing a web application using codeigniter.In there i am making a part to show images like in facebook as this screenshot.

For that i have created two database table.One to keep album names with user id.One for to keep images names with album id.Here i am providing the screenshot of two table.

I have lot of albums in the album table for one user.now i have entered only one data to this table just for test.how can i select only one image from one album to create album view like in Facebook wish i have shown in the top of this question as screenshot.I create a query like this in the model to select image.

$username=$this->session->userdata('username');
         $this->db->select('id');
         $this->db->where('email',$username);
         $query=$this->db->get('user');
         foreach ($query->result() as $row)
         {
             $user_id= $row->id;
         }

         $this->db->select('*');
         $this->db->where('album_images.user_id',$user_id);
         $this->db->from('album_images');
         $this->db->join('album', 'album.id = album_images.album_id');
         $query = $this->db->get();
         return $query->result();

How can improve this code to select only one image from each album and pass all the details of that image such as image id, album_id,album_name,image_name to the view?

Answer 1

Use row() instead of result() when you want to get/expect to get just 1 row:

$username = $this->session->userdata('username');
$this->db->select('id');
$this->db->where('email', $username);
$query = $this->db->get_where('user', array('email' => $username));
if ($query->num_rows() !== 1) {
    return null;
}
$this->db->select('*');
$this->db->where('album_images.user_id', $query->row()->id);
$this->db->from('album_images');
$this->db->join('album', 'album.id = album_images.album_id');
$query = $this->db->get();
if ($query->num_rows() < 1) {
    return null;
}
return $query->row();
Answer 2

try this

$this->db->select('uai.album_id, ua.album_name, uai.id as image_id, uai.image_name');
$this->db->from('user as u');
$this->db->join('album as ua', 'ua.user_id = u.id', 'left');
$this->db->join('album_images as uai', 'uai.user_id = u.id AND uai.album_id = ua.id', 'left');
$this->db->where('u.email',$this->session->userdata('username'));
$this->db->group_by('uai.user_id, uai.album_id');
$this->db->order_by('ua.album_name ASC');
$query = $this->db->get();
return ($query->num_rows() > 0) ? $query->result() : false;

Output will like following

Array
(
    [0] => stdClass Object
        (
            [album_id] => 1
            [album_name] => Apple
            [image_id] => 1
            [image_name] => Apple1.jpg
        )
    [1] => stdClass Object
        (
            [album_id] => 2
            [album_name] => Orange
            [image_id] => 3
            [image_name] => Orange1.jpg
        )
)

image_name will return one image from album_images table

ex. `[image_name] => Apple1.jpg` from (Apple1.jpg,Apple2.jpg,Apple3.jpg ..etc)
READ ALSO
SQL dropping selected row from table

SQL dropping selected row from table

I am new to SQL and do not understand how to drop rows from a tableI have this:

46
Sort comments by likes and dislikes

Sort comments by likes and dislikes

Help, please, correct the query, I need to sort comments by likes and dislikes (that is, by the sum of likes and dislays: cnt_total = cnt_like + cnt_dislike, who has more cnt_total is displayed above), and if cnt_total is equal - sort by datecnt_total...

81
One to Many Count with one query?

One to Many Count with one query?

I haven't touched the backend in a whileso forgive me if this is super simple

66
Java printing database records on separate lines

Java printing database records on separate lines

I have a JDBC program that takes records from a MySQL database and prints out the resultsThe user can select which results they want from the database by selecting different checkboxes to only display certain results

58