CODEWITHSUNDEEP
phpmysqlsqlcodeigniterjoin
user504363
user504363

Reputation: 551

how get unique rows using join from one table?

I am using codeigniter 2 in my project and I have a problem that I hope that you'll help me in solving it.

I have two mysql tables,"authors" and "articles" where relation is as "one author to many articles". I need to get authors "from 'authors'" that have already active articles in "articles".

note that in articles table, I have column called "status_id" that set to 1 if the article is active or 0 if suspended. and I have in "authors" table a record called "authors_categories_id".

I tried to do it with this code

$this->db->select('authors.name,authors.about,authors.author_thumbnail,authors.authors_categories_id,authors.user_id,authors.status_id,authors.id AS auth_id')
                  ->from('authors')
                  ->join('articles', 'authors.id = articles.author_id')
                  ->where('authors.authors_categories_id',$AuthCategory)
                  ->where('articles.status_id', 1)
                  ->order_by("auth_id", "desc")
                  ->limit($limit, $start);
            $Res = $this->db->get();
            $data['Authors'] = array();
            foreach($Res->result() as $R)
            {
                $data['Authors'][] = $R;
            }
            print_r($data['Authors']);

My problem that the code results me duplicated authors. for example, if an author have 5 articles in 'articles' table, the result will be 5 records for the same author while i need to view each author just once not more.

I need the code to generate records according to count of authors not articles.

thanks.

Upvotes: 1

Views: 331

Answers (2)

John Woo
John Woo

Reputation: 263693

add DISTINCT in your query.

select('authors.name,authors.about,authors.author_thumbnail,authors.authors_categories_id,authors.user_id,authors.status_id,authors.id AS auth_id')
              ->from('authors')
              ->join('articles', 'authors.id = articles.author_id')
              ->where('authors.authors_categories_id',$AuthCategory)
              ->where('articles.status_id', 1)
              ->group_by('authors.name');
              ->order_by("auth_id", "desc")

Upvotes: 4

Nick
Nick

Reputation: 6346

$this->db->select('authors.name,authors.about,authors.author_thumbnail,authors.authors_categories_id,authors.user_id,authors.status_id,authors.id AS auth_id')
                  ->from('authors')
                  ->join('articles', 'authors.id = articles.author_id')
                  ->where('authors.authors_categories_id',$AuthCategory)
                  ->where('articles.status_id', 1)
                  ->group_by('authors.id')
                  ->order_by("auth_id", "desc")
                  ->limit($limit, $start);
            $Res = $this->db->get();
            $data['Authors'] = array();
            foreach($Res->result() as $R)
            {
                $data['Authors'][] = $R;
            }
            print_r($data['Authors']);

Note the ->group_by('authors.id') in the code now. This should group all of the same authors together.

Upvotes: 1

Related Questions