php - PHP MySQL插入具有不同ID的多行

我有下表：

id | message_id | recevier_id
 1 |      8     |    2
 2 |      9     |    5
 3 |     14     |    4

我正在将数据发送到PHP文件以添加到上表中。我的数据如下：

messageid =“ 2”
接收者ID =“ 5,6,34”

我正在尝试添加具有不同“接收者ID”的多行，因此上述查询的结果应为：

id | message_id | recevier_id
 1 |      8     |    2
 2 |      9     |    5
 3 |     14     |    4
 4 |      2     |    5
 5 |      2     |    6
 6 |      2     |   34

我当前的MySQL查询如下所示：

<?php
$inputvalues = $_POST;
$errors = false;
$result = false;
session_start();
include_once '../../../includes/database.php';
$uid = $_SESSION['usr_id'];
$sendername = $_SESSION['firstnames'].' '.$_SESSION['surname'];

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    foreach ($inputvalues as $key => $value) {
        if(isset($value) && !empty($value)) {
            $inputvalues[$key] = $mysqli->real_escape_string( $value );
        } else {
            $errors[$key] = 'The field '.$key.' is empty';
        }
    }

    if( !$errors ) {
        $mysqli->query("
            INSERT INTO `message_receiver_map` (`message_id`, `receiver_id`) VALUES ('".$messageid."', '".$inputvalues['receiverid']."');
        ");
        $returnResult = "Success";
    }
    mysqli_close($mysqli);
    echo json_encode(['result' => $returnResult, 'errors' => $errors]);
    exit;
?>

我该如何实现？

最佳答案

假设$ _POST中的2个字段存在

$_POST['messageid'] = "2"
$_POST['receiver id'] = "5,6,34"

然后可以像这样完成3行的创建

请记住，一旦准备好带有参数的查询，该查询便可以重复使用任意次，并且每次执行时都会使用新的参数值。

<?php
    session_start();
    include_once '../../../includes/database.php';

    // this if should probably be in your database.php script
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    if ( isset($_POST['messageid'],$_POST['receiver id']) ){
        // proceed to create the new rows

        //create a query
        $sql = "INSERT INTO `message_receiver_map`
                        (`message_id`, `receiver_id`)
                VALUES (?,?)";

        // prepare the query (send to server and compile it)
        // now this query can be run multiple times with different
        // parameter set each time it is executed
        $stmt = $mysqli->prepare($sql);

        // now for each receiver_id run the query
        $rec_ids = explode($_POST['receiver id']);

        foreach ($rec_ids as $rec_id) {
            // bind the 2 parameters from this iteration to the query
            $stmt->bind_value('ii',
                                $_POST['messageid'],
                                $rec_id);
            // run query with these new params
            $result = $stmt->execute();
        }
    }
}
?>

如果您使用的是INNODB数据库，则还可以围绕该事务包装一个事务，这样一来，您将创建所有3行，如果在创建3个新行时某处发生错误，则不会创建任何3行。