douhuang2282
douhuang2282
2019-02-05 08:36

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

已采纳

first I hope You understand the question I made because I'm not English. I'm making simple football league table. So I want to record all the team's goal between home team and away team including the players assists, and own goals using PHP form. the problem is I want to SUBMIT them in ONE foreach() instead of foreach() for inserting the goals, foreach() for inserting the assists, and foreach() for inserting the own goals.

I have tried using foreach(), and I just only know that is for ONE indexed array variable or Associative Array;

The code of form input goals, asssists, and own goal (this is from JSON encode):

'.... <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>'+ ....

The code for get the data from home team side:

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

And i have tried to insert the data to database something like this:

 //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
                                        ");
                    }

but my expecting something like this (I know this is wrong):

    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
                     ");

I know that was wrong but I don't know the best way to explain this, I've been searching this similiar problem, but no one can resolve or maybe I don't understand. Thank you

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doutou7549 doutou7549 2年前

    In all of your arrays, you use the same indexes. So an easy way would be to run trough one of the arrays and use the index to get the data from all arrays with the given index within each loop.

    $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");
    }
    

    But you could also cange the names of your input fields from

    <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+']">
    

    to

    <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]">
    

    Then you get one Array with all the data in it and you can go trough the data like this:

    $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");
    }
    
    点赞 评论 复制链接分享
  • doubo4336 doubo4336 2年前

    So many UPDATEs. This is very inefficient. I would collect all data as SQL values, create a temporary table with all data and then update the main table.

    Your input filed share the same indices. Good! Collecting data:

    # 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
    

    Now we create a TEMPORARY table, which is automatically removed after closing the database connection. But first we drop the table to allow multiple usage.

    $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);
    

    Now we fill the table:

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

    Now we update the main table

    $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);
    

    Last not least some notes:

    • I wrote down this code without testing it. So use it as template and verify syntax and semantics.
    • Error handling must be added.
    • <<< __EOT__ until __EOT__; is a PHP here document
    点赞 评论 复制链接分享

相关推荐