How to insert and select data in the same SQL query?

Question

I used query like this

INSERT INTO coins VALUES (NULL, '$user_ID', '$title', NOW())

now later in this same script page, I need to use value coin_ID that I just inserted in the query above. It's auto increment value so I added NULL.

how can I use this coin_ID in this same page, after I ran some checking with PHP?

thanks

and I forgot to mention, I'm going to use that coin_ID to select linked columns in another table — Bojan Savic
– Bojan Savic, Commented Feb 20, 2012 at 17:44
I get this error when I use that function i.imgur.com/f34EJ.png can it be issue with permissions? — Bojan Savic
– Bojan Savic, Commented Feb 20, 2012 at 17:55

Jesse van Assen · Accepted Answer · 2012-02-20 17:45:08Z

2

mysql_insert_id in PHP or LAST_INSERT_ID in mysql

answered Feb 20, 2012 at 17:45

Jesse van Assen

2,3102 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jasonlfunk · Accepted Answer · 2012-02-20 17:52:39Z

2

It depends on which database driver you are using with PHP. If you are using the standard mysql functions you can use the mysql_insert_id function:

$coin_id = mysql_insert_id()

If you are using PHP's PDO then you can use the lastInsertId() method:

$coin_id = $DB->lastInsertId()

If you are using a database that supports the RETURNS sql keyword (such as postgres) then you can do it in one query. MySQL doesn't support it however.

INSERT INTO coins VALUES (NULL, '$user_ID', '$title', NOW()) RETURNING coin_id;

If you edit your post to include the method of database interaction that you are using, we can provide a more specific answer.

answered Feb 20, 2012 at 17:52

jasonlfunk

5,2695 gold badges32 silver badges39 bronze badges

Comments

animuson · Accepted Answer · 2012-02-24 05:39:10Z

2

See mysql_insert_id.

edited Feb 24, 2012 at 5:39

animuson♦

54.9k28 gold badges142 silver badges150 bronze badges

answered Feb 20, 2012 at 17:45

Kavi Siegel

2,9942 gold badges26 silver badges33 bronze badges

Comments

Bartosz Grzybowski · Accepted Answer · 2012-02-20 17:45:12Z

1

Use mysql_insert_id to retrive last inserted ID.

answered Feb 20, 2012 at 17:45

Bartosz Grzybowski

1,1999 silver badges21 bronze badges

Comments

Zac · Accepted Answer · 2012-02-20 17:46:52Z

1

$coin_ID = mysql_insert_id();

answered Feb 20, 2012 at 17:46

Zac

1,6372 gold badges13 silver badges15 bronze badges

Comments

Borealid · Accepted Answer · 2012-02-20 17:45:09Z

0

Try using LAST_INSERT_ID() via the PHP mysql_insert_id function.

answered Feb 20, 2012 at 17:45

Borealid

99.4k9 gold badges112 silver badges124 bronze badges

4 Comments

Kitsune Over a year ago

Is this function actually safe in the face of there being another thread/process that INSERTs into the database between when the current context inserts, and then runs this function? If not, seems like that'll lead to subtle corruptions/errors that are very hard to track down!

Borealid Over a year ago

@Kitsune LAST_INSERT_ID is safe in a transaction, and mysql_insert_id is always safe (for PHP code which doesn't share a single DB connection between threads). See stackoverflow.com/questions/1808337/… .

Your Common Sense Over a year ago

@Kitsune please don't take mysql developers as complete fools.

Kitsune Over a year ago

@Col.Shrapnel It has nothing to do with taking them for fools, but of exactly what that function does. According to that other question, if the same connection is shared among multiple threads and transactions are not used, it WILL return the wrong data in the scenario I outlined. That's important information to have.

Collectives™ on Stack Overflow

How to insert and select data in the same SQL query?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related