0

I need to insert values into multiple tables where the primary key (id) of the first table (users) is the foreign key (user_id) of subsequent tables (email etc.).

I worked out the following query in MySQL Workbench, which works perfectly fine there. However when I run it in the context of the website form that I'm developing I keep getting a syntax error message for the line: SET @nameID = LAST_INSERT_ID();

INSERT INTO users (first_name, last_name)
  VALUES('John', 'Doe');
SET @userID = LAST_INSERT_ID();
INSERT INTO email (user_id, email)
  VALUES(@userID,'johndoe@email.com');
INSERT INTO phone_number (user_id, phone_number)
  VALUES(@userID, 4546254758);

I've spent a bit of time trying to workout what the issue is but haven't been able to come with an answer so far. Any help would be appreciated.

Edit: Updated code to show that by 'multiple' I mean more than two.

Amir
  • 1,328
  • 2
  • 13
  • 27
miketruk
  • 3
  • 2
  • Consider an after insert trigger using NEW.ID instead of last_insert_id (assuming ID is your auto_increment column in users). – P.Salmon Sep 06 '20 at 08:06

2 Answers2

1

You don’t need a user variable for this. If you are running the two queries sequentially in the same database session, you can just do:

INSERT INTO users (first_name, last_name)
  VALUES('John', 'Doe');
INSERT INTO email (user_id, email)
  VALUES(LAST_INSERT_ID(),'johndoe@email.com');
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Sorry, I should have clarified that by 'multiple' I meant more than two. When I used the method you suggest previously I found that repeating LAST_INSERT_ID() in each query would mess up the data entry, so I figured I needed to use variables. I've also just tested the particular example you've given and it's still giving me a syntax error. – miketruk Sep 06 '20 at 10:19
0

don't run all queries one shot , you should run one by one then it will work through php normally , try like this

    $mysqli = new mysqli('localhost', 'root', '', 'test');
    $mysqli->query("INSERT INTO users (first_name, last_name)
       VALUES('John8', 'Doe');" );
    $mysqli->query('SET @userID = LAST_INSERT_ID();' );
    $mysqli->query("INSERT INTO email (user_id,email) VALUES ( @userID 
       ,'johndoe8@email.com');" );
Akram Elhaddad
  • 194
  • 3
  • 7
  • 1
    Thanks, this seems to work. I'd like to add an if/else confirmation message for the data going through but I'm still unfamiliar with object-oriented mysql. Do you have a solution available, or know of a place I can go to work out how to do it? – miketruk Sep 06 '20 at 10:59
  • it is more simple and safe , try to work with PDO is last object-oriented mysqli try to search youtube maximum 2 days you will be familiar with it – Akram Elhaddad Sep 06 '20 at 11:12