Php Mysql - Get value auto increment before and insert it to another column

Question

I have a table like

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

I want insert some value to name column like

id|name
1 |1_myvalue
2 |2_myvalue
...

This table has multi users can insert to that. What can I do to make that thanks

$r = mysqli_query($conn, "INSERT INTO `test`(`name`) VALUES ('_myvalue')");

auto increment value is generated only at the time of insert so you cannot get it before insert.but there is a workaround for you check this post (mysql, php) How to get auto_increment field value before inserting data? — bansi
– bansi, Commented Aug 14, 2013 at 3:56
possible duplicate of stackoverflow.com/questions/2251463/… — bansi
– bansi, Commented Aug 14, 2013 at 3:56
There's no safe way to do it! The most you could do is obtaining the latest registered id by calling last_insert_id() + 1 and locking table before you record a new record. — felipsmartins
– felipsmartins, Commented Aug 14, 2013 at 3:57
Why is there no safe way to do it? there are many ways to do it — Hanky Panky
– Hanky Panky, Commented Aug 14, 2013 at 4:06

Hanky Panky · Accepted Answer · 2013-08-14 04:07:33Z

4

Simple workaround:
Save the record, get the inserted record's id, update it to the proper value

$r = mysqli_query($conn, "INSERT INTO `test`(`name`) VALUES ('myvalue')");
//1. save the record

$id=mysqli_insert_id($conn));
//2.  get it's id

mysqli_query($conn, "UPDATE `test` set `name` = $id"."'_myvalue' where id=$id limit 1");
//3. update the record appending correct id to the value as desired

edited Aug 14, 2013 at 4:07

answered Aug 14, 2013 at 3:59

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

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

4 Comments

LookAtMeNow Over a year ago

That's working But, if multi users insert at the same time or another way, is that correct?

Hanky Panky Over a year ago

Yes why not? for most of the active website where are always multiple users doing stuff at the same time, that doesn't confuse MySQL into believing things otherwise. Why complicate things

LookAtMeNow Over a year ago

b/c I want store my upload file, and the id is sole then the uploaded file will not coincidence :) thanks u

Hanky Panky Over a year ago

Shouldn't be a problem

Collectives™ on Stack Overflow

Php Mysql - Get value auto increment before and insert it to another column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related