drz73366 2014-04-04 00:51
浏览 20
已采纳

插入多行的明智方法?

I have the following database relationship:

|--------|             |-----------------|            |-------|
| Groups | ------- 1.* | GroupMembership | *.1------- | Users |
|--------|             |-----------------|            |-------|

I have group ids in Groups, user ids in Users and the both of the in group membership, thus making a relationship between groups and users.

One record in GroupMembership looks like:

GroupID | UserID | datetime

I have an array posting from a form that looks like this:

["members"]=> array(5) { 
                          [0]=> "175" 
                          [1]=> "113" 
                          [2]=> "122" 
                          [3]=> "80" 
                          [4]=> "161"
}

Each value (175, 113 etc..) is a user ID. I need to insert a new row into the GroupMembership table for every ID in that array. How does one achieve such a task without kicking the ass out of the database and the servers resources?

Thanks in advance!

  • 写回答

1条回答 默认 最新

  • duanjupiao4730 2014-04-04 00:57
    关注

    Four INSERT statements is not going to kill your server. Here is something you can do:

    ["members"]=> array(5) { 
          [0]=> "175" 
          [1]=> "113" 
          [2]=> "122" 
          [3]=> "80" 
          [4]=> "161"
    }
    
    $db = new PDO('mysql:host=localhost;dbname=yourdbname', $user, $password);
    $statement = $db->prepare("
            INSERT INTO GroupMembership(GroupID, UserId) VALUES (
                :__group_id,
                :__user_id
            )
        ");
    
    $statement->bindParam(':__group_id', $DEFAULT_GROUP_ID, PDO::PARAM_INT);
    $statement->bindParam(':__user_id', $user_id, PDO::PARAM_INT);
    for($i = 0; $i < count($members); $i++) {
        $user_id = $members[$i];
        $DEFAULT_GROUP_ID = <your_group_id>
        $statement->execute();
    }
    

    mysqli version

    $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
    $stmt = $mysqli->prepare("
           INSERT INTO GroupMembership(GroupID, UserId) VALUES (
               ?,
               ?
           )
     ")
    for($i = 0; $i < count($members); $i++) {
        $user_id = $members[$i];
        $stmt->bind_param('ii', $DEFAULT_GROUP_ID, $user_id);
        $stmt->execute();
    }
    $stmt->close();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 qteasy 运行前进中
  • ¥15 我想通过python处理sybase IQ库的一些数据,但是不知道sybase IQ库怎么连接,之前连接都是用linux上的 dbisql
  • ¥15 安装llama-cpp-python==0.1.83失败
  • ¥20 管道轴向耦合水击问题
  • ¥60 补全networkx TODO部分。
  • ¥15 有内推吗,云计算linux运维方向
  • ¥30 sort cuteSV.vcf by bcftools用IGV可视化出现报错
  • ¥100 SOS!对STK中导出的天体图像进行质心提取有没有人做过啊
  • ¥15 python 欧式距离
  • ¥15 遗传算法解决有工序顺序约束的大规模FJSP问题