Fw: Database Insert Help

Hi, I need some help with an insert issue.

I have two tables, organizations and contacts.
Every contact relates back to an organization

Organizations has org_id, org_name, and org_address, etc
Contacts has contact_id, contact_name, contact_address and a foreign key
called c_org_id.

contact_id and org_id are both auto increment integers.

I would like to set it up, via a web page, or a third party control, so that
when a user is adding new contact information,
they don't have to know which number to enter to relate back to an
organization, but instead could just pull down the organizations name from a
list box. Behind the scenes, that would be associated with the
organization's unique id and that would be entered into the table.

Can someone show me an example of how to do this in mysql? Since this will
be on a web page, I can combine with perl to make this easier to do. And
please, the more detailed and explicit the better.

Thank you,
Taylor
>


--
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: Database Insert Help

Taylor,

You can have the MySQL do all the heavy lifting; it will be happy to find
the right org_id from the org_name to put in the contact table.

Here's a test script that you can feed to the mysql client that shows how;
the trick in the "insert...select" statement.

Thanks for an interesting digression from an otherwise boring afternoon.

jim cant

###### demonstrates "INSERT ... SELECT"
use test;
#
# Clean out and reconstruct the tables
#
drop table org;
drop table person;

create table org (id int not null auto_increment,
name varchar(50) not null,
primary key( id ),
);

create table person (id int not null auto_increment,
orgId int not null,
last varchar(50) not null,
first varchar(50) not null,
primary key( id ),
);


describe org;
describe person;

#
# Put some data in them
#
insert org set name="org_One";
insert org set name="org_Two";
insert org set name="org_Three";

insert person set orgId=99, last="theLast", first="theFirst" ;
insert person set orgId=98, last="theEnd", first="theBeginning" ;

#
# Here's the one! The three quoted strings are data entered by the
# user; we put the last and first names in as 'column names' in the
# select statement (be sure they're quoted). The organization name
# gets passed as an arguement to the where clause.
#
insert into person (orgId,last,first) (select org.id, "SirName", "GivenName"
from org where org.name="org_Two" );


select * from org;
select * from person;


----- Original Message -----
From: "Taylor Lewick"
To:
Sent: Tuesday, May 18, 2004 7:39 PM
Subject: Fw: Database Insert Help


>
> Hi, I need some help with an insert issue.
>
> I have two tables, organizations and contacts.
> Every contact relates back to an organization
>
> Organizations has org_id, org_name, and org_address, etc
> Contacts has contact_id, contact_name, contact_address and a foreign key
> called c_org_id.
>
> contact_id and org_id are both auto increment integers.
>
> I would like to set it up, via a web page, or a third party control, so
that
> when a user is adding new contact information,
> they don't have to know which number to enter to relate back to an
> organization, but instead could just pull down the organizations name from
a
> list box. Behind the scenes, that would be associated with the
> organization's unique id and that would be entered into the table.
>
> Can someone show me an example of how to do this in mysql? Since this
will
> be on a web page, I can combine with perl to make this easier to do. And
> please, the more detailed and explicit the better.
>
> Thank you,
> Taylor
> >
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=cant_jim@hotmail.com
>
>

--
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: Database Insert Help

Taylor,

You can have the MySQL do all the heavy lifting; it will be happy to find
the right org_id from the org_name to put in the contact table.

Here's a test script that you can feed to the mysql client that shows how;
the trick in the "insert...select" statement.

Thanks for an interesting digression from an otherwise boring afternoon.

jim cant

###### demonstrates "INSERT ... SELECT"
use test;
#
# Clean out and reconstruct the tables
#
drop table org;
drop table person;

create table org (id int not null auto_increment,
name varchar(50) not null,
primary key( id ),
);

create table person (id int not null auto_increment,
orgId int not null,
last varchar(50) not null,
first varchar(50) not null,
primary key( id ),
);


describe org;
describe person;

#
# Put some data in them
#
insert org set name="org_One";
insert org set name="org_Two";
insert org set name="org_Three";

insert person set orgId=99, last="theLast", first="theFirst" ;
insert person set orgId=98, last="theEnd", first="theBeginning" ;

#
# Here's the one! The three quoted strings are data entered by the
# user; we put the last and first names in as 'column names' in the
# select statement (be sure they're quoted). The organization name
# gets passed as an arguement to the where clause.
#
insert into person (orgId,last,first) (select org.id, "SirName", "GivenName"
from org where org.name="org_Two" );


select * from org;
select * from person;


----- Original Message -----
From: "Taylor Lewick"
To:
Sent: Tuesday, May 18, 2004 7:39 PM
Subject: Fw: Database Insert Help


>
> Hi, I need some help with an insert issue.
>
> I have two tables, organizations and contacts.
> Every contact relates back to an organization
>
> Organizations has org_id, org_name, and org_address, etc
> Contacts has contact_id, contact_name, contact_address and a foreign key
> called c_org_id.
>
> contact_id and org_id are both auto increment integers.
>
> I would like to set it up, via a web page, or a third party control, so
that
> when a user is adding new contact information,
> they don't have to know which number to enter to relate back to an
> organization, but instead could just pull down the organizations name from
a
> list box. Behind the scenes, that would be associated with the
> organization's unique id and that would be entered into the table.
>
> Can someone show me an example of how to do this in mysql? Since this
will
> be on a web page, I can combine with perl to make this easier to do. And
> please, the more detailed and explicit the better.
>
> Thank you,
> Taylor
> >
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=cant_jim@hotmail.com
>
>

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