Insert into 2 tables with PDO MySQL

Question

Possible Duplicate:
MySQL Insert into multiple tables? (Database normalization?)

im trying to use PDO to insert my records into 2 tables, I have the following

try {
  // Connect and create the PDO object
 $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
$conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "INSERT INTO `directory` (`First_Name`,`Surname`,`Nicknames`) 
      VALUES (:firstname, :surname, :nicknames) ";

 $statement = $conn->prepare($sql);
 $statement->bindValue(":firstname", $firstname);
 $statement->bindValue(":surname", $surname);
 $statement->bindValue(":nicknames", $nicknames);


 $count = $statement->execute();

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

that inserts my data into 1 table fine, if I use (What i presume to be corrct) however my page doesnt render and no source code is output? Can anybody see if im going wrong anywhere?

try {
  // Connect and create the PDO object
 $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
$conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "INSERT INTO `directory`, `nicknames`  (`First_Name`,`Surname`,`Nicknames`) 
      VALUES (:firstname, :surname, :nicknames) ";

 $statement = $conn->prepare($sql);
 $statement->bindValue(":firstname", $firstname);
 $statement->bindValue(":surname", $surname);
 $statement->bindValue(":nicknames", $nicknames);


 $count = $statement->execute();

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

You don't need two tables if you insert exactly the same data. It much looks like you're doing it wrong on a different level. — hakre, Dec 27 '12 at 18:17

score 8 · Accepted Answer · answered Dec 27 '12 at 18:12

8

That’s not valid SQL; you need to do them separately. Maybe:

$sql = "
    INSERT INTO `directory`(`First_Name`,`Surname`,`Nicknames`) VALUES (:firstname, :surname, :nicknames);
    INSERT INTO `nicknames`(`First_Name`,`Surname`,`Nicknames`) VALUES (:firstname, :surname, :nicknames);
";

answered Dec 27 '12 at 18:12

Ry-

218,210
55
464
476

Agreed, or use a transaction to process them both at once... though it is effectively doing the same thing. – Mattt Dec 27 '12 at 18:14
Worked! I have a slight issue however, When inserted into my first table, a User_ID is generated with auto_inc, how do i take this ID and place it into the second Insert query? – Liam Dec 27 '12 at 18:18
@Liam: Then you’d have to do them as separate queries, and insert [`PDO::lastInsertId`](http://php.net/manual/en/pdo.lastinsertid.php). – Ry- Dec 27 '12 at 18:19

MrGlass · Answer 2 · 2019-11-24T22:26:22.030

As far as I know, you cannot insert into 2 tables with one mySQL query. Instead, you should use two different queries.

Addendum Looking on this answer much later in my career, I have two additions:

You could probably do two inserts with subqueries, but you shouldn't. You should use separate queries and a transaction. Transactions are important, more devs should use them.
Looking at what the OP was trying to accomplish, this could be an operation that would be best done in a stored procedure.

score 0 · Answer 3 · answered Dec 27 '12 at 18:14

0

The problem is with the SQL, I don't believe it is valid syntax. I think you need a transaction in this case.

answered Dec 27 '12 at 18:14

Mattt

1,780
14
15

Insert into 2 tables with PDO MySQL

3 Answers3

Linked

Related