0

I have a simple table 

CREATE TABLE `keys` (
`IDkey` int NOT NULL auto_increment,
`username` varchar(50) NOT NULL, 
PRIMARY KEY  (`IDkey`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

and give out incrementing integer keys 'IDkey' to users as they call in with my webapp. I also store their username in the table.

I have two questions I'm stuck on.

First question: at the moment I am doing the following to give out the key, is there a way to combine this into one query?

INSERT INTO keys VALUES (NULL, '$username');
SELECT IDkey FROM keys WHERE username='$username';

Second Question: the key expires so the same user may return for a new key but this causes a problem because their username is already in the database. Is there a way to write my SELECT query so it returns the most recent record by that user?

PrimeLens
  • 2,547
  • 4
  • 23
  • 28
  • 1
    got to this link hope it will help you in giving the idea: http://stackoverflow.com/questions/5391344/mysql-insert-with-select – Clint Bugs Oct 08 '12 at 02:27
  • I read the link. So if I removed my semicolon and combined my two queries so it read INSERT INTO keys VALUES (NULL, '$username') SELECT IDkey FROM keys WHERE username='$username'; then would it return the newly created IDkey? – PrimeLens Oct 08 '12 at 02:50

2 Answers2

2

If you're using PDO, check out PDO::lastInsertId().

If you're using mysqli, check out mysqli::$insert_id.

If you're using mysql, upgrade your code to use PDO or mysqli. ;-) (But seriously, you can do this with mysql_insert_id()).

FtDRbwLXw6
  • 27,774
  • 13
  • 70
  • 107
  • I'm worried that if twenty users request simultaneously mysqli::$insert_id might return the wrong record because it returns the most recent without checking username. I guess I dont know if the server is running my script sequentially or in parallel. If it processes each call one at a time then of course it would return the correct record as it is the most recent record. But if the server executes the code simultaneously for the calls then several records might be inserted before the request you suggested. please advise – PrimeLens Oct 08 '12 at 03:00
  • @PrimeLens: Each database connection is its own session. As long as your code retrieves the insert ID before it does another insert, you will be okay. – FtDRbwLXw6 Oct 08 '12 at 03:04
1

Immediately call this function mysql_insert_id() after you insert this record. It will give you the id of last inserted record.

Murtuza Kabul
  • 6,438
  • 6
  • 27
  • 34