PDO Mysql same query but insert to multiple users [duplicate]

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
– Barmar, Commented Feb 8, 2022 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
– Your Common Sense, Commented Feb 9, 2022 at 1:38

Barmar · Accepted Answer · 2022-02-08 19:23:34Z

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 8, 2022 at 19:23

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Printer Over a year ago

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?

Barmar Over a year ago

It's hard to tell what you're talking about, you should post a new question.

Printer Over a year ago

I understand, will do

Collectives™ on Stack Overflow

PDO Mysql same query but insert to multiple users [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related