How to fetch multiple values from the table based on foreign key?

Question

I have two tables,

#table 1 contains the following data structure

NO category_name    category_code   amount   type
1  provident_fund       PF           1000   Deduction
2  Home Allowance       HM           12000  Earning
3  Basic_pay            BP           35000  Earning

#table 2 contains the following data structure

NO group_name         payroll_cat_name 
1  permanent          PF,HM,BP,TX
1  visiting           HM,BP

from the table 2, I can get the value of payroll_cat_name using the query

$a = "Select payroll_cat_name from table_2";

using the above query, I'm getting the value PF,HM,BP,TX.

Now, I have to use this multiple values PF,HM,BP,TX in order to get the sum amount from table 1.

Following is my code that I have tried,

include "../db.php";
$a = "Select payroll_cat_name from table_2";
$b = mysqli_query($con,$a);
$c = mysqli_fetch_array($b);
$d = $c["payroll_cat_name"];
echo "$d";
    $myArray = explode(',', $d);
    
print_r($myArray);
$tr = "select SUM(amount) as am from table_1 where category_code in ($d)";

$rt = mysqli_query($con,$tr);
$new = mysqli_fetch_array($rt);
$gh = $new["am"];

Answer 1

That’s a poor data model. There should be a separate table to store the relation between groups and categories, which each tuple on a different row. The linked commented by sticky bit on the question gives great explanation on how and why.

For your current structure, one option using uses find_in_set() and a subquery:

select t2.*,
    (
        select sum(t1.amount)
        from table_1 t1
        where find_in_set(t1.category_code, t2.payroll_cat_name)
    ) as t1_amount
from table_t2 t2