Solved

mysql insert auto increment vaule from one table to another

Posted on 2010-09-09
13
685 Views
Last Modified: 2012-05-10
I have four tables each table has a auto increment. I am having a hard time  figuring out how to add the auto increment values from the three tables to the relationship table when a new record is entered.

heres a screen cap of the relationship table



  relationship table
0
Comment
Question by:tomjenkins12
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33637745
in MySQL, you can fetch the last generated ID by your session, after the INSERT, using last_insert_id function:http://dev.mysql.com/doc/refman/5.0/en/information-functions.htmlin PHP, you can call mysql_insert_id function likely:http://php.net/manual/en/function.mysql-insert-id.php
0
 
LVL 1

Expert Comment

by:kaushikdeb
ID: 33637787
There must be some event which would trigger the relationship to be formed among these ids, right? I think you need to manually (i.e. insert query) insert the foreign key ids to the relationship table when that event is triggered. There is no automated way of doing it otherwise (which i know of ). If there is some logic behind the relationships, you can use database functions/procedures, but that too will mean writing a query to do the inserts.

Hope this helps.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33637788
Doesn't INNODB have a way of making this automatic?

Just a note going forward, JPG files are OK - the BMP image above is 5 megabytes!
0
 

Author Comment

by:tomjenkins12
ID: 33637870
I am using  innodb. if that helps
0
 

Author Comment

by:tomjenkins12
ID: 33638241
angelIII i looked at those two sites and my question is what happens if two people are inserting a new record at the same time? so to prevent this i need to make a session ?

do you have a example you can show me ?

I tried this, but it gave me a error

INSERT INTO `client_db`.`obligee` (`Obligee`, `OBLIGEE_ADDRESS`, `OBLIGEE_CITY`, `OBLIGEE_STATE`, `OBLIGEE_ZIP`, `OBLIGEE_CONTACT`, `OBLIGEE_PHONE`, `OBLIGEE_FAX`, `OBLIGEE_EMAIL`, `OBLIGEE_ID`) VALUES ('test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'terst3', NULL);
 UPDATE obligee SET OBLIGEE_ID=LAST_INSERT_ID(id+1);
 SELECT LAST_INSERT_ID();
0
 

Author Comment

by:tomjenkins12
ID: 33638273

I changed my query around and now i get this error

SQL query:

UPDATE obligee SET OBLIGEE_ID = LAST_INSERT_ID( OBLIGEE_ID +1 ) ;

MySQL said: Documentation
#1062 - Duplicate entry '2' for key 'PRIMARY'
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33638318
the functions are relative to the connection session, no the php session

now, those functions presmnume the field is auto_increment ...
as from then, the insert will initially fill the value, no need to "update" again.
you only can/will use that value when you insert into other tables, with this field as foreign key
0
 

Author Comment

by:tomjenkins12
ID: 33638492
ok i changed the query and i can get id value, but when i try to add it to the relate table i get this error


relate is where the values of the other tables are stored obligee is where i  insert the record. Keep in mind i have four other tables could this be not working because i need to to dos this with all four tables?  
I wanted to see if this would work with one table before i did it to all the tables
Error

SQL query:

UPDATE relate SET relate.OBLIGEE_ID = LAST_INSERT_ID( OBLIGEE_ID +1 ) ;

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`client_db`.`relate`, CONSTRAINT `relate_ibfk_2` FOREIGN KEY (`OBLIGEE_ID`) REFERENCES `obligee` (`OBLIGEE_ID`) ON DELETE CASCADE ON UPDATE CASCADE)

INSERT INTO `client_db`.`obligee` (`Obligee`, `OBLIGEE_ADDRESS`, `OBLIGEE_CITY`, `OBLIGEE_STATE`, `OBLIGEE_ZIP`, `OBLIGEE_CONTACT`, `OBLIGEE_PHONE`, `OBLIGEE_FAX`, `OBLIGEE_EMAIL`, `OBLIGEE_ID`) VALUES ('test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'terst3', NULL);
 SELECT LAST_INSERT_ID();
 UPDATE relate SET relate.OBLIGEE_ID=LAST_INSERT_ID(OBLIGEE_ID+1);
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33638551
I fear that you misunderstand how the things have to work ...

can you please show data samples of the different tables of what you expect it to work?
0
 

Author Comment

by:tomjenkins12
ID: 33638658
ok i cnahed by code around to make it not a update but a insert, thank you.

it adds a record to the realte table, but it does not add the last_insert_id from obligee_id to relate.OBLIGEE_ID

INSERT INTO `client_db`.`obligee` (`Obligee`, `OBLIGEE_ADDRESS`, `OBLIGEE_CITY`, `OBLIGEE_STATE`, `OBLIGEE_ZIP`, `OBLIGEE_CONTACT`, `OBLIGEE_PHONE`, `OBLIGEE_FAX`, `OBLIGEE_EMAIL`, `OBLIGEE_ID`) VALUES ('test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'test3', 'terst3', NULL);# 1 row(s) affected.

 SELECT LAST_INSERT_ID();# Rows: 1

INSERT INTO relate SET relate.OBLIGEE_ID=LAST_INSERT_ID(relate.OBLIGEE_ID+1);# 1 row(s) affected.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33638690
INSERT INTO relate SET relate.OBLIGEE_ID=LAST_INSERT_ID(relate.OBLIGEE_ID+1);# 1 row(s) affected.

must be:

INSERT INTO relate SET relate.OBLIGEE_ID=LAST_INSERT_ID();# 1 row(s) affected.

adding +1 does not make any sense ...
0
 

Author Comment

by:tomjenkins12
ID: 33638923
AWESOME THANKS. i WAS USING THE EXAMPLE I SAW I GUESS ITS FOR SOME ELSE. THANK YOU VERY MUCH
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now