Solved

mysql insert auto increment vaule from one table to another

Posted on 2010-09-09
13
691 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 143

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 110

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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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
 
LVL 143

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 143

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 143

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

Upcoming Webinar: Percona XtraDB Cluster 6/21 10am

Join Percona’s MySQL Practice Manager Kenny Gryp and QA Engineer, Ramesh Sivaraman as they present Percona XtraDB Cluster, Galera Cluster, MySQL Group Replication on Wednesday, June 21, 2017 at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

734 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