CodeIgniter Forums
Mysql Queries - Best Tips - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Mysql Queries - Best Tips (/thread-9230.html)



Mysql Queries - Best Tips - El Forum - 06-17-2008

[eluser]cristian_c[/eluser]
So I have multiple tables that I need to pull the data from. This is how I have been doing it but I not confident that this is the best way to do this. here is some code

Code:
function getBlogs()
    {
        $query = $this->db->get('blogs');
        $result = $query->result_array();
        foreach($result as $i => $post){
            $result[$i]['blogger'] = $this->getBlogger($post['author_id']);
            $result[$i]['categories'] = $this->getCategory($post['cat_id']);
        }
        return $result;
    }
    function getBlogger($author_id)
    {
         $this->db->where('id',$id);
         $query = $this->db->get('accounts');
         return $query->row_array();
    }
    function getCategory($cat_id)
    {
         $this->db->where('post_id',$cat_id);
         $query = $this->db->get('posts_categories');
         return $query->result_array();
    }



Mysql Queries - Best Tips - El Forum - 06-17-2008

[eluser]Armchair Samurai[/eluser]
Assuming there is a 1-to-1 relationship between the tables, just join them rather than making three separate queries.
Code:
$this->db->join('accounts y', 'x.author_id = y.id');
$this->db->join('posts_categories z', 'x.cat_id = z.post_id');
$query = $this->db->get('blogs x');
return $query->result_array();