在数据库中更新表行的快捷方式? [英] A short-cut to update a table row in the database?

问题描述

我想知道这种想法是否是更新数据库表中行的一种好习惯.

I wonder whether this idea is a good practice to update a row in a table in the database.

我通常这样更新行，

$pg_id = set_variable($_POST,'pg_id');
$pg_url = set_variable($_POST,'pg_url');
$pg_title = set_variable($_POST,'pg_title');
$pg_subtitle = set_variable($_POST,'pg_subtitle');
$pg_description = set_variable($_POST,'pg_description');
$pg_introduction = set_variable($_POST,'pg_introduction');
$pg_content_1 = set_variable($_POST,'pg_content_1');
$pg_content_2 = set_variable($_POST,'pg_content_2');
$pg_content_3 = set_variable($_POST,'pg_content_3');
$pg_content_4 = set_variable($_POST,'pg_content_4');
$pg_backdate = set_variable($_POST,'pg_backdate');
$pg_tag = set_variable($_POST,'pg_tag');
$pg_user = set_variable($_POST,'pg_user');
$pg_member = set_variable($_POST,'pg_member');
$pg_highlight = set_variable($_POST,'pg_highlight');
$pg_hide = set_variable($_POST,'pg_hide');
$pg_cat_id = set_variable($_POST,'pg_cat_id');
$ps_cat_id = set_variable($_POST,'ps_cat_id'); 
$parent_id = set_variable($_POST,'parent_id');
$tmp_id = set_variable($_POST,'tmp_id');
$usr_id = set_variable($_POST,'usr_id');

$sql = "
UPDATE root_pages
SET 
    pg_url = ?, 
    pg_title = ?,
    pg_subtitle = ?,
    pg_backdate = ?,
    pg_description = ?,
    pg_introduction = ?,        
    pg_content_1 = ?,
    pg_content_2 = ?,
    pg_content_3 = ?,
    pg_content_4 = ?,
    pg_highlight = ?,
    pg_hide = ?,
    ps_cat_id = ?,  
    parent_id = ?, 
    tmp_id = ?,
    updated_by = ?
WHERE pg_id = ?
";

# use the instantiated db connection object from the init.php, to process the query
$result = $connection->run_query($sql,array(
    $pg_url, 
    $pg_title,
    $pg_subtitle,
    $pg_backdate,
    $pg_description,
    $pg_introduction,
    $pg_content_1,
    $pg_content_2,
    $pg_content_3,
    $pg_content_4,
    $pg_highlight,
    $pg_hide,
    $ps_cat_id, 
    $parent_id, 
    $tmp_id,
    $usr_id,
    $pg_id
    ));

在进行维护时，我发现要花很长时间键入以列出表中的所有字段，所以我有一个捷径可以解决它，

I find this is taking a long time to type to list all the fields in the table when comes to maintenance, so I have this short-cut idea to get around it,

# queury the table columns.
$sql = "
SHOW COLUMNS 
FROM root_pages
";

# use the stored connection object from the class_page_controller.php, to process the query.
$columns = $connection->fetch_all($sql);

# loop through the table columns, select the 'Field' column only, turn the field into variables, then get the variable's value from the array.
foreach($columns as $column)
{
    $$column['Field'] = set_variable($_POST,$column['Field']);
}

foreach($columns as $column)
{

    $sql = "
    UPDATE root_pages
    SET 
        ".$column['Field']." = ?
    WHERE pg_id = ?
    ";

    # use the instantiated db connection object from the init.php, to process the query
    $result = $connection->run_query($sql,array($$column['Field'],$pg_id));

}

它更短，但是我在此快捷方式中使用了很多循环-不好吗?

It is shorter but I use loads of looping in this short-cut - is it bad?

这是否会使服务器处理更新速度变慢?我看不到这种方法会遇到什么问题?

Does this make the server slow in processing the update? What problems would I get in this method that I fail to see?

推荐答案

更多查询意味着更多时间，因此，如果要单独更新每个字段(而不是一次更新)，则会花费更长的时间.

More queries means more time, so if you are updating each field individually (rather than a row at a time), it will take significantly longer.

此外，您可能希望对提交的值应用过滤器，以确保您不希望更新的任何字段都可以.

Also, you may want to apply a filter to the submitted values to ensure that no field which you do not want updated can be.

例如，如果您有一个用户表，其中列出了针对他们的帐户余额:

For instance, if you had a table of users who had account balances listed against them:

id | user       | credit
==========================
1  | John Smith | 50

如果我可以向您的表单处理程序提交表单，因为字段将显示在SHOW COLUMNS...查询中，那么我可以通过旨在用于更改我的表单的表单向您发送POST提交名称，用$_POST['user'] = "Mike Rowe" 和 $_POST['credit'] = 9999，然后将上面的内容更改为:

If I could submit a form to your form handler, as the "credit" field would show up in the SHOW COLUMNS... query, I could send you a POST submission, through a form intended to be used for me to change my name, with $_POST['user'] = "Mike Rowe" and $_POST['credit'] = 9999, and you would change the above to:

id | user       | credit
==========================
1  | Mike Rowe  | 9999

更新:建议的解决方案

与其相信数据库字段名可以安全地用于处理这样的查询，还不如不拥有自己的可编辑字段数组，而直接遍历它们呢?

Rather than trust that the database field names are safe to use for handling a query like this, why not have your own array of editable fields and just loop through them?

$editable_fields = array(
  'pg_url' ,
  'pg_title' ,
  ...
);

$form_values = array();
$sql_pattern = array();
foreach( $editable_fields as $k ){
  if( $k != 'pg_id'
      && isset( $_POST[$k] ) ){
    $form_values[$k] = $_POST[$k];
    // NOTE: You could use a variant on your above code here, like so
    // $form_values[$k] = set_variable( $_POST , $k );
    $sql_pattern[] = "$k = ?";
  }
}

$sql_pattern = 'UPDATE root_pages SET '.implode( ' , ' , $sql_pattern ).' WHERE pg_id = ?';

# use the instantiated db connection object from the init.php, to process the query
$result = $connection->run_query($sql_pattern,array_merge(
    $form_values ,
    $_POST['pg_id']
    ));

注意:此代码未经测试，并且不是我通常的操作方式，因此请使用它作为指南，而不是圣经...

NOTE: This code is untested and not the way I usually operate, so use it as a guide, not a bible...

这篇关于在数据库中更新表行的快捷方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！