How to insert data in multiple table with one query PDO mysql?

Question

I need to insert data in mysql database with one query in PDO mysql.

I need the same thing what is here done with mysqli multi query.

This works fine

$insert =" 

insert into comments (user_id,comment) values('$user_id','$comment');

insert into comments2 (user_id,comment) values('$user_id','$comment');

$run = mysqli_multi_query($con,$insert);

But how can I do this in PDO

connection.php :

<?php
    class db {
     private $conn;
     private $host;
     private $user;
     private $password;
     private $baseName;
     private $port;
     private $Debug;
     
        function __construct($params=array()) {
      $this->conn = false;
      $this->host = 'localhost'; //hostname
      $this->user = 'root'; //username
      $this->password = ''; //password
      $this->baseName = 'hotwall'; //name of your database
      $this->port = '3306';
      $this->debug = true;
      $this->connect();
     }
     
     function __destruct() {
      $this->disconnect();
     }
     
     function connect() {
      if (!$this->conn) {
       try {
        $this->conn = new PDO('mysql:host='.$this->host.';dbname='.$this->baseName.'', $this->user, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));  
       }
       catch (Exception $e) {
        die('Erreur : ' . $e->getMessage());
       }
     
       if (!$this->conn) {
        $this->status_fatal = true;
        echo 'Connection BDD failed';
        die();
       } 
       else {
        $this->status_fatal = false;
       }
      }
     
      return $this->conn;
     }
     
     function disconnect() {
      if ($this->conn) {
       $this->conn = null;
      }
     }
     
     function getOne($query) {
      $result = $this->conn->prepare($query);
      $ret = $result->execute();
      if (!$ret) {
         echo 'PDO::errorInfo():';
         echo '<br />';
         echo 'error SQL: '.$query;
         die();
      }
      $result->setFetchMode(PDO::FETCH_ASSOC);
      $reponse = $result->fetch();
      
      return $reponse;
     }
     
     function getAll($query) {
      $result = $this->conn->prepare($query);
      $ret = $result->execute();
      if (!$ret) {
         echo 'PDO::errorInfo():';
         echo '<br />';
         echo 'error SQL: '.$query;
         die();
      }
      $result->setFetchMode(PDO::FETCH_ASSOC);
      $reponse = $result->fetchAll();
      
      return $reponse;
     }
     
     function execute($query) {
      if (!$response = $this->conn->exec($query)) {
       echo 'PDO::errorInfo():';
         echo '<br />';
         echo 'error SQL: '.$query;
         die();
      }
      return $response;
     }
    }

what should I do here to insert in another table

$query = $bdd->execute('insert into comments (user_id,comment) 
values('$user_id','$comment')');

I don't think this is possible in SQL. But you can make use of stored procedure for this — Saili Jaguste, Apr 04 '17 at 10:04
Possible duplicate of [PDO Prepared Inserts multiple rows in single query](http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) — Masivuye Cokile, Apr 04 '17 at 10:17

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

YOU DON'T

There is not a single reason to do these inserts in one statement. Whatever issue you are trying to fix this way is either doesn't exist or should be solved another way.

But as a rule, just insert them separately.

the db class from connection.php is also rather a disaster. Create a raw PDO connection and then run your inserts one by one:

$stmt = $pdo->prepare("insert into comments (user_id,comment) values(?,?)");
$stmt->execute([$user_id,$comment]);

$stmt = $pdo->prepare("insert into comments2 (user_id,comment) values(?,?)");
$stmt->execute([$user_id,$comment]);

is all the code you need.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 04 '17 at 10:04

Your Common Sense

152,517
33
193
313

after $query = $bdd->execute('insert into comments (user_id,comment) values('$user_id','$comment')'); i added $query = $bdd->execute('insert into comments2 (user_id,comment) values('$user_id','$comment')'); ............... is it supposed to work ?? – Lady Lancer Apr 04 '17 at 10:42
You should add the **exact** code I posted above. You should never ever use that db class of yours. – Your Common Sense Apr 04 '17 at 10:58

How to insert data in multiple table with one query PDO mysql?

1 Answers1

YOU DON'T