Page 1 of 1

Capture and insert an auto incremented value

Posted: Fri May 31, 2013 3:33 pm
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

Re: Capture and insert an auto incremented value

Posted: Fri May 31, 2013 4:50 pm
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);

Re: Capture and insert an auto incremented value

Posted: Sat Jun 01, 2013 12:47 pm
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

Re: Capture and insert an auto incremented value

Posted: Sat Jun 01, 2013 5:41 pm
by requinix
Take a look at the second INSERT query. It has a typo.

Re: Capture and insert an auto incremented value

Posted: Sun Jun 02, 2013 9:31 pm
by McInfo
I'll just leave this here.

Code: Select all

sha1('PassWord1') == '285d0c707f9644b75e1a87a62f25d0efb56800f0'

Re: Capture and insert an auto incremented value

Posted: Mon Jun 03, 2013 5:10 am
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...

Re: Capture and insert an auto incremented value

Posted: Wed Jun 05, 2013 4:52 pm
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