MySQL Insert into multiple tables Database normalization

Question

I tried searching a way to insert information in multiple tables in the same query  but found out it s impossible  So I want to insert it by simply using multiple queries i e   INSERT INTO users  username  password  VALUES  test    test   INSERT INTO profiles  userid  bio  homepage  VALUES   id of the user here     Hello world     http   www stackoverflow com     But how can I give the auto-increment id from the users to the  manual  userid for the profile table

User · Answer

For PDO You may do this   stmt1    INSERT INTO users  username  password  VALUES  test    test       stmt2    INSERT INTO profiles  userid  bio  homepage  VALUES  LAST INSERT ID    Hello world     http   www stackoverflow com       sth1    dbh- gt prepare  stmt1    sth2    dbh- gt prepare  stmt2    BEGIN   sth1- gt execute  array   test   test      sth2- gt execute  array   Hello world    http   www stackoverflow com     COMMIT

User · Answer

try this    sql    INSERT INTO users  username  password  VALUES  test    test      mysql query  sql    user id  mysql insert id    if  empty  user id      sql INSERT INTO profiles  userid  bio  homepage  VALUES  user id  Hello world     http   www stackoverflow com       or    sql INSERT INTO profiles  userid  bio  homepage  VALUES LAST INSERT ID    Hello   world     http   www stackoverflow com        mysql query  sql        References PHP MYSQL

User · Answer

What would happen  if you want to create many such records ones  to register 10 users  not just one   I find the following solution  just 5 queryes    Step I  Create temporary table to store new data   CREATE TEMPORARY TABLE tmp  id bigint 20  NOT NULL             Next  fill this table with values   INSERT INTO tmp  username  password  bio  homepage  VALUES  ALL VAL   Here  instead of  ALL VAL you place list of values    test1   test1   bio1   home1         testn   testn   bion   homen    Step II  Send data to  user  table   INSERT IGNORE INTO users  username  password  SELECT username  password FROM tmp    Here   IGNORE  can be used  if you allow some users already to be inside  Optionaly you can use UPDATE similar to step III  before this step  to find whom users are already inside  and mark them in tmp table   Here we suppouse  that username is declared as PRIMARY in users table   Step III  Apply update to read all users id from users to tmp table  THIS IS ESSENTIAL STEP   UPDATE tmp JOIN users ON tmp username users username SET tmp id users id   Step IV  Create another table  useing read id for users  INSERT INTO profiles  userid  bio  homepage   SELECT id  bio  homepage FROM tmp

User · Answer

Just a remark about your saying     Hi  I tried searching a way to insert information in multiple tables in the same query   Do you eat all your lunch dishes mixed with drinks in the same bowl  I suppose - no   Same here  There are things we do separately  2 insert queries are 2 insert queries  It s all right  Nothing wrong with it  No need to mash it in one  Same for select  Query must be sensible and do it s job  That s the only reasons  Number of queries is not   As for the transactions - you may use them  but it s not THAT big deal for the average web-site  If it happened once a year  if ever  that one user registration being broken you ll be able to fix  no doubt  there are hundreds of thousands sites running mysql with no transaction support driver  Have you heard of terrible disasters breaking these sites apart  Me neither   And mysql insert id   has noting to do with transactions  you may include in into transaction all right  it s just different matters  Someone raised this question out of nowhere

User · Answer

fairly simple if you use stored procedures   call insert user and profile  f00   http   www f00 com      full script   drop table if exists users  create table users   user id int unsigned not null auto increment primary key  username varchar 32  unique not null   engine innodb   drop table if exists user profile  create table user profile   profile id int unsigned not null auto increment primary key  user id int unsigned not null  homepage varchar 255  not null  key  user id    engine innodb   drop procedure if exists insert user and profile   delimiter    create procedure insert user and profile   in p username varchar 32   in p homepage varchar 255    begin declare v user id int unsigned default 0   insert into users  username  values  p username   set v user id   last insert id    -- save the newly created user id  insert into user profile  user id  homepage  values  v user id  p homepage    end   delimiter    call insert user and profile  f00   http   www f00 com     select   from users  select   from user profile

User · Answer

No  you can t insert into multiple tables in one MySQL command  You can however use transactions   BEGIN  INSERT INTO users  username  password    VALUES  test    test    INSERT INTO profiles  userid  bio  homepage     VALUES LAST INSERT ID    Hello world     http   www stackoverflow com    COMMIT    Have a look at LAST INSERT ID   to reuse autoincrement values   Edit  you said  After all this time trying to figure it out  it still doesn t work  Can t I simply put the just generated ID in a  var and put that  var in all the MySQL commands    Let me elaborate  there are 3 possible ways here    In the code you see above  This does it all in MySQL  and the LAST INSERT ID   in the second statement will automatically be the value of the autoincrement-column that was inserted in the first statement   Unfortunately  when the second statement itself inserts rows in a table with an auto-increment column  the LAST INSERT ID   will be updated to that of table 2  and not table 1  If you still need that of table 1 afterwards  we will have to store it in a variable  This leads us to ways 2 and 3  Will stock the LAST INSERT ID   in a MySQL variable   INSERT     SELECT LAST INSERT ID   INTO  mysql variable here  INSERT INTO table2   mysql variable here        INSERT INTO table3   mysql variable here         Will stock the LAST INSERT ID   in a php variable  or any language that can connect to a database  of your choice     INSERT     Use your language to retrieve the LAST INSERT ID    either by executing that literal statement in MySQL  or using for example php s mysql insert id   which does that for you INSERT  use your php variable here     WARNING  Whatever way of solving this you choose  you must decide what should happen should the execution be interrupted between queries  for example  your database-server crashes   If you can live with  some have finished  others not   don t read on   If however you decide  either all queries finish  or none finish - I do not want rows in some tables but no matching rows in others  I always want my database tables to be consistent   you need to wrap all statements in a transaction  That s why I used the BEGIN and COMMIT here   Comment again if you need more info

User · Answer

have a look at mysql insert id    here the documentation  http   in php net manual en function mysql-insert-id php

User · Answer

This is the way that I did it for a uni project  works fine  prob not safe tho   dbhost    localhost    dbuser    root    dbpass        conn   mysql connect  dbhost   dbuser   dbpass     title        POST  title                 name         POST  name              surname      POST  surname                       email        POST  email                 pass         POST  password          cpass        POST  cpassword              check   1   if       else       check   1       if   check    1    require once  website data collecting db php      sel user    SELECT   FROM users WHERE user email   email     run user   mysqli query  con   sel user    check user   mysqli num rows  run user    if   check user  gt  0       echo      lt div style  margin  0 0 10px 20px   gt Email already exists  lt  br gt                lt a href  recover php  gt Recover Password lt  a gt  lt  div gt      else       users tb    INSERT INTO users                  user name  user email  user password               VALUES   name    email    pass           users info tb    INSERT INTO users info                user title  user surname             VALUES   title     surname          mysql select db  dropbox         run users tb   mysql query   users tb   conn         run users info tb   mysql query   users info tb   conn         if   run users tb      run users info tb           die  Could not enter data      mysql error               else          echo  Entered data successfully n              mysql close  conn

[mysql] MySQL Insert into multiple tables? (Database normalization?)

Examples related to mysql