Show value of one table in other table (Codeigniter 4)

Question

I have two tables:

table1:

ID	Name	Email	profile_id

table2:

id	profile_name

I display table to the user on the website in the view using a foreach loop, but in that view I want the profile_id to display the corresponding profile_name from table2 and not the id.

I think I'm supposed to use join(), but I have no clue how to use this. I can provide my code if neccesairy but I don't think it is.

Any help?

Answer 1

Before looking up the codeigniter 4 way of creating queries, take a moment to understand the basic SQL behind joins. Joins allow you to combine results from multiple tables. You need to be specific about which table you want which value from. In basic SQL it looks like

SELECT table1.something, table2.somethingElse 
FROM table1
JOIN table2
ON table1.id = table2.referenceID

The key is the ON keyword. It establishes the link between the 2 tables. Sometimes you will have the same field names in your result, like ID, so you can change those in your query using AS like

SELECT  
   *, table1.id AS table1_id, 
   table2.id AS table2_id 
FROM table1
JOIN table2
ON table1.id= table2.referenceID

This query will still get the id column from both databases, but will also get the new ad-hoc fields so we can differentiate in the result.

In codeigniter 4 you just use their method

$builder = $db->table('table1');
$builder->select('table1.*, table2.profile_name');
$builder->join('table2', 'table1.profile_id = table2.id');
$query = $builder->get();
foreach ($query->getResult() as $row)
{
    $users[]= $row;
}
echo view('myPage', array('users' => $users) );

This is the most basic rudimentary intro to joins. I encourage you to learn more about them