select only matching value in sql with codeigniter

Question

I have 6 more table in mysql and every field has a unique field 'works_id', I input data these table. I want to only matching value it shows but it takes 16 rows. Suppose in worker name field it shows two name with repeated 8 times but i want two name only once. It may take two rows. But how its possible. My Table Show:

Ser Works ID Date Of Works  Infrastructure  Name of System  Type of Works       Vendor Name     Worker Name     
1   1016    2017-03-12      Server          JoinBDApps      Trobleshooting      Vintage IT      Md Rajaon
2   1016    2017-03-12      Server          JoinBDApps      Software Upgrade    Vintage IT      Md Rajaon
3   1016    2017-03-12      Network         JoinBDApps      Trobleshooting      Vintage IT      Md Rajaon
4   1016    2017-03-12      Network         JoinBDApps      Software Upgrade    Vintage IT      Md Rajaon
5   1016    2017-03-12      Server          CMH             Trobleshooting      Vintage IT      Md Rajaon
6   1016    2017-03-12      Server          CMH             Software Upgrade    Vintage IT      Md Rajaon
7   1016    2017-03-12      Network         CMH             Trobleshooting      Vintage IT      Md Rajaon
8   1016    2017-03-12      Network         CMH             Software Upgrade    Vintage IT      Md Rajaon
9   1016    2017-03-12      Server          JoinBDApps      Trobleshooting      GP IT           Md Forkan
10  1016    2017-03-12      Server          JoinBDApps      Software Upgrade    GP IT           Md Forkan
11  1016    2017-03-12      Network         JoinBDApps      Trobleshooting      GP IT           Md Forkan
12  1016    2017-03-12      Network         JoinBDApps      Software Upgrade    GP IT           Md Forkan
13  1016    2017-03-12      Server          CMH             Trobleshooting      GP IT           Md Forkan
14  1016    2017-03-12      Server          CMH             Software Upgrade    GP IT           Md Forkan
15  1016    2017-03-12      Network         CMH             Trobleshooting      GP IT           Md Forkan
16  1016    2017-03-12      Network         CMH             Software Upgrade    GP IT           Md Forkan

But i Want:

Ser Works ID Date Of Works  Infrastructure  Name of System  Type of Works   Vendor Name     Worker Name     
1   1016    2017-03-12      Server          JoinBDApps      Trobleshooting  Vintage IT      Md Rajaon
                            Network         CMH             Software Upgrade GP IT          Md Forkan

My Codeigniter Model is:

$this->db->select('trxn_tbl.works_id, trxn_tbl.works_date, infrashtructure_txn_info.infrashtructure_name, apps_txn_tbl.apps_name, works_type_txn_tbl.works_type, workers_tbl.vendor_id, workers_tbl.name');
        $this->db->from('infrashtructure_txn_info');
        $this->db->join('workers_tbl', 'trxn_tbl.works_id = workers_tbl.works_id');
        $this->db->join('works_type_txn_tbl', 'trxn_tbl.works_id = works_type_txn_tbl.works_id');
        $this->db->join('infrashtructure_txn_info', 'trxn_tbl.works_id = infrashtructure_txn_info.works_id');
        $this->db->join('apps_txn_tbl', 'trxn_tbl.works_id = apps_txn_tbl.works_id');
        $query = $this->db->get();

        return $query->result();

Please anybody help me...

Michael Krikorev · Answer 1 · 2017-03-13T00:41:08.033

You should use GROUP BY to aggregate the rows:

$this->db->group_by(array('trxn_tbl.works_id', 'infrashtructure_txn_info.infrashtructure_name'));

Try if this works:

$this->db->select('trxn_tbl.works_id, trxn_tbl.works_date, infrashtructure_txn_info.infrashtructure_name, apps_txn_tbl.apps_name, works_type_txn_tbl.works_type, workers_tbl.vendor_id, workers_tbl.name');
$this->db->from('infrashtructure_txn_info');
$this->db->join('workers_tbl', 'trxn_tbl.works_id = workers_tbl.works_id');
$this->db->join('works_type_txn_tbl', 'trxn_tbl.works_id = works_type_txn_tbl.works_id');
$this->db->join('infrashtructure_txn_info', 'trxn_tbl.works_id = infrashtructure_txn_info.works_id');
$this->db->join('apps_txn_tbl', 'trxn_tbl.works_id = apps_txn_tbl.works_id');

$this->db->group_by(array('trxn_tbl.works_id', 'infrashtructure_txn_info.infrashtructure_name'));

$query = $this->db->get();

return $query->result();

EDIT: If you get any group_by related errors, try to group by all named columns in your select. Another alternative is to try using GROUP_CONCAT for all columns not included in the group_by clause. For example: GROUP_CONCAT(workers_tbl.name SEPARATOR ",") as workers_tbl.name etc.

score 0 · Answer 2 · answered Mar 12 '17 at 14:31

0

Put the distinct() in the top of your code like this:

$this->db->distinct();
$this->db->select('trxn_tbl.works_id, trxn_tbl.works_date, infrashtructure_txn_info.infrashtructure_name, apps_txn_tbl.apps_name, works_type_txn_tbl.works_type, workers_tbl.vendor_id, workers_tbl.name');
...rest of your code...

Does that help ?

answered Mar 12 '17 at 14:31

Rabin Lama Dong

2,422
1
27
33

add `$this->db->group_by('name_of_column');` after `$this->db->distinct()` Now ? – Rabin Lama Dong Mar 12 '17 at 15:09
Then Shows......Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dcapp_db.trxn_tbl.works_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Forkan Akon Mar 12 '17 at 15:19
It seems the error is same as in this thread http://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Rabin Lama Dong Mar 12 '17 at 15:26

select only matching value in sql with codeigniter

2 Answers2