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********@myoddweb.com> wrote in message
news:37*************@individual.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********@myoddweb.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********@myoddweb.com> wrote in message
news:37*************@individual.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

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

Why would you say that? I can see good reasons to have a split table.
For example on section contains an (int)ID while another contains some text
related to that ID and again another table that contains images related to
that ID and so on.

But if you have a link I would be interested to have a look at it.

Simon

[NC]

simon wrote:

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?

Assuming `TABLE_A`.`ID` is an AUTO_INCREMENT column, you can
obtain that ID by calling mysql_insert_id():

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

Cheers,
NC

[Matt Mitchell]

"simon" <sp********@myoddweb.com> wrote in message
news:37*************@individual.net...

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...

Why would you say that? I can see good reasons to have a split table.
For example on section contains an (int)ID while another contains some
text related to that ID and again another table that contains images
related to that ID and so on.

But if you have a link I would be interested to have a look at it.

Simon

Sounds like you're over-normalising your data, at least maybe a little.

Couldn't the text relating to the id be stored in the same table, if it's
part of some kind of "profile" information?
Ditto with the pictures (although I have to admit I normally store info on
images in a special table, so I can see a case for this)

Certainly, any information that relates to the id, and doesn't depend on any
other data, probably ought to be in the same table. This will also help
your performance, or should do, as it will reduce the number of joins that
you need.

That said, if it's a question of adding a user, and then returning the new
user id, or setting some fields afterwards, you could use insertid.

If you're adding data to another table that's solely dependent on the first
table's primary key, it might be worth thinking about whether that data
actually belongs in the first table too...

Matt

[simon]

> Sounds like you're over-normalising your data, at least maybe a little.

it is hard to be clear in a newsgroup :)

That said, if it's a question of adding a user, and then returning the new
user id, or setting some fields afterwards, you could use insertid.
the reason for split tables is more because the data is not always needed so
the query is more efficient is all I need is to get the ID, (and whatever
other basic info).
The other reason is because the other table can store past information.

For example, lets say I have 1 ID, very easy to SELECT.
Then I can go to the "Text data" table and get all the data related to that
ID, (if I want).
There could be more than one item for each ID.

Same as pictures, one ID could have 10, 20 or many pictures related to it...
all attached to one ID.

If you're adding data to another table that's solely dependent on the
first table's primary key, it might be worth thinking about whether that
data actually belongs in the first table too...

Simon

[Matt Mitchell]

"simon" <sp********@myoddweb.com> wrote in message
news:37*************@individual.net...

the reason for split tables is more because the data is not always needed
so the query is more efficient is all I need is to get the ID, (and
whatever other basic info).
The other reason is because the other table can store past information.

For example, lets say I have 1 ID, very easy to SELECT.
Then I can go to the "Text data" table and get all the data related to
that ID, (if I want).
There could be more than one item for each ID.

Same as pictures, one ID could have 10, 20 or many pictures related to
it... all attached to one ID.

OK, so as long as there isn't a consistent 1-to-1 correspondence, yes,
you'll need separate tables.

mysql_insert_id() will give you the last autonumber id; I don't know if this
is transaction-safe or not though (checking mysql.com will probably tell you
this).

If it *isn't* you may be able to work around it by using some unique field
in the table you just inserted into.

For example:

INSERT INTO users (username, usertype) VALUES ('foo', 'administrator');

// check mysql_error() to make sure that you didn't get a duplicate key
error

SELECT userautoid FROM users WHERE username='foo';

Then you have your userid number.

Of course, it may not actually be necessary to insert the data into the
other tables as part of the same operation...

Matt