Capture and insert an auto incremented value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Capture and insert an auto incremented value

Post by rick.emmet »

Hi Everyone,
I'm trying to help out a classmate from a Javascript class I'm taking. He created a website with a MySQL database and had problems with foreign constraints. About 5 years ago, I had been using phpMyAdmin to do some testing of INSERT statements. I was able to insert a “user name” and other personal information into a “user” table, and then take the “user_id” (auto incremented) and insert that into a second table where there was a relationship between the two tables.

As I recall, there was a built-in MySQL function that supported this. I have looked for it and all I have found is the LAST_INSERT_ID() function. It doesn't look quite right to me, and the simple insert statement I ran throws and error.

Here's the SQL to create the database:

Code: Select all

--
-- Database : test
--

DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE test;


SET FOREIGN_KEY_CHECKS=0;

--
-- table structure for `clients`    
--

CREATE TABLE users (
  user_id int NOT NULL auto_increment,		
  email Varchar(50) NOT NULL default '',
  user_name Varchar(16) NOT NULL default '',
  password Varchar(40) NOT NULL default '',
  user_group Varchar(16) NOT NULL default 'User',
  first_name Varchar(25) NOT NULL default '',
  last_name Varchar(25) NOT NULL default '',
  street_nmb int(7) NOT NULL default '0',		
  st_name Varchar(50) NOT NULL default '',
  city Varchar(50) NOT NULL default '',
  state ENUM ('AA','AE','AL','AK','AP','AR','AS','AZ','CA','CO','CT','DC','DE','FL','FM','GA','GU','HI','ID','IL','IN','IA','KS','KY','LA','MA','ME','MD','MH',
    'MI','MN','MO','MP','MS','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','PR','PW','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY') NOT NULL,
  zipcode Mediumint(5) Unsigned NOT NULL default '00000',
  reg_date DateTime,
  last_edit DateTime NOT NULL,
  last_editby Varchar(20) NOT NULL default '',
  PRIMARY KEY  (user_id)
) Engine=InnoDB;

--
-- table structure for `otherTb`
--

CREATE TABLE otherTb (
  user_id Int NOT NULL default '0',
  instance_id int NOT NULL auto_increment,
  category_id Tinyint(2) Zerofill NOT NULL default '00',
  insert_date DateTime NOT NULL,	
  PRIMARY KEY  (instance_id),
  KEY (user_id),
  FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) Engine=InnoDB;


SET FOREIGN_KEY_CHECKS=1;
And here is the insert statement that is failing (the MySQL engines says that I have a syntax error on the line “VAL = LAST_INSERT_ID();” ):

Code: Select all

SET AUTOCOMMIT=0;

INSERT INTO `users` (`user_id`, `email`, `user_name`, `password`, `user_group`, `first_name`, `last_name`, `street_nmb`, `st_name`, `city`, `state`, `zipcode`, `reg_date`, `last_edit`, `last_editby`) VALUES
(NULL, 'henry@localhost', 'henryhill', '285d0c707f9644b75e1a87a62f25d0efb56800f0', 'Forum Type Three', 'henry', 'hill', 9876, 'adams st', 'youngstown', 'OH', 54321, NOW(), NOW(), 'henryhill');

VAL = LAST_INSERT_ID();

INSERT INTO `otherTb` (VAL, `instance_id`, `category_id`,) VALUES (NULL, NULL, 04);

SET AUTOCOMMIT=1;
As I recall, I used transactions for this process all those years ago, and the general structure was very much like what I have above. It may be that there was a special variable that has to be used with the LAST_INSERT_ID() function (and that would in turn be used in the following insert statement), but I couldn't find one at the MySQL site. Does anyone have an idea of what this built in function (or perhaps the special variable) I'm trying to use is called? Sorry that my query is so “nebulous” but I can't remember stuff I did that long ago!
Cheers,
Rick
Last edited by requinix on Fri May 31, 2013 4:47 pm, edited 1 time in total.
Reason: breaking the long line of states
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Capture and insert an auto incremented value

Post by requinix »

If you only need the ID one time then put the function right in the query. As a value, not as a column name.

Code: Select all

INSERT INTO `users`...;

INSERT INTO `otherTb` (`user_id`, `instance_id`, `category_id`) VALUES (LAST_INSERT_ID(), NULL, 4);
Otherwise you need a variable.

Code: Select all

SET @userid = LAST_INSERT_ID();
INSERT INTO `otherTb` (`user_id`, `instance_id`, `category_id`) VALUES (@userid, NULL, 4);
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Re: Capture and insert an auto incremented value

Post by rick.emmet »

Hi requinix,
Thank you so much for your reply! The use of a SET statement looks exactly like what I had used way back when. How did I forget SET (duh)? So, I plugged the code into a query and it fails. It looks perfectly fine to me:

Code: Select all

SET AUTOCOMMIT=0;

INSERT INTO `users` (`user_id`, `email`, `user_name`, `password`, `user_group`, `first_name`, `last_name`, `street_nmb`, `st_name`, `city`, `state`, `zipcode`, `reg_date`, `last_edit`, `last_editby`) VALUES
(NULL, 'henry@localhost', 'henryhill', '285d0c707f9644b75e1a87a62f25d0efb56800f0', 'Forum Type Three', 'henry', 'hill', 9876, 'adams st', 'youngstown', 'OH', 54321, NOW(), NOW(), 'henryhill');

SET @userid = LAST_INSERT_ID();

INSERT INTO `otherTb` (`user_id`, `instance_id`, `category_id`,) VALUES (@userid, NULL, 04);

SET AUTOCOMMIT=1;
This is a new installation of XAMPP and I have not touched the my.ini file. Is there something that I have to turn on in that configuration file?
Thanks again,
Rick
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Capture and insert an auto incremented value

Post by requinix »

Take a look at the second INSERT query. It has a typo.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Capture and insert an auto incremented value

Post by McInfo »

I'll just leave this here.

Code: Select all

sha1('PassWord1') == '285d0c707f9644b75e1a87a62f25d0efb56800f0'
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Capture and insert an auto incremented value

Post by requinix »

McInfo wrote:I'll just leave this here.
To be honest it might not be obvious (enough) why that's a bad thing...
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Re: Capture and insert an auto incremented value

Post by rick.emmet »

Hi Everyone,
Boy, I hate when I do that!! I didn't even finish writing the fields that the table has. Guess I was in a bigger hurry than I thought. Thank you very much for your help!!!
Cheers,
Rick
Post Reply