0

I want to select id from 2 different tables and use the id which is present in both the tables.

seller

id | name | mobile_number | password | is_active    
1  | abc  | 987654321     | 12345678 |  0
2  | pqr  | 989898989     | 12345678 |  1
3  | lmn  | 919191991     | 12345678 |  1

where, 0 is not active and 1 is active.

tanker

id | seller_id | capacity    
1  | 1         | 14
2  | 2         | 7
3  | 2         | 3.5
4  | 3         | 3.5

where, seller_id is foreign key.

Now, I want to select all seller whose status is active i.e., is_active = 1 and whose capacity = 3.5

Here is my code.


        $data = $this->db->select('id')
                 ->from('seller')
                 ->where('is_active', 1)
                 ->get()
                 ->result();

        return $data;

    }```

```public function check_capacity($id,$capacity){

        $data=$this->db->select('seller_id',$idd)
                 ->from('tanker')
                 ->where('capacity', $capacity)
                 ->get()
                 ->result();

        return $data;
    }```

Expected Output : 

*Array
(
[0] => stdClass Object
(
[id] => 2
)

[1] => stdClass Object
(
[id] => 3
)

)*
Usama Bagwan
  • 21
  • 1
  • 6
  • `$idd` looks like a Typo. There will be many duplicates that demonstrate how to join two tables in CodeIgniter. https://stackoverflow.com/q/40640049/2943403 – mickmackusa Jun 09 '21 at 15:24

1 Answers1

1

You have to join these two tables together:

$this->db->select('*, seller.id as seller_id, tanker.id as tanker_id')
    ->from('seller')
    ->join('tanker', 'seller.id = tanker.seller_id')
    ->where('is_active', 1)
    ->where('capacity', 3.5)
    ->get()
    ->result();

Once you have this, you can play around with the parameter (e.g. choose a dynamic capacity). If you have troubles, note that you can always print the last query using:

print $this->db->last_query();
D B
  • 534
  • 3
  • 14
  • Why select everything (`*`) then select two additional columns (again)? – mickmackusa Jun 09 '21 at 15:23
  • @mickmackusa I choose to select everything, so that the preson asking the question can see what is happening. Since CI will only return the second id column (and will overwrite the first one), these are also selected. But now that you ask this question, these fields need an alias. – D B Jun 10 '21 at 07:38
  • I reckon you are safe to use `select("seller.*")` because the OP has shown nothing valuable in the joined table -- the tanker table is only being used for filtering purposes. – mickmackusa Jun 10 '21 at 07:50
  • Thanks for the answer can you explain what you did there in simple words? – Usama Bagwan Jun 10 '21 at 10:27
  • A join puts together all rows of two or more tables based on conditions. In this case the column `seller_id` 1 indicates, that this row belongs to `id` 1 of the seller table. If you omit the where clauses and print the result of the query, you will see that all four rows of the tanker tables were matched to the seller table rows. Your can work with this construct as if the tables were not separated in the first place. You can find a visiual representation [here](https://stackoverflow.com/questions/5706437/) – D B Jun 11 '21 at 07:33