Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

mysql insert auto increment vaule from one table to another

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
tomjenkins12
Asked:
tomjenkins12
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kaushikdebCommented:
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
 
Ray PaseurCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
tomjenkins12Author Commented:
I am using  innodb. if that helps
0
 
tomjenkins12Author Commented:
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
 
tomjenkins12Author Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tomjenkins12Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tomjenkins12Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tomjenkins12Author Commented:
AWESOME THANKS. i WAS USING THE EXAMPLE I SAW I GUESS ITS FOR SOME ELSE. THANK YOU VERY MUCH
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now