Capture and insert an auto incremented value

[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:

--
-- 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();” ):

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

[Barand]

perhaps

@VAL := LAST_INSERT_ID();

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

 

[rick.emmet]

Hi Barand,

Thanks so much for your reply, this is very helpful!

Cheers,

Rick