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
– Saili Jaguste, Commented Apr 4, 2017 at 10:04
Possible duplicate of PDO Prepared Inserts multiple rows in single query — Masivuye Cokile
– Masivuye Cokile, Commented Apr 4, 2017 at 10:17

Your Common Sense · Accepted Answer · 2022-01-18 13:00:58Z

2

Use 2 queries, not one. Of course it must be parameterized queries.

$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 Jan 18, 2022 at 13:00

answered Apr 4, 2017 at 10:04

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

2 Comments

Lady Lancer Over a year ago

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 ??

Your Common Sense Over a year ago

You should add the exact code I posted above. You should never ever use that db class of yours.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related