CODEWITHSUNDEEP
mysqldatabase
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

Is there anyway i can Insert Records in Multiple Tables using single MySQL Query?

I have a user Form where users Fill in the following details

a) Username b) Password c) Email

Username and Password belongs to users table whereas Email belongs to contacts table , now users table have foreign key contact_id which stores the Primary Key id of contacts. whenever the user submit the form i have to make two MySQL Queries to insert records into two different table.

Here is what i am using.

First Query: 

$sth = $this->dbh->prepare("INSERT INTO contacts(email) VALUES(:email)");

Second Query :

$sth = $this->dbh->prepare("INSERT INTO users(username,password,registerdate,activationString,contact_id) VALUES(:username,:password,NOW(),:activationString,".$this->dbh->lastInsertId().")");

is there anyway i could make the query into one instead of two? utilizing MySQL last_insert_id() function. ?

thank you

Upvotes: 0

Views: 826

Answers (1)

Devart
Devart

Reputation: 121912

INSERT statement allows to insert into one table.

But you could create a stored procedure to do the job.

Example:

CREATE TABLE contacts(
  id INT(11) NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE users(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  registerdate DATE DEFAULT NULL,
  activationString VARCHAR(255) DEFAULT NULL,
  contact_id VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

DELIMITER $$

CREATE
PROCEDURE add_new_user(IN p_username         VARCHAR(255),
                       IN p_password         VARCHAR(255),
                       IN p_registerdate     DATE,
                       IN p_activationString VARCHAR(255),
                       IN p_email            VARCHAR(255)
                       )
BEGIN
  INSERT INTO contacts (email) VALUES (p_email);
  INSERT INTO users (username, password, registerdate, activationString, contact_id) VALUES (p_username, p_password, p_registerdate, p_activationString, LAST_INSERT_ID ());
END
$$

DELIMITER ;

SET @p_username = 'user1';
SET @p_password = 'pwd';
SET @p_registerdate = now();
SET @p_activationString = 'str';
SET @p_email = '[email protected]';
CALL database3.add_new_user(@p_username, @p_password, @p_registerdate, @p_activationString, @p_email);

Stored procedure is optional, you can use two insert statements.

Upvotes: 2

Related Questions