MySql: Insert a row and get the content

Question

Is it possible to insert a row and get the values inserted in the same query?

Something like...

INSERT INTO `items` (`item`, `number`, `state`)  (SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3') 

And then, get ID and execute a

SELECT * FROM `items` WHERE `id`='$id' 

But using only one query.

Answer 1

Execute your insert statement and then you can do this:

SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID() 

Answer 2

you can call a stored procedure which will perform the insert and return a resultset in a single call from your app layer to mysql:

Stored procedure call

mysql> call insert_user('bar'); +---------+----------+ | user_id | username | +---------+----------+ |       1 | bar      | +---------+----------+ 1 row in set (0.02 sec)  $sqlCmd = sprintf("call insert_user('%s')", ...); 

Simple example:

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 procedure if exists insert_user;  delimiter #  create procedure insert_user ( in p_username varchar(32) ) begin declare v_user_id int unsigned default 0;   insert into users (username) values (p_username);   set v_user_id = last_insert_id();   -- do more stuff with v_user_id e.g. logs etc...   select * from users where user_id = v_user_id;  end#  delimiter ;  call insert_user('bar');