How to take data from an array and distribute within a method?

Question

I want to create a registration method as follows:

On the products page:

require_once('metodos/metodosClass.php');
$metodos = new metodosClass();
....
// Cadastrar produtos
$tabela = 'produtos';
$camposTB = array('Nome,Descricao,Valor,ValorDesconto');
$valores = array_filter($_POST);
echo $metodos->cadastrarProdutos($tabela,$campoTB,$valores);

But how would I get the values of the variables $peasantsTB and $values and distribute within the method cadastrarProducts()?

public function cadastrarProdutos($tabela, $camposTB, array $valores)
{
   mysqli_query($this->conexao,"INSERT INTO ".$tabela." ("{INCLUIR OS VALORES DA VARIÁVEL $camposTB}")") VALUES("{INCLUIR OS VALORES DA VARIÁVEL $valores}")");

My intention is to create a generic method, regardless of the amount of fields have the query.

Answer 1

I suggest, in that case, you use Prepared statements, both present in the PDO as in the OO interface of Mysqli, to avoid the famous SQL injections, which may indicate the vulnerability of your system, so to speak.

Anyway, here I present a solution using the method you ask for, and another using the method "safer".

Using the functions mysqli_*

public function cadastrarProdutos(string $tabela, array $campos, array $valores) {
    $query = "INSERT INTO $tabela";
    $query .= ' (' . implode(', ', $campos) . ') ';
    $query .= 'VALUES (\n';

    //Aqui adiciona aspas para todos os valores e escapa as que já vieram no valor. Ou seja,
    // se o texto veio como 'Olá, 'mundo'!', transformará em
    // 'Olá \'mundo\'!'
    $valores = array_map(function($item) { return "'".addslashes($item)."'"; }, $valores);
    $query .= implode(', ', $valores);
    $query .= '\n);';

    mysqli_query($this->conexao, $query);
    //...
}

Using Prepared statements with PDO

Having obviously made all PDO settings, the method could look like this:

public function cadastrarProdutos(string $tabela, array $campos, array $valores) {
     $statement = $this->pdoConn->prepare('INSERT INTO ' . $tabela . ' (' . implode(', ', $campos) . ') VALUES (' . implode(', ', str_split( str_repeat('?', count($campos) ) ) ) . ');');
     for ($i=0;$i<count($campos);$i++) {
         $statement->bindValue($i+1, array_values($valores)[$i]);
     }
     $statement->execute();
}

Here I use the for to be able to take each value and its position, since, in logic, the sequence of elements in the array of the values shall be the same as array of the fields (as well as the size of both must be the same), thus using the parameter bindValue to replace the value at the position equivalent to its field. That is, the SQL command at the beginning would look like this:

INSERT INTO produtos (Nome, Descricao, Valor, ValorDesconto) VALUES (?, ?, ?, ?);

And after all the replacements had been made, it would look something like this:

INSERT INTO produtos (Nome, Descricao, Valor, ValorDesconto) VALUES ('<nome>', '<desc>', '<valor>'|<valor>, '<valor_desconto>'|<valor_desconto>);

OBS.: the ratings '<valor>'|<valor> and '<valor_desconto>'|<valor_desconto> indicate that the value to be entered may be a text or a numerical value.

And, in both forms, you could create an "algorithm" to know the type of the value and thus pass from the "correct" form to the SQL command, differentiating between those that are texts and those that are numbers, either through the manual form (the first solution), or by Prepared statements, using the method bindValue, that even has the parameter data_type, which by default accepts all as one string.

Using Prepared statements with mysqli (object-oriented)

Additionally, you can also use the Prepared statements with the object-oriented "interface" of Mysqli, which you can see better in the documentation how to use, and do similarly to PDO:

public function cadastrarProdutos(string $tabela, array $campos, array $valores) {
     $statement = $this->mysqli->prepare('INSERT INTO ' . $tabela . ' (' . implode(', ', $campos) . ') VALUES (' . implode(', ', str_split( str_repeat('?', count($campos) ) ) ) . ');');
     $statement->bind_param("ssss", ...array_values($valores));
     $statement->execute();
     //$statement->close();
}

The question of the meaning of those "ssss", you can also see on documentation, but basically I’m informing you that all the values that are to be put in charge are strings. And after, I use the Operator spread ("scattering" operator, free translation) to pass the values of the array as parameters, but there may be other (better) ways to do. And here comes, once again, the possibility for you to change the values for the correct (data) types, according to your needs.

Completion

Finally, to use the method cadastrarProdutos, whatever the chosen form of the above, would be:

require "...";

$tabela = "produtos";
$campos = array('Nome', 'Descricao', 'Valor', 'ValorDesconto');
$valores = array_filter($_POST);
$metodos->cadastrarProdutos($tabela, $campos, $valores);

I hope I’ve helped!

Answer 2

Ignoring security issues, you could use the implode normally.

$tabela = 'produtos';
$camposTB = ['Nome','Descricao','Valor','ValorDesconto']; // Cada coluna é um item
$valores = array_filter($_POST);

echo $metodos->cadastrarProdutos($tabela,$campoTB,$valores);

So:

public function cadastrarProdutos(string $tabela, array $campos, array $valores)
{
   $campos = '`' . implode($campos, '`,`') . '`';
   $valores = '"' . implode($valores, '","') . '"';
   mysqli_query($this->conexao,"INSERT INTO ".$tabela." (".$campos.") VALUES (".$valores.")");
}