Dynamic Update with variable verification $ _POST

Question

Dynamic Update with variable verification $ _POST

Navigation

#1 by (0 votes)

-1

I have a problem. I'm setting up a dynamic update, but I'm not able to mount the query on the parameters to update in the query. 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 serve 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 it difficult for me.

1st - Each table can have a number of different columns, so I thought of putting a select to pick up those columns and then using them with the variables reported by the form, but I could not do that.

2nd - Even if I extracted the columns correctly from the table, how would I give%% of them to each column? That got a bit 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.

php

asked by anonymous 15.08.2016 / 02:58

1 answer

Messages do not appear on the screen using Angular undefined index array pdo

score 0 · Answer 1

Getting the name of the columns of a table

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

Set values

From now on you need to adopt some pattern so that the data is compatible with the columns.

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

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

To make it easier, the column names must also be in a simple array and in exactly the same position as their respective values

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

Then just 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 have some basic care with this logic presented in the question since you need to have a default for fields of type auto increment, usually the id of the table. As it is auto increment, it can not be set in the update.

I am against what you intend to do. However, I refrain from thinking and opted for the most objective way.