Heres the scenario: I am supposed to make a small site where user can register, I am using a table named users with fields
userid int unsigned auto_increment,
name varchar(50),
gender enum('m','f'),
time datetime,
joiningyear int unsigned,
primary key (userid)
When I insert a new user I am also supposed to log him in by saving an encrypted form of his userid as a cookie. The problem is this, if userid is already used, it is auto incremented by 1, as is obvious. I would like to recieve this new value as an output of my insert query, without adding another query that fetches it, this will makes things easy.
Currently I am using two seperate queries one checking if randomly chosen userid is already taken and second inserting the entry. Insert into users(......) values(...)
Is there a way to achieve this with one query?
Depending on your MySql adapter you can use:
mysqli_insert_id or PDO::lastInsertId. There is also mysql_insert_id but described with:
Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
If you are using any framework, it should support returning last inserted id.
mysql_insert_id() will give you the last inserted ID for a new user. As long as you do not have a massive influx you should be fine to use this.
Ultimately, a second query to retrieve the information based on their input information would be fine then store that to the cookie.