473,225 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

Inserting into two table

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
Jul 17 '05 #1
14 1987
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.
Jul 17 '05 #2
>>
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
Jul 17 '05 #3
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
Jul 17 '05 #4
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

Jul 17 '05 #5
>>

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
Jul 17 '05 #6
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
Jul 17 '05 #7
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.
Jul 17 '05 #8
"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/
Jul 17 '05 #9

"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
Jul 17 '05 #10
> 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
Jul 17 '05 #11
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

Jul 17 '05 #12

"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

Jul 17 '05 #13
> 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
Jul 17 '05 #14

"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
Jul 17 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish,...
3
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
1
by: ing42 | last post by:
I have a problem with inserting records into table when an indexed view is based on it. Table has text field (without it there is no problem, but I need it). Here is a sample code: USE test GO...
3
by: rcoco | last post by:
Hi, I want to share this problem. I have a datagrid that will help me Insert data into sql database. So I made a button On my form so that when I press the button a new row on datagrid should be...
1
by: madhuxml82 | last post by:
Dear Forum Members, I have generated an XML Schema and a Table of XMLType referencing the XML Schema. Now When I am Inserting the Data into the Table. I am getting the Error 0RA-30937: Error is...
5
by: dos360 | last post by:
Hello, I have two tables, one is a list of activities, the other a list of participants. I want to insert one record in the activities table and then using its identity column as foreign key, I...
2
by: AlexanderDeLarge | last post by:
Hi! I got a problem that's driving me crazy and I'm desperately in need of help. I'll explain my scenario: I'm doing a database driven site for a band, I got these tables for their discography...
5
by: rando1000 | last post by:
Okay, here's my situation. I need to loop through a file, inserting records based on a number field (in order) and if the character in a certain field = "##", I need to insert a blank record. ...
5
by: billelev | last post by:
I have a large array of data (1000 x 40 x 3) that I am inserting into a database table. It is incredibly slow, and so I was wondering if there is a quicker way of inserting array data into a table....
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.