Dynamic update with variable check $_POST

Question

I have a problem. I am mounting a dynamic update, but I am not able to mount in the query the part of parameters to be updated. Follow the code below to be clear what I need:

function UpdateCommand($table, $params, $id){
        $database = new Database();
        $database->database_connect();

        $select = "SELECT * FROM {$table}";

        $resultselect = mysqli_query($database->database_connect(), $select);
        // Aqui não consigo dar sequência

        $query = "UPDATE {$table} SET {$params} WHERE id_{$table} = $id";
        echo $query;
        //$result = mysqli_query($database->database_connect(), $query);

        $database->database_close_connection(); 
    }

This update would be for any table, which would be informed in the parameter. The same would happen with the id. The problem was in the part of the fields to be updated. And two things made me difficult in this.

Each table can have a number of different columns, so I thought I’d mount a select to take these columns and then use them with the variables informed by the form, but I couldn’t do that.

2nd - Even if I correctly extract the columns of the table, how would I give a SET in them for each column? It got a little confusing for me.

I forgot to put that I would need to do a check of the $_POST variables that were set. But this item here is less relevant, what matters most is the update.

Answer 1

Getting the column name of a table

$result = mysqli_query("SHOW COLUMNS FROM nome_da_tabela");
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = $row;
}
print_r($data);

Set the values

From now on you need to adopt some standard for data to be compatible with columns.

Let’s assume the data is in a simple array.

$dados = array('valor 1', 'valor 2', 'valor 3');

For ease, column names should also be in a simple array and exactly at the same position as their respective values

$colunas = array('coluna_do_valor_1', 'coluna_do_valor_2', 'coluna_do_valor_3');

Then simply iterate both arrays to mount the SQL query:

foreach ($colunas as $k => $v) {
    $params[] = $v." = '".$dados[$k]."'";
}
$params = implode(', ', $params);

$query = "UPDATE {$table} SET {$params} WHERE id_{$table} = $id";

Note that you should take some basic precautions with this logic presented in the question because you need to have a default for auto increment fields, usually the table id. As it is auto increment, it cannot be set in update.

_{I am against what you intend to do. However, I abstain from the opinion and chose the most objective form.}