Inserting and retrieving id

Hi,

I have a subroutine which updates and inserts edited/new rows into my datab=
ase.

Afterwards, I want it to display the edited/new row. This is easy in case o=
f an edited row, since I have an id to refer to:

if ($id) { $dbh->do("UPDATE items $set_clause WHERE item_id =3D $id"); }
else { $dbh->do("INSERT INTO items $set_clause"); }

print $q->redirect("media.pl?mode=3Dget_changed&id=3D$id&m edia_type=
=3D$media_type");

But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT) immediately after=
inserting new data? Is there a good way to return such a value upon insert=
ion?

Thanks,

Jan
--=20
Either this man is dead or my watch has stopped. - Groucho Marx

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Inserting and retrieving id

Jan Eden said:
> Hi,
>
> I have a subroutine which updates and inserts edited/new rows into my
> database.
>
> Afterwards, I want it to display the edited/new row. This is easy in
> case of an edited row, since I have an id to refer to:
>
> if ($id) { $dbh->do("UPDATE items $set_clause WHERE item_id = $id"); }
> else { $dbh->do("INSERT INTO items $set_clause"); }
>
> print
> $q->redirect("media.pl?mode=get_changed&id=$id&media _type=$media_type");
>
> But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT) immediately
> after inserting new data? Is there a good way to return such a value
> upon insertion?
>
> Thanks,
>
> Jan
> --
> Either this man is dead or my watch has stopped. - Groucho Marx
>
add following line into else clause after do.
($id)=$dbh->selectrow_array("SELECT LAST_INSERT_ID()");
BTW: equivalent construct will work for java.

--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Inserting and retrieving id

Jan Eden said:
> Hi,
>
> I have a subroutine which updates and inserts edited/new rows into my
> database.
>
> Afterwards, I want it to display the edited/new row. This is easy in
> case of an edited row, since I have an id to refer to:
>
> if ($id) { $dbh->do("UPDATE items $set_clause WHERE item_id = $id"); }
> else { $dbh->do("INSERT INTO items $set_clause"); }
>
> print
> $q->redirect("media.pl?mode=get_changed&id=$id&media _type=$media_type");
>
> But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT) immediately
> after inserting new data? Is there a good way to return such a value
> upon insertion?
>
> Thanks,
>
> Jan
> --
> Either this man is dead or my watch has stopped. - Groucho Marx
>
add following line into else clause after do.
($id)=$dbh->selectrow_array("SELECT LAST_INSERT_ID()");
BTW: equivalent construct will work for java.

--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Inserting and retrieving id

Jan Eden wrote:

>Hi,
>
>I have a subroutine which updates and inserts edited/new rows into my database.
>
>Afterwards, I want it to display the edited/new row. This is easy in case of an edited row, since I have an id to refer to:
>
>if ($id) { $dbh->do("UPDATE items $set_clause WHERE item_id = $id"); }
>else { $dbh->do("INSERT INTO items $set_clause"); }
>
>print $q->redirect("media.pl?mode=get_changed&id=$id&media _type=$media_type");
>
>But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT) immediately after inserting new data? Is there a good way to return such a value upon insertion?
>
>Thanks,
>
>Jan
>
>
else {
$id = $dbh->{mysql_insertid};
}

should work.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Inserting and retrieving id

Jan Eden wrote:

>Hi,
>
>I have a subroutine which updates and inserts edited/new rows into my database.
>
>Afterwards, I want it to display the edited/new row. This is easy in case of an edited row, since I have an id to refer to:
>
>if ($id) { $dbh->do("UPDATE items $set_clause WHERE item_id = $id"); }
>else { $dbh->do("INSERT INTO items $set_clause"); }
>
>print $q->redirect("media.pl?mode=get_changed&id=$id&media _type=$media_type");
>
>But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT) immediately after inserting new data? Is there a good way to return such a value upon insertion?
>
>Thanks,
>
>Jan
>
>
else {
$id = $dbh->{mysql_insertid};
}

should work.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Inserting and retrieving id

William R. Mussatto wrote on 29.06.2004:

>Jan Eden said:
>>
>>But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT)
>>immediately after inserting new data? Is there a good way to return
>>such a value upon insertion?
>>
>add following line into else clause after do.
>($id)=3D$dbh->selectrow_array("SELECT LAST_INSERT_ID()"); BTW:
>equivalent construct will work for java.

Thanks, William and Mayuran,

that did the trick. I suspected there is a function for such an obvious nec=
essity, but I got lost in all the manuals around me.

- Jan
--=20
There are 10 kinds of people: those who understand binary, and those who d=
on't

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Inserting and retrieving id

William R. Mussatto wrote on 29.06.2004:

>Jan Eden said:
>>
>>But how can I get the id (INT PRIMARY KEY AUTO_INCREMENT)
>>immediately after inserting new data? Is there a good way to return
>>such a value upon insertion?
>>
>add following line into else clause after do.
>($id)=3D$dbh->selectrow_array("SELECT LAST_INSERT_ID()"); BTW:
>equivalent construct will work for java.

Thanks, William and Mayuran,

that did the trick. I suspected there is a function for such an obvious nec=
essity, but I got lost in all the manuals around me.

- Jan
--=20
There are 10 kinds of people: those who understand binary, and those who d=
on't

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Inserting and retrieving id

Jan Eden wrote:

>>add following line into else clause after do.
>>($id)=$dbh->selectrow_array("SELECT LAST_INSERT_ID()"); BTW:
>>equivalent construct will work for java.
>
>
> Thanks, William and Mayuran,
>
> that did the trick. I suspected there is a function for such an obvious necessity, but I got lost in all the manuals around me.

Note: Compared to the other suggestion (using the database handle or
statement handle attribute mysql_insert_id), this one is slower, because it
performs another database query.


Jochen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Inserting and retrieving id

Jan Eden wrote:

>>add following line into else clause after do.
>>($id)=$dbh->selectrow_array("SELECT LAST_INSERT_ID()"); BTW:
>>equivalent construct will work for java.
>
>
> Thanks, William and Mayuran,
>
> that did the trick. I suspected there is a function for such an obvious necessity, but I got lost in all the manuals around me.

Note: Compared to the other suggestion (using the database handle or
statement handle attribute mysql_insert_id), this one is slower, because it
performs another database query.


Jochen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org