Jump to content


Photo

Retrieving insert id


  • Please log in to reply
8 replies to this topic

#1 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 11 September 2006 - 02:55 PM

Hi Guys 'n Gals...

Just a quicky hopefully.

I run an insert query like so..

	INSERT INTO
		`image`
		(
		`image_id` ,
		`image_path` ,
		`image_type` ,
		`image_dimension` ,
		`image_size` ,
		`image_alt` ,
		`image_title` ,
		`image_caption` ,
		`image_longdesc`
		) 
	VALUES
		(
		NULL ,
		'xxx
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx'
		),
		(
		NULL ,
		'xxx
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx'
		),
		(
		NULL ,
		'xxx
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx',
		'xxx'
		)

It matters not about 'xxx' at the moment.  What I wish to achieve is to grab all the id's that query creates. mysql_insert_id returns the last but I want a list of them all...

Any help much appreciated.



follow me on twitter @PHPsycho

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 11 September 2006 - 03:20 PM

I don't know of any way to get a list of ids created after using an INSERT with multiple VALUES.

Unless you're inserting a great deal of information at one time I don't think you'll notice much of a performance difference when using single INSERTs in a loop, using mysql_insert_id() to retrieve the ids versus one with multiple VALUES.

If you do notice a difference, post and let us know.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 05:43 PM

I thought there was a difference in when the indexes were updated... I'll have to profile it to double-check.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 11 September 2006 - 05:49 PM

I thought there was a difference in when the indexes were updated... I'll have to profile it to double-check.

EDIT: Ignore the following comment.

There may be, but how do you determine whether the indexes that last got updated are from this connection doing an insert or another connection doing an insert.

Maybe an md5hash of all the info concatenated with microtime a random number and an incremented value inserted would work to keep track, but I don't think there's enough of a reason in this instance to try unorthodox methods.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 06:37 PM

Well, in a controlled environment, one would expect to see multi-valued INSERT statements scall well will a growing number of indexes, while individual INSERT statement should slow down noticeably.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 11 September 2006 - 06:53 PM

Nevermind what I said. I misinterpreted what you said and only realized that after seeing that the misinterpretation didn't make sense either.

Hence, my own comment doesn't make sense.

The mysql docs do say that the multiple value insert is faster btw. Although, there was a time I tested it and noticed no real difference.That was some time ago and perhaps I made an error somewhere or perhaps used a bad test.

http://dev.mysql.com...sert-speed.html

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 07:31 PM

Right, I forgot about that refman page.  The take-home messages from that page, IMHO:

1) You have to open the table once per statement, that doesn't scale well otherwise
2) You have to connect every time too -- also, bad for lots of statements
3) Turning off keys is good, less overhead
4) Locking the tables is also good, less overhead
5) Using LOAD DATA INFILE is _insanely_ faster -- personal experience on 50K bulk insert
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 12 September 2006 - 12:53 PM

So how does all this help me grab the list of id's inserted?

I coudl concievabley have 50 record sets inserted with one query!!!!
follow me on twitter @PHPsycho

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 September 2006 - 01:39 PM

True... the easiest way is simply to add another column and mark it accordingly, and fetch the UIDs later.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users