插入多行的明智方法？

Question

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!

Answer 1

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