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

Question

我有下表:

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

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

消息ID =“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;
?>

我怎样才能实现这个目标？

Answer 1

你可以这样做:

$messageid = "2";         // received value
$receiverids = "5,6,34";  // received value
$receiverid = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO `table` (`message_id`, `receiver_id`) VALUES (?, ?)");
$stmt->bind_param("ss", $messageid, $receiverid);

foreach($rid in explode($receiverids)) {
   // set parameters and execute
   $receiverid = $rid;
   $stmt->execute();
}

$stmt->close();