Re: [PHP] Retrieve value of newly inserted row.
I GOT IT! WooHoo!
Thanks to a co-worker, this problem has been solved! I was using
scope_identity incorrectly.
This is how you should use it:
INSERT INTO Table1 (
Column1,
Column2)
VALUES (
Value1,
Value2)
INSERT INTO Table2 (
Column1,
Column2)
VALUES (
scope_identity(),
Value2)
Using scope_identity() for the value in insert #2 will return the value of
the identity field just created from insert #1.
On 2/15/07, Brad Fuller <[EMAIL PROTECTED]> wrote:
> Okay, I found the correct function and below is what I have:
>
> - Show quoted text -
> $insert1 = "INSERT INTO table1 (
)
> VALUES (
)
>
> SELECT scope_identity()
>
> INSERT INTO table2 (
> credit_card_id,
> case_number,
> comments)
> VALUES (
> 'scope_identity',
> '$case',
> '$comments')";
> echo "$insert1";
> mssql_query($insert1) or die ("Query failed: />".mssql_get_last_message());
>
> echo "Insert complete";
>
> the scope_identity function is suppose to select the last inserted ID
for
> the first insert statement.
>
> When my query executes, it "appears" to go thru all the steps
> correctly...it
> inserts the first record just fine, displays the echo of my query and
> returns the echo of "Insert Complete"
>
> HOWEVER, when I go to look at the data base there is NO data inserted
into
> table2 even though my query returned that "Insert Complete" statement.
>
> Any ideas?
>
>
>
> On 2/14/07, Jim Lucas <[EMAIL PROTECTED]> wrote:
> >
> > Brad Fuller wrote:
> > >> >From looking on the web (MSDN) I found the @@identity and the
> > explanation
> > >> of
> > >> what it is, but MS's "example" is horrible and does not show a good
> > >> context
> > >> for using this function. Could you elaborate more on its use?
> > >
> > > $q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT
> > > LAST_INSERT_ID=@@IDENTITY");
> > > $r = mssql_fetch_assoc($q);
> > >
> > >
> > > HTH,
> > >
> > > Brad
> > >
> >
> > Might look at this
> >
> > http://us3.php.net/manual/en/function.mssql-query.php#46026
> >
> > --
> > Enjoy,
> >
> > Jim Lucas
> >
> > Different eyes see different things. Different hearts beat on
different
> > strings. But there are times for you and me when all such things
agree.
> >
> > - Rush
I don't think you can do multiple inserts in a single query. Or maybe you
can but the select statement after it causes the second insert not to be
run? I don't know, but I've seen several examples - each use 2 separate
insert queries.
My reply to your original message was incomplete; I apologize
$q1 = mssql_query("INSERT INTO Table1 (...) VALUES (...) SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY]");
$r = mssql_fetch_assoc($q1);
$insert_id = $r['SCOPE_IDENTITY'];
$q2 = mssql_query("INSERT INTO Table2 (...) VALUES ({$insert_id}, ...)");
RE: [PHP] Retrieve value of newly inserted row.
> Okay, I found the correct function and below is what I have:
>
> - Show quoted text -
> $insert1 = "INSERT INTO table1 (
)
> VALUES (
)
>
> SELECT scope_identity()
>
> INSERT INTO table2 (
> credit_card_id,
> case_number,
> comments)
> VALUES (
> 'scope_identity',
> '$case',
> '$comments')";
> echo "$insert1";
> mssql_query($insert1) or die ("Query failed: />".mssql_get_last_message());
>
> echo "Insert complete";
>
> the scope_identity function is suppose to select the last inserted ID for
> the first insert statement.
>
> When my query executes, it "appears" to go thru all the steps
> correctly...it
> inserts the first record just fine, displays the echo of my query and
> returns the echo of "Insert Complete"
>
> HOWEVER, when I go to look at the data base there is NO data inserted into
> table2 even though my query returned that "Insert Complete" statement.
>
> Any ideas?
>
>
>
> On 2/14/07, Jim Lucas <[EMAIL PROTECTED]> wrote:
> >
> > Brad Fuller wrote:
> > >> >From looking on the web (MSDN) I found the @@identity and the
> > explanation
> > >> of
> > >> what it is, but MS's "example" is horrible and does not show a good
> > >> context
> > >> for using this function. Could you elaborate more on its use?
> > >
> > > $q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT
> > > LAST_INSERT_ID=@@IDENTITY");
> > > $r = mssql_fetch_assoc($q);
> > >
> > >
> > > HTH,
> > >
> > > Brad
> > >
> >
> > Might look at this
> >
> > http://us3.php.net/manual/en/function.mssql-query.php#46026
> >
> > --
> > Enjoy,
> >
> > Jim Lucas
> >
> > Different eyes see different things. Different hearts beat on different
> > strings. But there are times for you and me when all such things agree.
> >
> > - Rush
I don't think you can do multiple inserts in a single query. Or maybe you
can but the select statement after it causes the second insert not to be
run? I don't know, but I've seen several examples - each use 2 separate
insert queries.
My reply to your original message was incomplete; I apologize
$q1 = mssql_query("INSERT INTO Table1 (...) VALUES (...) SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY]");
$r = mssql_fetch_assoc($q1);
$insert_id = $r['SCOPE_IDENTITY'];
$q2 = mssql_query("INSERT INTO Table2 (...) VALUES ({$insert_id}, ...)");
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Retrieve value of newly inserted row.
Okay, I found the correct function and below is what I have:
- Show quoted text -
$insert1 = "INSERT INTO table1 (
debit_card,
card_type,
card_number,
exp_date,
payment_amount,
cvv_number,
first_name,
middle_name,
last_name,
address_1,
address_2,
city,
zip_code,
zip_4,
phone_number,
fax_number,
email_address,
receipt,
comments,
date_request_received,
employee_received_call,
research_phase_date,
research_phase_user,
submit_phase_date,
submit_phase_user,
status_code,
state_code)
VALUES (
'$debit_card',
'$card_type',
'$card_number',
'$exp_date',
'$amount',
'$cvv',
'$cc_first',
'$cc_middle',
'$cc_last',
'$cc_address_1',
'$cc_address_2',
'$cc_city',
'$cc_zip',
'$cc_zip_4',
'$cc_phone_number',
'$cc_fax_number',
'$cc_email_address',
'$receipt',
'$cc_comments',
'$create_date',
'$create_user',
'$research_date',
'$research_user',
'$submit_date',
'$submit_user',
'$status_code',
'$cc_state')
SELECT scope_identity()
INSERT INTO table2 (
credit_card_id,
case_number,
comments)
VALUES (
'scope_identity',
'$case',
'$comments')";
echo "$insert1";
mssql_query($insert1) or die ("Query failed: ".mssql_get_last_message());
echo "Insert complete";
the scope_identity function is suppose to select the last inserted ID for
the first insert statement.
When my query executes, it "appears" to go thru all the steps correctly...it
inserts the first record just fine, displays the echo of my query and
returns the echo of "Insert Complete"
HOWEVER, when I go to look at the data base there is NO data inserted into
table2 even though my query returned that "Insert Complete" statement.
Any ideas?
On 2/14/07, Jim Lucas <[EMAIL PROTECTED]> wrote:
Brad Fuller wrote:
>> >From looking on the web (MSDN) I found the @@identity and the
explanation
>> of
>> what it is, but MS's "example" is horrible and does not show a good
>> context
>> for using this function. Could you elaborate more on its use?
>
> $q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT
> LAST_INSERT_ID=@@IDENTITY");
> $r = mssql_fetch_assoc($q);
>
>
> HTH,
>
> Brad
>
Might look at this
http://us3.php.net/manual/en/function.mssql-query.php#46026
--
Enjoy,
Jim Lucas
Different eyes see different things. Different hearts beat on different
strings. But there are times for you and me when all such things agree.
- Rush
Re: [PHP] Retrieve value of newly inserted row.
Brad Fuller wrote:
>From looking on the web (MSDN) I found the @@identity and the explanation
of
what it is, but MS's "example" is horrible and does not show a good
context
for using this function. Could you elaborate more on its use?
$q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT
LAST_INSERT_ID=@@IDENTITY");
$r = mssql_fetch_assoc($q);
HTH,
Brad
Might look at this
http://us3.php.net/manual/en/function.mssql-query.php#46026
--
Enjoy,
Jim Lucas
Different eyes see different things. Different hearts beat on different
strings. But there are times for you and me when all such things agree.
- Rush
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Retrieve value of newly inserted row.
> >From looking on the web (MSDN) I found the @@identity and the explanation
> of
> what it is, but MS's "example" is horrible and does not show a good
> context
> for using this function. Could you elaborate more on its use?
$q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT
LAST_INSERT_ID=@@IDENTITY");
$r = mssql_fetch_assoc($q);
HTH,
Brad
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Retrieve value of newly inserted row.
Sorry, I wasn't trying to fool you, I promise! :) Arpad, From looking on the web (MSDN) I found the @@identity and the explanation of what it is, but MS's "example" is horrible and does not show a good context for using this function. Could you elaborate more on its use? On 2/14/07, Robert Cummings <[EMAIL PROTECTED]> wrote: On Wed, 2007-02-14 at 19:34 +0100, Tim wrote: > > > > -Message d'origine- > > De : Tim [mailto:[EMAIL PROTECTED] > > > > > I hope that wasn't too confusing. > > > > http://cz2.php.net/manual/en/function.mysql-insert-id.php > > Sorry my eyes played some tricks on me ragarding mysql/mssql refer to > Robert's post on looking up "last insert id". No, no, I had the same tricksies played on me too :) Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `'
RE: [PHP] Retrieve value of newly inserted row.
On Wed, 2007-02-14 at 19:34 +0100, Tim wrote: > > > > -Message d'origine- > > De : Tim [mailto:[EMAIL PROTECTED] > > > > > I hope that wasn't too confusing. > > > > http://cz2.php.net/manual/en/function.mysql-insert-id.php > > Sorry my eyes played some tricks on me ragarding mysql/mssql refer to > Robert's post on looking up "last insert id". No, no, I had the same tricksies played on me too :) Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Retrieve value of newly inserted row.
> -Message d'origine-
> De : Tim [mailto:[EMAIL PROTECTED]
> Envoyé : mercredi 14 février 2007 19:28
> À : 'Dan Shirah'; 'php-general'
> Objet : RE: [PHP] Retrieve value of newly inserted row.
>
>
>
> > -Message d'origine-
> > De : Dan Shirah [mailto:[EMAIL PROTECTED] Envoyé : mercredi 14
> > février 2007 19:20 À : php-general Objet : [PHP] Retrieve value of
> > newly inserted row.
> >
> > Hello,
> >
> > I have a page the contains two insert statements.
> >
> >
> > $insert1 = "INSERT INTO table1 (
> > debit_card,
> > card_type,
> > card_number,
> > exp_date,
> > payment_amount,
> > cvv_number,
> > first_name,
> > middle_name,
> > last_name,
> > address_1,
> > address_2,
> > city,
> > zip_code,
> > zip_4,
> > phone_number,
> > fax_number,
> > email_address,
> > receipt,
> > comments,
> > date_request_received,
> > employee_received_call,
> > research_phase_date,
> > research_phase_user,
> > submit_phase_date,
> > submit_phase_user,
> > status_code,
> > state_code)
> > VALUES (
> > '$debit_card',
> > '$card_type',
> > '$card_number',
> > '$exp_date',
> > '$amount',
> > '$cvv',
> > '$cc_first',
> > '$cc_middle',
> > '$cc_last',
> > '$cc_address_1',
> > '$cc_address_2',
> > '$cc_city',
> > '$cc_zip',
> > '$cc_zip_4',
> > '$cc_phone_number',
> > '$cc_fax_number',
> > '$cc_email_address',
> > '$receipt',
> > '$cc_comments',
> > '$create_date',
> > '$create_user',
> > '$research_date',
> > '$research_user',
> > '$submit_date',
> > '$submit_user',
> > '$status_code',
> > '$cc_state')";
> >mssql_query($insert1) or die ("Query failed: > />".mssql_get_last_message());
> >
> >
> > $insert2 = "INSERT INTO table2 (
> > credit_card_id,
> > case_number,
> > comments)
> > VALUES (
> > 'card_id',
> > '$case',
> > '$comments')";
> > mssql_query($insert2) or die ("Query failed: > />".mssql_get_last_message());
> >
> > echo "Insert complete";
> >
> >
> >
> >
> >
> > On my second insert statement, please note "credit_card_id".
> > This is an
> > auto_increment column in table1. What I need to do is pull
> the value
> > of "credit_card_id" from the newly inserted row from
> insert1 and put
> > that value in a variable to assign it to "credit_card_id"
> in insert2.
> >
> >
> >
> > I hope that wasn't too confusing.
>
> http://cz2.php.net/manual/en/function.mysql-insert-id.php
Sorry my eyes played some tricks on me ragarding mysql/mssql refer to
Robert's post on looking up "last insert id".
Regards,
Tim
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Retrieve value of newly inserted row.
On Wed, 2007-02-14 at 13:26 -0500, Robert Cummings wrote: > On Wed, 2007-02-14 at 13:20 -0500, Dan Shirah wrote: > > Hello, > > > > On my second insert statement, please note "credit_card_id". This is an > > auto_increment column in table1. What I need to do is pull the value of > > "credit_card_id" from the newly inserted row from insert1 and put that value > > in a variable to assign it to "credit_card_id" in insert2. > > Search the web for last_insert_id() Never mind, just noticed you are using MS SQL. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Retrieve value of newly inserted row.
> -Message d'origine-
> De : Dan Shirah [mailto:[EMAIL PROTECTED]
> Envoyé : mercredi 14 février 2007 19:20
> À : php-general
> Objet : [PHP] Retrieve value of newly inserted row.
>
> Hello,
>
> I have a page the contains two insert statements.
>
>
> $insert1 = "INSERT INTO table1 (
> debit_card,
> card_type,
> card_number,
> exp_date,
> payment_amount,
> cvv_number,
> first_name,
> middle_name,
> last_name,
> address_1,
> address_2,
> city,
> zip_code,
> zip_4,
> phone_number,
> fax_number,
> email_address,
> receipt,
> comments,
> date_request_received,
> employee_received_call,
> research_phase_date,
> research_phase_user,
> submit_phase_date,
> submit_phase_user,
> status_code,
> state_code)
> VALUES (
> '$debit_card',
> '$card_type',
> '$card_number',
> '$exp_date',
> '$amount',
> '$cvv',
> '$cc_first',
> '$cc_middle',
> '$cc_last',
> '$cc_address_1',
> '$cc_address_2',
> '$cc_city',
> '$cc_zip',
> '$cc_zip_4',
> '$cc_phone_number',
> '$cc_fax_number',
> '$cc_email_address',
> '$receipt',
> '$cc_comments',
> '$create_date',
> '$create_user',
> '$research_date',
> '$research_user',
> '$submit_date',
> '$submit_user',
> '$status_code',
> '$cc_state')";
>mssql_query($insert1) or die ("Query failed: />".mssql_get_last_message());
>
>
> $insert2 = "INSERT INTO table2 (
> credit_card_id,
> case_number,
> comments)
> VALUES (
> 'card_id',
> '$case',
> '$comments')";
> mssql_query($insert2) or die ("Query failed: />".mssql_get_last_message());
>
> echo "Insert complete";
>
>
>
>
>
> On my second insert statement, please note "credit_card_id".
> This is an
> auto_increment column in table1. What I need to do is pull
> the value of "credit_card_id" from the newly inserted row
> from insert1 and put that value in a variable to assign it to
> "credit_card_id" in insert2.
>
>
>
> I hope that wasn't too confusing.
http://cz2.php.net/manual/en/function.mysql-insert-id.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Retrieve value of newly inserted row.
Dan Shirah wrote: On my second insert statement, please note "credit_card_id". This is an auto_increment column in table1. What I need to do is pull the value of "credit_card_id" from the newly inserted row from insert1 and put that value in a variable to assign it to "credit_card_id" in insert2. Just append "; SELECT @@identity" to the first query, then fetch the result as normal. Arpad -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Retrieve value of newly inserted row.
On Wed, 2007-02-14 at 13:20 -0500, Dan Shirah wrote: > Hello, > > On my second insert statement, please note "credit_card_id". This is an > auto_increment column in table1. What I need to do is pull the value of > "credit_card_id" from the newly inserted row from insert1 and put that value > in a variable to assign it to "credit_card_id" in insert2. Search the web for last_insert_id() Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
