0

I have table definitions:

create table users(
                  id int not null auto_increment, 
                  usernaname varchar(50) not null, 
                  pass varchar(50) not null,
                  primary key(id)
                  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
create table users_description(
                  user_id int not null, 
                  description varchar(255),
                  key(user_id), 
                  foreign key(user_id) references users(id) on delete cascade
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

and it is good, works nice.

but when I add a new user I use a next query: 

insert into users (username, pass) values('test', 'test');

but how to add a id of user automatically in users_description table? Something similar to to this: 

insert into users_description values(
                                    select id from users where username = 'test',
                                     'user description');

I want to use only two queries, is this possible?

consigliere
  • 73
  • 3
  • 11

3 Answers3

3

You can use LAST_INSERT_ID() to get the last inserted primary key id (which would be from the users table):

INSERT INTO users_description VALUES 
(LAST_INSERT_ID(), 'test', 'user description');
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • Ok, thanks.. But is this safe? I mean if something goes wrong, maybe first query fails or something other.. Is there any other solution? – consigliere Aug 16 '12 at 03:51
  • @consigliere you should encase both inserts within a transaction, so if the first insert fails, you can do a rollback to ensure your data is kept consistent. – Zane Bien Aug 16 '12 at 03:53
  • Is this examle from this [link](http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples) good enough to kept my data consistent? – consigliere Aug 16 '12 at 03:58
0

Use LAST_INSERT_ID(). Example here.

Conner
  • 30,144
  • 8
  • 52
  • 73
John Woo
  • 258,903
  • 69
  • 498
  • 492
0
$query = mysql_query("insert form (username, pass) values('test', 'test')");
$id = mysql_insert_id();

gives you the last inserted id in PHP ..

jogesh_pi
  • 9,762
  • 4
  • 37
  • 65