How to reduce this PHP/MYSQL function by adding data in multiple columns?

Question

Hello, I have a private variable $group_permissions which stores all columns of permissions, they columns have the value false or true, to tell if the user has permission to run some on the panel.

The user marks several checkboxes, and sends them by an array variable called $p_permissions , after it arrives at the function, through an in_array is checked if the value came, if it comes is transforming the variavéll with column name = true.

I’d like to reduce that function, but how can I?

public function add($id_subscribers, $p_name, $p_description, $p_permissions = ''){

// VARIAVEIS ACCOUNTS, PRODUCTS, SALES, PURCHASES, FINANCIAL, SETTINGS
$accounts_view = 'false'; $accounts_add = 'false'; $accounts_edit = 'false'; $accounts_delete = 'false';
$products_view = 'false'; $products_add = 'false'; $products_edit = 'false'; $products_delete = 'false';
$sales_view = 'false'; $sales_add = 'false'; $sales_edit = 'false'; $sales_delete = 'false';
$purchases_view = 'false'; $purchases_add = 'false'; $purchases_edit = 'false'; $purchases_delete = 'false';
$financial_view = 'false'; $financial_add = 'false'; $financial_edit = 'false'; $financial_delete = 'false';
$settings_view = 'false'; $settings_add = 'false'; $settings_edit = 'false'; $settings_delete = 'false';

// CASO ESTEJA NA ARRAY - Accounts
if(in_array("accounts_view", $p_permissions)){$accounts_view = 'true';}
if(in_array("accounts_add", $p_permissions)){$accounts_add = 'true';}
if(in_array("accounts_edit", $p_permissions)){$accounts_edit = 'true';}
if(in_array("accounts_delete", $p_permissions)){$accounts_delete = 'true';}
// CASO ESTEJA NA ARRAY - Products
if(in_array("products_view", $p_permissions)){$products_view = 'true';}
if(in_array("products_add", $p_permissions)){$products_add = 'true';}
if(in_array("products_edit", $p_permissions)){$products_edit = 'true';}
if(in_array("products_delete", $p_permissions)){$products_delete = 'true';}
// CASO ESTEJA NA ARRAY - Sales
if(in_array("sales_view", $p_permissions)){$sales_view = 'true';}
if(in_array("sales_add", $p_permissions)){$sales_add = 'true';}
if(in_array("sales_edit", $p_permissions)){$sales_edit = 'true';}
if(in_array("sales_delete", $p_permissions)){$sales_delete = 'true';}
// CASO ESTEJA NA ARRAY - Purchases
if(in_array("purchases_view", $p_permissions)){$purchases_view = 'true';}
if(in_array("purchases_add", $p_permissions)){$purchases_add = 'true';}
if(in_array("purchases_edit", $p_permissions)){$purchases_edit = 'true';}
if(in_array("purchases_delete", $p_permissions)){$purchases_delete = 'true';}
// CASO ESTEJA NA ARRAY - Financial
if(in_array("financial_view", $p_permissions)){$financial_view = 'true';}
if(in_array("financial_add", $p_permissions)){$financial_add = 'true';}
if(in_array("financial_edit", $p_permissions)){$financial_edit = 'true';}
if(in_array("financial_delete", $p_permissions)){$financial_delete = 'true';}
// CASO ESTEJA NA ARRAY - Settings
if(in_array("settings_view", $p_permissions)){$settings_view = 'true';}
if(in_array("settings_add", $p_permissions)){$settings_add = 'true';}
if(in_array("settings_edit", $p_permissions)){$settings_edit = 'true';}
if(in_array("settings_delete", $p_permissions)){$settings_delete = 'true';}

// INSERT no banco de dados
$sql = $this->db->prepare("INSERT INTO cms_accounts_acess (id_subscribers, name, description, accounts_view, accounts_add, accounts_edit, accounts_delete, products_view, products_add, products_edit, products_delete, sales_view, sales_add, sales_edit, sales_delete, purchases_view, purchases_add, purchases_edit, purchases_delete, financial_view, financial_add, financial_edit, financial_delete, settings_view, settings_add, settings_edit, settings_delete) VALUES (:id_subscribers, :p_name, :p_description, :accounts_view, :accounts_add, :accounts_edit, :accounts_delete, :products_view, :products_add, :products_edit, :products_delete, :sales_view, :sales_add, :sales_edit, :sales_delete, :purchases_view, :purchases_add, :purchases_edit, :purchases_delete, :financial_view, :financial_add, :financial_edit, :financial_delete, :settings_view, :settings_add, :settings_edit, :settings_delete)");

// INSERT no banco de dados
/*$sql = $this->db->prepare("INSERT INTO cms_accounts_acess SET id_subscribers = :id_subscribers, name = :name, description = :description, accounts_view = :accounts_view, accounts_add = :accounts_add, accounts_edit = :accounts_edit, accounts_delete = :accounts_delete, products_view = :products_view, products_add = :products_add, products_edit = :products_edit, products_delete = :products_delete, sales_view = :sales_view, sales_add = :sales_add, sales_edit = :sales_edit, sales_delete = :sales_delete, purchases_view = :purchases_view, purchases_add = :purchases_add, purchases_edit = :purchases_edit, purchases_delete = :purchases_delete, financial_view = :financial_view, financial_add = :financial_add, financial_edit = :financial_edit, financial_delete = :financial_delete, settings_view = :settings_view, settings_add = :settings_add, settings_edit = :settings_edit, settings_delete = :settings_delete");*/





$sql->bindValue(":id_subscribers", $id_subscribers);
$sql->bindValue(":p_name", $p_name);
$sql->bindValue(":p_description", $p_description);
$sql->bindValue(":accounts_view", $accounts_view);
$sql->bindValue(":accounts_add", $accounts_add);
$sql->bindValue(":accounts_edit", $accounts_edit);
$sql->bindValue(":accounts_delete", $accounts_delete);
$sql->bindValue(":products_view", $products_view);
$sql->bindValue(":products_add", $products_add);
$sql->bindValue(":products_edit", $products_edit);
$sql->bindValue(":products_delete", $products_delete);
$sql->bindValue(":sales_view", $sales_view);
$sql->bindValue(":sales_add", $sales_add);
$sql->bindValue(":sales_edit", $sales_edit);
$sql->bindValue(":sales_delete", $sales_delete);
$sql->bindValue(":purchases_view", $purchases_view);
$sql->bindValue(":purchases_add", $purchases_add);
$sql->bindValue(":purchases_edit", $purchases_edit);
$sql->bindValue(":purchases_delete", $purchases_delete);
$sql->bindValue(":financial_view", $financial_view);
$sql->bindValue(":financial_add", $financial_add);
$sql->bindValue(":financial_edit", $financial_edit);
$sql->bindValue(":financial_delete", $financial_delete);
$sql->bindValue(":settings_view", $settings_view);
$sql->bindValue(":settings_add", $settings_add);
$sql->bindValue(":settings_edit", $settings_edit);
$sql->bindValue(":settings_delete", $settings_delete);
$sql->execute();    
}

Answer 1

Use arrays

$permissions = ['accounts', 'products', 'sales', 'purchases', 'financial', 'settings'];
$modifiers = ['view', 'add', 'edit', 'delete'];

foreach ($permissions as $permission) {
    foreach ($modifiers as $modifier) { 
        $set_permissions[$permission.'_'.$modifier] = in_array($permission.'_'.$modifier, $p_permissions) ? 'true' : 'false';
    }
}

$indexes = array_keys($set_permissions);

$sql = $this->db->prepare('INSERT INTO cms_accounts_acess (id_subscribers, name, description, '.implode(', ', $indexes).') VALUES (:id_subscribers, :p_name, :p_description, :'.implode(', :', $indexes).')');
$sql->bindValue(':id_subscribers', $id_subscribers);
$sql->bindValue(':p_name', $p_name);
$sql->bindValue(':p_description', $p_description);

foreach ($set_permissions as $key => $value) {
    $sql->bindValue(':'.$key, $value);
}

$sql->execute();