PDO Mysql same query but insert to multiple users

February 8, 2022

I would like through pdo insert multiple (bulk) rows with same value, only diffent is the user_id

I’m passing a array with userIds but i have no idea how to bind them.

<?php   
    require_once("db.php");

    $usersId = $jsonData["usersId"];
    $text = $jsonData["text"];

    // Try to fetch the user from the database
    $query = "INSERT INTO posts (user_id, text) VALUES (:usersId, :text)";
    $stmt = $db->prepare($query);

    // Bind value
    $stmt->bindValue(":userId", $userId);
    $stmt->bindValue(":text", $text, PDO::PARAM_STR);

    // Execute
    $result = $stmt->execute();
?>

My Tables:

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

INSERT INTO users (name)
VALUES ("Gregor"),
    ("Liza"),
    ("Matt"),
    ("Bob");
   
CREATE TABLE posts(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    text VARCHAR(255)
);

>Solution :

You need a loop:

    require_once("db.php");

    $text = $jsonData["text"];

    // Try to fetch the user from the database
    $query = "INSERT INTO posts (user_id, text) VALUES (:usersId, :text)";
    $stmt = $db->prepare($query);

    // Bind value
    $stmt->bindParam(":userId", $userId);
    $stmt->bindValue(":text", $text, PDO::PARAM_STR);

    // Execute
    foreach ($jsonData["usersId"] as $userId) {
        $result = $stmt->execute();
    }

Use bindParam() so it binds to a reference to the variable. That allows you to reassign the variable each time through the loop without re-binding.