php - Insert values into two tables that are linked (1:M)

258

I have two tables user and email. One user can have multiple emails so its a seperate table(that contains user_id as part of its primary key). I would like to insert multiple attributes into user table and one email into email table as well. What is the best way to do that with mySQL query. Is there any single line command that will do that??

If i cant do that with just with mySql I can use php script as well

Update I see that people are misunderstanding my question. All i want to do is toinsert values like username password into my user table and one email into the linked email table (the one linked to the same user) I wonder what is the best way to do it in mySQL (and if necessarly Php)

I would like the answer to include the mySQL query (and php scrips as well if necessarily)

953

Answer

Solution:

The best way is to do it in a transaction. That way, if the e-mail insert fails the entire transaction will also fail.

BEGIN;

INSERT INTO users (name)
VALUES ('Emil Vikström');

INSERT INTO emails (user_id, email)
VALUES (LAST_INSERT_ID(), 'email address');

COMMIT;

840

Answer

Solution:

You should execute multiple SQL INSERT queries. I think the best way to do multiple operations monolite is to use SQL-transactions.

999

Answer

Solution:

You can do this;

$query = "INSERT INTO users (name) VALUES ("you username");
$result = mysql_query($query);
if($result)
{
   $generated_userID = mysql_insert_id(); // php function to get the last generated ID by previous result
    $query = "INSERT INTO emails(user_ID, email) VALUES ($generated_userID, "you email value");
    $result = mysql_query($query);
    if($result)  
       echo "email inserted in email table";
     else
      {
        echo "problem inserting in email table";
         return;
       }
 }
 else
 {
    echo "can't insert into users table";
  }

Let me know if it works.

989

Answer

Solution:

Like this you can insert multiple values and multiple rows, but I never saw somehthing to do muliple insert into different tables.

 INSERT INTO example
      (example_id, name, value, other_value)
    VALUES
      (100, 'Name 1', 'Value 1', 'Other 1'),
      (101, 'Name 2', 'Value 2', 'Other 2'),
      (102, 'Name 3', 'Value 3', 'Other 3'),
      (103, 'Name 4', 'Value 4', 'Other 4');

The best thing to do is to insert a row into user retreive the user id withmysql_insert_id described here

People are also looking for solutions to the problem: php - Unable to submit feed in sites like friendfeed and feedage

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question