如何使用一个前端从索引数组文本输入名称PHP向多列数据库插入数据？

Question

首先，我希望你能理解我提出的问题，因为我不是英国人。我在做简单的足球联赛。所以我想记录下球队在主队和客队之间的所有进球，包括球员的助攻，以及使用PHP表单的自己的进球。问题是，我想在ONE foreach()中提交它们，而不是使用foreach()来插入目标，foreach()用于插入助攻，而foreach()用于插入自己的目标。

我尝试过使用foreach()，我只知道对于一个索引数组变量或关联数组；

表单输入目标代码、asssists代码和自己的目标代码(来自JSON编码)：

'.... <td><input class="goal-home form-control col-6" name="goal_home['+data.player[i].id_player+']" type="number" value="0"></td>'+
'<td><input class="assist-home form-control col-6" name="assist_home['+data.player[i].id_player+']" type="number" value="0"></td>'+
'<td><input class="owngoal-home form-control col-6" name="owngoal_home['+data.player[i].id_player+']" type="number" value="0"></td>'+ ....

从主队一方获取数据的代码：

$goal_home = $this->input->post('goal_home');
$assist_home = $this->input->post('assist_home');
$owngoal_home = $this->input->post('owngoal_home');

我试着把数据插入到数据库中，如下所示：

 //The code for inserting each player's goal
    foreach ($goal_home as $goal => $val) {
                        $this->m->query("UPDATE tbl_player 
                                        SET goal = (goal + $val)
                                        WHERE id_player = $goal
                                        ");
                    }
    //The code for inserting each player's assist
    foreach ($assist_home as $assist => $val) {
                        $this->m->query("UPDATE tbl_player 
                                        SET assist = (assist + $val)
                                        WHERE id_player = $assist
                                        ");
                    }
    //The code for inserting each player's own goals
    foreach ($owngoal_home as $owngoal => $val) {
                        $this->m->query("UPDATE tbl_player 
                                        SET owngoal = (owngoal + $val)
                                        WHERE id_player = $owngoal
                                        ");
                    }

但我期待这样的事情(我知道这是错误的)：

    foreach (($goal_home as $goal => $val1), ($assist_home as $assist => $val2), ($owngoal_home as $owngoal => $val3)) {

$this->m->query("UPDATE tbl_player 
                      SET goal = (goal + $val1), assist = (assist + $val2) ,owngoal = (owngoal + $val3)
                      WHERE id_player = $goal
                     ");

我知道这是错误的，但我不知道最好的解释方法，我一直在寻找这个相似的问题，但没有人能解决，也许我不明白。谢谢

Answer 1

在所有数组中，都使用相同的索引。因此，一种简单的方法是运行一个数组，并使用索引从每个循环中给定索引的所有数组中获取数据。

$goal_home    = $this->input->post('goal_home');
$assist_home  = $this->input->post('assist_home');
$owngoal_home = $this->input->post('owngoal_home');

foreach ($goal_home as $id => $dummy) {
 $this->m->query("UPDATE
                        tbl_player 
                     SET
                        Goal    = (goal    + $goal_home[$id]),
                        assist  = (assist  + $assist_home[$id])
                        owngoal = (owngoal + $owngoal_home[$id])
                   WHERE
                        id_player = $id");
}

但是，您也可以从

<input name="goal_home['+data.player[i].id_player+']">
<input name="assist_home['+data.player[i].id_player+']">
<input name="owngoal_home['+data.player[i].id_player+']">

至

<input name="player['+data.player[i].id_player+'][goals]">
<input name="player['+data.player[i].id_player+'][assists]">
<input name="player['+data.player[i].id_player+'][owngoals]">

然后，您得到一个包含所有数据的数组，然后您可以通过这样的数据：

$data = $this->input->post('player');

foreach ($data as id => $item) {
 $this->m->query("UPDATE
                        tbl_player 
                     SET
                        Goal    = (goal    + $item['goals']),
                        assist  = (assist  + $item['assists'])
                        owngoal = (owngoal + $item['owngoals'])
                   WHERE
                        id_player = $id");
}

Answer 2

这么多更新。这是非常低效的。我将收集所有数据作为SQL值，使用所有数据创建一个临时表，然后更新主表。

您的输入文件共享相同的索引。好的!收集数据：

# assumeing, that $goal_home, $assist_home and $owngoal_home exist
$keys = array_keys($goal_home);
$values = '';

foreach ( $keys as key )
{
    # assume, that all value are integers.
    # '%d' avoids SQL injection

    $key = intval($key);
    $values .= sprintf(',"%s",%d,%d,%d)',
                 $key, $goal_home[$key],
                 $assist_home[$key], $owngoal_home[$key] );
}
$values = substr($values,1); # delete first comma

现在我们创建一个临时表，该表在关闭数据库连接后自动删除。但首先，我们删除表以允许多次使用。

$this->m->query("DROP TABLE IF EXISTS update_temp");
$query = <<< __EOT__
    CREATE TEMPORAY TABLE update_temp
    (
        id_player int not null primary key,
        goal      int not null,
        assist    int not null,
        owngoal   int not null
    )
__EOT__;
$this->m->query($quote);

现在我们坐满了桌子：

$query = <<< __EOT__
    INSERT INTO update_temp (id_player,goal,assist,owngoal)
    VALUES $values
__EOT__;
$this->m->query($quote);

现在我们更新主表

$query = <<< __EOT__
    UPDATE tbl_player p, update_temp t
    SET p.goal    = p.goal    + t.goal,
        p.assist  = p.assist  + t.assist,
        p.owngoal = p.owngoal + t.owngoal
    WHERE p.id_player = t.id_player
__EOT__;
$this->m->query($quote);

最后，一些重要的注意事项：

• 我没有测试就写下了这段代码。因此，使用它作为模板，并验证语法和语义。
• 必须添加错误处理。
• <<< __EOT__，直到__EOT__;是PHP此处文档