Get a single value by using foreach loop in codeigniter

Question

tbl_products:

+------------+--------------+-------------+------------+
| product_id | product_name | category_id |company_name|
+------------------------------------------------------+
| 1          | iPhone       |      3      |  Apple     |
| 2          | galaxy s1    |      3      |  Samsung   |
| 3          | galaxy s2    |      3      |  Samsung   | 
| 4          | tab          |      4      |  Apple     |
+------------------------------------------------------+

From the above table, i want to get the company name according to the category_id. I have used foreach to get the value. I got Samsung 2 times and Apple once. But i want Samsung and Apple both will show only once. Is it possible? i wrote the bellow code in my model.

    public function select_company_by_category_id($category_id) {
    $this->db->select('*');
    $this->db->from('tbl_products');
    $this->db->where('category_id', $category_id);
    $query_result = $this->db->get();
    $result = $query_result->result();
    return $result;
}

How exactly do you want your $result to look like? I mean, what do you expect to see there? — raina77ow
– raina77ow, Commented Feb 1, 2016 at 14:26

Gouda Elalfy · Accepted Answer · 2016-02-01 14:27:25Z

1

use group_by, and select only company_name :

public function select_company_by_category_id($category_id) {
    $this->db->select('company_name');
    $this->db->from('tbl_products');
    $this->db->where('category_id', $category_id);
    $this->db->group_by('company_name');
    $query_result = $this->db->get();
    $result = $query_result->result();
    return $result;
}

answered Feb 1, 2016 at 14:27

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

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

Comments

Abdulla Nilam · Accepted Answer · 2016-02-01 14:29:05Z

1

Use group_by

$this->db->group_by('company_name');

answered Feb 1, 2016 at 14:29

Abdulla Nilam

39.8k18 gold badges85 silver badges115 bronze badges

Comments

S. Imp · Accepted Answer · 2016-12-15 01:26:51Z

It should be apparent that you have more than one record for certain category_ids. You may have 0, 1, or numerous records in practice. That being the case, you would need to either a) report "No company name" if no records were found, b) loop through the records that were found and concatenate them, or c) use a fancier query and a group_concat function to tell SQL to get you all the names. If I'm not mistaken, I think the two prior answers here are incorrect. I believe that just using group_by will still just get you one company_name.

MySQL offers a GROUP_CONCAT function you can use in conjunction with a GROUP BY clause but you might be using some other DBMS. I think Postgresql has array_agg or something like that.

Also, if you use $query_result->result() then you will get an array of objects. If you just use $query_result->row() then you should get a single object instead of an array of objects

At any rate, I tried something like this which seems to work.

public function select_company_by_category_id($category_id) {
    $this->db->select("GROUP_CONCAT(company_name) AS names");
    $this->db->from('tbl_products');
    $this->db->where('category_id', $category_id);
    $this->db->group_by('category_id');
    $query_result = $this->db->get();
    $result = $query_result->row();
    return $result
}

Collectives™ on Stack Overflow

Get a single value by using foreach loop in codeigniter

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related