Home > Archive > PHP SQL > November 2004 > Insert value in related tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Insert value in related tables
|
|
| Vincent Lascaux 2004-11-06, 3:55 pm |
| Hello,
I have two related tables : Reports and Lines that are linked (a report may
be several line long). So Lines table contains a field called KeyReport, and
Reports contains an auto generated key called KeyReport also.
Now I want to insert a new report. I insert an entry in the reports table,
and then I want to insert values in the Lines table. Is there a standard SQL
way to insert the Lines with the good KeyReport ?
I'm using the odbc_ functions, but may have to change the database (so I may
have to use the my sql functions, or any other I dont know ?) so that's why
I would like something as portable as possible...
--
Vincent
| |
| Andy Hassall 2004-11-06, 3:55 pm |
| On Sat, 6 Nov 2004 15:54:59 -0000, "Vincent Lascaux" <nospam@nospam.org> wrote:
>I have two related tables : Reports and Lines that are linked (a report may
>be several line long). So Lines table contains a field called KeyReport, and
>Reports contains an auto generated key called KeyReport also.
>
>Now I want to insert a new report. I insert an entry in the reports table,
>and then I want to insert values in the Lines table. Is there a standard SQL
>way to insert the Lines with the good KeyReport ?
>
>I'm using the odbc_ functions, but may have to change the database (so I may
>have to use the my sql functions, or any other I dont know ?) so that's why
>I would like something as portable as possible...
If you're after the autogenerated key value, that's could be tricky to do
portably since automatically generated keys work in widely different ways
between databases, and I can't spot anything in the ODBC extension that
abstracts that into a common call. This is probably a place where you will have
to put some database-specific code in.
--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
| |
| Vincent Lascaux 2004-11-06, 8:56 pm |
| > If you're after the autogenerated key value, that's could be tricky to do
> portably since automatically generated keys work in widely different ways
> between databases, and I can't spot anything in the ODBC extension that
> abstracts that into a common call. This is probably a place where you will
> have
> to put some database-specific code in.
Could you point me to some document on how to do this with the standard
databases (SQL Server, Oracle, MySQL...) ?
Thank you
--
Vincent
| |
|
| Vincent Lascaux wrote:
> Hello,
>
> I have two related tables : Reports and Lines that are linked (a report may
> be several line long). So Lines table contains a field called KeyReport, and
> Reports contains an auto generated key called KeyReport also.
>
> Now I want to insert a new report. I insert an entry in the reports table,
> and then I want to insert values in the Lines table. Is there a standard SQL
> way to insert the Lines with the good KeyReport ?
>
> I'm using the odbc_ functions, but may have to change the database (so I may
> have to use the my sql functions, or any other I dont know ?) so that's why
> I would like something as portable as possible...
>
Its going to have to be database specific. I believe you can retrieve
the ID from a newly insert MySQL row as a variable and then use that in
your remaining insert statements. Oracle on the other hand has a
sequence that you can either select from to get a key value and then
apply it to all appropriate records however if you are using a trigger
to apply the key value to inserted records automatically then your going
to have to work a little harder.
J
| |
|
| JAS wrote:
>
> Its going to have to be database specific. I believe you can retrieve
> the ID from a newly insert MySQL row as a variable and then use that in
> your remaining insert statements. Oracle on the other hand has a
> sequence that you can either select from to get a key value and then
> apply it to all appropriate records however if you are using a trigger
> to apply the key value to inserted records automatically then your going
> to have to work a little harder.
>
> J
To add to this, in Oracle it is possible to insert via a view and
populate multiple tables in one go. It might not lend itself to you
one-to-many records but hey - it could come in useful sometime.
J
| |
| Vincent Lascaux 2004-11-07, 8:56 am |
| > Its going to have to be database specific. I believe you can retrieve the
> ID from a newly insert MySQL row as a variable and then use that in your
> remaining insert statements. Oracle on the other hand has a sequence that
> you can either select from to get a key value and then apply it to all
> appropriate records however if you are using a trigger to apply the key
> value to inserted records automatically then your going to have to work a
> little harder.
Thank you for your detailed answer. I'm surprised that inserting values in
two related tables (something that shold be very basic for a relational
database) is so complex !
--
Vincent
| |
| Ilija Studen 2004-11-07, 8:56 am |
| Vincent Lascaux wrote:
>
>
> Thank you for your detailed answer. I'm surprised that inserting values in
> two related tables (something that shold be very basic for a relational
> database) is so complex !
>
It isn't complex. It is just different from DB to DB :)
If you use MySQL you can insert report like this (PHP example):
// ====
mysql_query('INSERT INTO reports (...) VALUES (...)');
// Return last generated insert ID, works only for auto_increment pkeys
$rep_id = mysql_insert_id();
for($i = 0; $i < count($lines); $i++) {
mysql_query('INSERT INTO lines (key, ...) VALUES ('$rep_id', ...)');
}
// ===
As you can see it is not so complex.
| |
| Hilarion 2004-11-07, 8:56 am |
| > Could you point me to some document on how to do this with the standard databases (SQL Server, Oracle, MySQL...) ?
In SQL Server there are @@IDENTITY, SCOPE_IDENTITY( ) and
IDENT_CURRENT('table_name') to retrieve last generated autoincrement
(identity) value respectively for whole last statement generated operations
(including triggered operations), only direct statement operations
(not including triggered operations), and for given table.
Most useful is SCOPE_IDENTITY() (you have to call it in the same
batch as the INSERT statement).
In Oracle there's RETURNING clause, which is best for retrieving
any values set by DB (not only sequence values).
I do not use MySQL, but there's PHP MySQL function 'mysql_insert_id', which
gives last autoincrement value generated in given connection.
Hilarion
| |
| Hilarion 2004-11-08, 8:55 am |
| > I'm surprised that inserting values in two related tables (something that shold be very basic for a relational database) is so
> complex !
Inserting is not complexed. The problem is that there's nothing about
autoincrement / identity columns etc. in SQL standard, so each DB
engin has it's own solution for this.
If you do not use this autonumbering features, then it's almost same
in all DBs:
INSERT INTO main_table ( some_key_id, some_data )
VALUES ( 1234, 'my data to key 1234, which I created myself' );
INSERT INTO slave_table ( some_slave_id, master_id, another_data )
VALUES ( 15, 1234, 'my "slave" data with myself generated slave key 15, for master key 1234' );
Hilarion
| |
| Vincent Lascaux 2004-11-08, 8:57 pm |
| > If you do not use this autonumbering features, then it's almost same
> in all DBs:
>
> INSERT INTO main_table ( some_key_id, some_data )
> VALUES ( 1234, 'my data to key 1234, which I created myself' );
>
> INSERT INTO slave_table ( some_slave_id, master_id, another_data )
> VALUES ( 15, 1234, 'my "slave" data with myself generated slave key 15,
> for master key 1234' );
I agree, but how can you not use autonumbering feature ? unless the key has
some meaning...
I find it strange that SQL doesn't define a standard auto numbering
feature... I think its really a lack in the language
--
Vincent
| |
| Hilarion 2004-11-10, 8:56 pm |
| > I agree, but how can you not use autonumbering feature ? unless the key has some meaning...
> I find it strange that SQL doesn't define a standard auto numbering feature... I think its really a lack in the language
Main problem - as I see it - is not in autonumbering usage standards, but
in general autovalues retrieving standard (eg. values actually inserted
or updated may differ from initial values, cause triggers can modify
them before they "reach the table"). Personaly I like the way that Oracle
gives, which allows getting all this, not only autonumbers.
Hilarion
|
|
|
|
|