mysql在多个循环中从一个用户向其他用户插入表中的多行只会插入最后一个结果

我试图将所有结果从关系表中的client\u id 67复制到users\u groups表中的所有其他用户，所以id的99、111、130在关系表中的结果必须与id 67相同。
所以所有的查询都可以正常工作，更新也可以正常工作，删除也可以，但是insert不会将任何数据插入到我需要的其他id的关系表中。
我搜索了stackoverflow，但没有找到任何可能适用于我的示例的解决方案。
需要从中复制数据的用户id 67的关系表和结果

signal_providers_relations TABLE

| id | client_id | provider_id | active_sp | subscription_sp | created_by | cr_datetime | ea_settings_active_sp | ea_settings_sp          | up_datetime | sp_risk_allocation | sp_max_trades |
|----|-----------|-------------|-----------|-----------------|------------|-------------|-----------------------|-------------------------|-------------|--------------------|---------------|
| 1  |  67       |  12         | Y         | 0000-00-00      | user123    | 2018-04-09  | Y                     | 0|1|0|1|1|2||1.0|0|0|1  | 2018-04-09  | 100                | 0             |
| 2  |  67       |  4          | N         | 0000-00-00      | user1      | 2018-04-10  | Y                     | 1|1|1|1|0|0|1|00|0|0|2  | 2018-04-10  | 120                | 1             | 
| 3  |  67       |  5          | Y         | 0000-00-00      | test23     | 2018-04-11  | N                     | 2|1|0|2|1|5|1|11|1|2|1  | 2018-04-11  | 95                 | 1             |

用户组表

users_groups TABLE

| id | user_id | group_id | user_role |
|----|---------|----------|-----------|
| 1  | 130     | 94       | 0         |
| 2  | 111     | 94       | 0         |
| 3  | 99      | 94       | 0         |
| 4  | 67      | 94       | 1         |

代码如下：

$user_role = intval($_POST['user_role']);
    $userID = intval($_POST['user_id']);
    $groupID = intval($_POST['group_id']);
    $ea_settings = $_POST['ea_settings'];

    // get users from group to apply ea_settings
    $get_users = "SELECT a.group_name, b.user_id, b.user_role, b.group_id, c.email, c.ea_settings
            FROM user_groups a
            INNER JOIN users_groups b ON b.group_id = a.id
            INNER JOIN www_users c ON c.id = b.user_id
            WHERE a.id = '".$groupID."'";
    $pr = fetch_array($get_users);

    // get special user relations
    $query2 = "SELECT a.*, b.user_role
               FROM signal_providers_relations a
               INNER JOIN users_groups b ON a.client_id = b.user_id
               WHERE a.client_id = '".$userID."' AND b.user_role = '1'";
    $pr2 = fetch_array($query2);

    // get number of items from special user
    $query3 = "SELECT count(a.id) as cnt
                FROM signal_providers_relations a
                INNER JOIN users_groups b ON a.client_id = b.user_id
                WHERE a.client_id = '".$userID."' AND b.user_role = '1'";
    $pr3 = fetch_array($query3);

    // update all users where id = all id's from group, apply ea_settings to all id's from Special user 
    foreach ($pr as $key) {
        foreach ($pr2 as $r) {
            foreach ($pr3 as $s) {

                $user_id = intval($key['user_id']); // id of all users

                // update users
                $update_users = "UPDATE www_users SET ea_settings = '".$ea_settings."' WHERE id = '".$user_id."'";

                if (exec_query_confirm($update_users)) {

                    // first delete all user record in relations that are not special users, delete all record if user is have role = 0, and insert new data
                    if ($key['user_role'] == '0') {
                        $delete_relations = "DELETE FROM signal_providers_relations WHERE client_id = '".$user_id."'";
                        exec_query($delete_relations);

                        // loop through special user records and add to other users
                        for ($i=0; $i < $s['cnt']; $i++) { 

                            // $s['cnt'] = 10 = number of records in relations table from id 67

                            $insert_new_values = "INSERT INTO signal_providers_relations (client_id, provider_id, active_sp, subscription_sp, created_by, cr_datetime, ea_settings_active_sp, ea_settings_sp, up_datetime, sp_risk_allocation, sp_max_trades) 
                                       VALUES ('".$user_id."', '".$r['provider_id']."', '".$r['active_sp']."', '".$r['subscription_sp']."', '".$r['created_by']."', '".$r['cr_datetime']."', '".$r['ea_settings_active_sp']."', '".$r['ea_settings_sp']."', '".$r['up_datetime']."', '".$r['sp_risk_allocation']."', '".$r['sp_max_trades']."')";
                          exec_query($insert_new_values);

                        } 
                    }
                } else {
                    $err = 61;
                }
            } // foreach
        } // foreach
    } // foreach

}

所以基本上我需要这个

| id | client_id | provider_id | active_sp | subscription_sp | created_by | cr_datetime | ea_settings_active_sp | ea_settings_sp          | up_datetime | sp_risk_allocation | sp_max_trades |
|----|-----------|-------------|-----------|-----------------|------------|-------------|-----------------------|-------------------------|-------------|--------------------|---------------|
| 1  |  99       |  12         | Y         | 0000-00-00      | user123    | 2018-04-09  | Y                     | 0|1|0|1|1|2||1.0|0|0|1  | 2018-04-09  | 100                | 0             |
| 2  |  99       |  4          | N         | 0000-00-00      | user1      | 2018-04-10  | Y                     | 1|1|1|1|0|0|1|00|0|0|2  | 2018-04-10  | 120                | 1             | 
| 3  |  99       |  5          | Y         | 0000-00-00      | test23     | 2018-04-11  | N                     | 2|1|0|2|1|5|1|11|1|2|1  | 2018-04-11  | 95                 | 1             |

| id | client_id | provider_id | active_sp | subscription_sp | created_by | cr_datetime | ea_settings_active_sp | ea_settings_sp          | up_datetime | sp_risk_allocation | sp_max_trades |
|----|-----------|-------------|-----------|-----------------|------------|-------------|-----------------------|-------------------------|-------------|--------------------|---------------|
| 1  |  111      |  12         | Y         | 0000-00-00      | user123    | 2018-04-09  | Y                     | 0|1|0|1|1|2||1.0|0|0|1  | 2018-04-09  | 100                | 0             |
| 2  |  111      |  4          | N         | 0000-00-00      | user1      | 2018-04-10  | Y                     | 1|1|1|1|0|0|1|00|0|0|2  | 2018-04-10  | 120                | 1             | 
| 3  |  111      |  5          | Y         | 0000-00-00      | test23     | 2018-04-11  | N                     | 2|1|0|2|1|5|1|11|1|2|1  | 2018-04-11  | 95                 | 1             |

| id | client_id | provider_id | active_sp | subscription_sp | created_by | cr_datetime | ea_settings_active_sp | ea_settings_sp          | up_datetime | sp_risk_allocation | sp_max_trades |
|----|-----------|-------------|-----------|-----------------|------------|-------------|-----------------------|-------------------------|-------------|--------------------|---------------|
| 1  |  130      |  12         | Y         | 0000-00-00      | user123    | 2018-04-09  | Y                     | 0|1|0|1|1|2||1.0|0|0|1  | 2018-04-09  | 100                | 0             |
| 2  |  130      |  4          | N         | 0000-00-00      | user1      | 2018-04-10  | Y                     | 1|1|1|1|0|0|1|00|0|0|2  | 2018-04-10  | 120                | 1             | 
| 3  |  130      |  5          | Y         | 0000-00-00      | test23     | 2018-04-11  | N                     | 2|1|0|2|1|5|1|11|1|2|1  | 2018-04-11  | 95                 | 1             |

我认为您的主要问题是实际上不需要第三个查询或循环（ foreach 在 $pr2 达到与 for 在 $s['cnt'] ). 你应该这样做：

// update all users where id = all id's from group, apply ea_settings to all id's from Special user 
foreach ($pr as $key) {
    $user_id = intval($key['user_id']); // id of all users
    // update users
    $update_users = "UPDATE www_users SET ea_settings = '".$ea_settings."' WHERE id = '".$user_id."'";
    if (exec_query_confirm($update_users)) {
        // first delete all user record in relations that are not special users, delete all record if user is have role = 0, and insert new data
        if ($key['user_role'] == '0') {
            $delete_relations = "DELETE FROM signal_providers_relations WHERE client_id = '".$user_id."'";
            exec_query($delete_relations);
            // loop through special user records and add to other users
            foreach ($pr2 as $r) {
                $insert_new_values = "INSERT INTO signal_providers_relations (client_id, provider_id, active_sp, subscription_sp, created_by, cr_datetime, ea_settings_active_sp, ea_settings_sp, up_datetime, sp_risk_allocation, sp_max_trades) 
                                       VALUES ('".$user_id."', '".$r['provider_id']."', '".$r['active_sp']."', '".$r['subscription_sp']."', '".$r['created_by']."', '".$r['cr_datetime']."', '".$r['ea_settings_active_sp']."', '".$r['ea_settings_sp']."', '".$r['up_datetime']."', '".$r['sp_risk_allocation']."', '".$r['sp_max_trades']."')";
                exec_query($insert_new_values);
            } // foreach
        } else {
            $err = 61;
        }
    } // if
} // foreach

mysql在多个循环中从一个用户向其他用户插入表中的多行只会插入最后一个结果

1条答案

相关问题

热门标签

最新问答