Inserting into two table

[simon]

Hi,

I have two tables
Table A = {ID, Item}
Table B = {ID, IDA, subItem}

Where ID auto increment.

INSERT INTO TABLE_A ('items') values ('a')

If I did an insert into Table A how can I find the ID of the item I just
inserted?
so I can do
INSERT INTO TABLE_A ( IDA, 'items') values ( 'IDA', 'a')

many thanks

Simon

[noSpam]

simon wrote:

Hi,

I have two tables
Table A = {ID, Item}
Table B = {ID, IDA, subItem}

Where ID auto increment.

INSERT INTO TABLE_A ('items') values ('a')

If I did an insert into Table A how can I find the ID of the item I just
inserted?
so I can do
INSERT INTO TABLE_A ( IDA, 'items') values ( 'IDA', 'a')

many thanks

Simon

Whoops.

Thats a transaction - are you using MySQL or another transaction capable
database ?

If you aren't then you have a problem - two inserts in two tables
requires a transaction for data integrity.

[simon]

>>

Whoops.

Why?

Thats a transaction - are you using MySQL or another transaction capable
database ?
I am using MySQL

If you aren't then you have a problem - two inserts in two tables requires
a transaction for data integrity.

I am not sure I follow, if I know the ID of the of the item I inserted in
Table A then I can use it for table B
I don't need to know the auto increment value of Table B, all I need is the
value of A.

Simon

[noSpam]

simon wrote:

Hi,

I have two tables
Table A = {ID, Item}
Table B = {ID, IDA, subItem}

Where ID auto increment.

INSERT INTO TABLE_A ('items') values ('a')

If I did an insert into Table A how can I find the ID of the item I just
inserted?
so I can do
INSERT INTO TABLE_A ( IDA, 'items') values ( 'IDA', 'a')

many thanks

Simon

What you need is something along the lines of:-

pseudo code:

BEGIN TRANSACTION
if insert into table a succeeds then {
insert into table b
if insert into table b succeeds then
commit
else
rollback
}
else {
rollback
}
END TRANSACTION

[Kevin]

Use mysql_insert_id ():

http://us4.php.net/manual/en/functio...-insert-id.php

"simon" <sp********@myo ddweb.com> wrote in message
news:37******** *****@individua l.net...

Hi,

I have two tables
Table A = {ID, Item}
Table B = {ID, IDA, subItem}

Where ID auto increment.

INSERT INTO TABLE_A ('items') values ('a')

If I did an insert into Table A how can I find the ID of the item I just
inserted?
so I can do
INSERT INTO TABLE_A ( IDA, 'items') values ( 'IDA', 'a')

many thanks

Simon

[simon]

>>

What you need is something along the lines of:-

pseudo code:

BEGIN TRANSACTION
if insert into table a succeeds then {
insert into table b
if insert into table b succeeds then
commit
else
rollback
}
else {
rollback
}
END TRANSACTION

I see, but what I was looking for was the ID of newly, (successfully),
inserted item in Table A.

Simon

[noSpam]

simon wrote:

What you need is something along the lines of:-

pseudo code:

BEGIN TRANSACTION
if insert into table a succeeds then {
insert into table b
if insert into table b succeeds then
commit
else
rollback
}
else {
rollback
}
END TRANSACTION

I see, but what I was looking for was the ID of newly, (successfully),
inserted item in Table A.

Simon

No problem - Kevin has given you the answer to that. but if you are
using MySQL then you need to use INNODB tables not iSAM tables - ISAM
tables don't support transactions, and you are proposing a two table
operation which is essentially atomic. In other words, two inserts on
two tables both of which have to succeed to ensure data integrity

[Jan Pieter Kunst]

simon wrote:

Hi,

I have two tables
Table A = {ID, Item}
Table B = {ID, IDA, subItem}

Where ID auto increment.

INSERT INTO TABLE_A ('items') values ('a')

If I did an insert into Table A how can I find the ID of the item I just
inserted?
so I can do
INSERT INTO TABLE_A ( IDA, 'items') values ( 'IDA', 'a')

many thanks

Simon

In MySQL:

SELECT LAST_INSERT_ID( )

See this page for detailed information:
<http://dev.mysql.com/doc/mysql/en/information-functions.html>

JP

--
Sorry, <de*****@cauce. org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

[Jerry Sievers]

"simon" <sp********@myo ddweb.com> writes:

Hi,

I have two tables
Table A = {ID, Item}
Table B = {ID, IDA, subItem}

Where ID auto increment.

INSERT INTO TABLE_A ('items') values ('a')

If I did an insert into Table A how can I find the ID of the item I just
inserted?
so I can do
INSERT INTO TABLE_A ( IDA, 'items') values ( 'IDA', 'a')

Straight from the PHP manual;

int mysql_insert_id ( [resource link_identifier])

mysql_insert_id () returns the ID generated for an AUTO_INCREMENT column by the
previous INSERT query using the given link_identifier . If link_identifier
isn't specified, the last opened link is assumed.

....You'd be smart doing the two inserts inside a transaction block
also.

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

[Matt Mitchell]

"simon" <sp********@myo ddweb.com> wrote in message
news:37******** *****@individua l.net...

Whoops.

Why?

Thats a transaction - are you using MySQL or another transaction capable
database ?

I am using MySQL

If you aren't then you have a problem - two inserts in two tables
requires a transaction for data integrity.

I am not sure I follow, if I know the ID of the of the item I inserted in
Table A then I can use it for table B
I don't need to know the auto increment value of Table B, all I need is
the value of A.

Simon

1 - It's always worth reading a little bit. You might notice that
http://php.net/mysql_insert_id has some pertinent information for you.

2 - As pointed out, you might want to think about transactions. IIRC, mysql
offers a SELECT INSERT_ID; or something similar (the mysql manual online
will tell you this) that you could use as part of a transaction to make
things work ok.

3 - There's a certain school of thought that says you might need to rethink
your data/application design to avoid this situation being necessary if
possible...

Matt