PDO Mysql same query but insert to multiple users

Question

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

You need to use a loop to insert each userid, it won't automatically loop over the array. — Barmar, Feb 08 '22 at 19:22
Why would you need to insert multiple (bulk) rows with same value? It makes no sense and bloats your database — Your Common Sense, Feb 09 '22 at 01:38

score 1 · Accepted Answer · answered Feb 08 '22 at 19:23

1

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.

answered Feb 08 '22 at 19:23

Barmar

741,623
53
500
612

Wow you are just amazing. Thank you once again! I just tried make a 1000 row insert and this seems fast. As usual, i got a question and i hope you can answer them. `bindParam` was something i seen before, but never thougt about to use it. There is cases where i would like to reuse a `bindValue` but i cant, i need to name it e.g `:text2` . Is that normal? – Printer Feb 08 '22 at 19:36
1

It's hard to tell what you're talking about, you should post a new question. – Barmar Feb 08 '22 at 20:51
I understand, will do – Printer Feb 08 '22 at 20:52

PDO Mysql same query but insert to multiple users

1 Answers1