Well, there's a few things. First, I think you need to do a few things to
better normalize your data. You need a table to join a person with a job.
It would look like this:
create table peoplejob (people_id int not null, job_id not null)
Then remove the job column from people.
Then when you create a new person record, you'd do this:
insert into people (4,Bob,'Teacher')
insert into peoplejob (4,2)
This will allow you to have people who have more than one job.
If the job wasn't listed, you'd insert it and get the new id.
From PHP, you'd do something like this ($link_id is the reference to you
database connection)
$person = "Jon"
$jobsearch = "Doctor";
$result=mysql_query("select job_id from job where
title='$jobsearch'",$link_id) or die(mysql_error());
if($row=mysql_fetch_array($result))
$job_id = $row["job_id"];
else {
$result=mysql_query("insert into job values (null,'$jobsearch') or
die(mysql_error()); // assumes auto_increment
$job_id = mysql_insert_id($link_id);
}
$result=mysql_query("insert into people (name) values ('$person')",$link_id)
or die(mysql_error()); // assumes auto_increment
I just typed this in without trying it in any way, so don't tryo to run it
as is -- but it should give you an idea.
----- Original Message -----
From: "Bryan Wheelock" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, March 16, 2001 3:46 PM
Subject: INSERT records into multiple tables
> I'm just learning how to use MySQL and I am searching for a more simple
way
> of doing an INSERT query into multiple tables simultaneously.
> I have done my best to nominalize my DB but that creates but I'm confused
as
> to how to insert data into the DB and have all the related table be
updated.
> I think that I can work out some way using conditional statements in PHP
to
> insert the data into all the records, but I'm thinking there must be a
more
> elegant and faster way of doing this.
>
> e.g.
> table people
> 1 Bryan student
> 2 Jill teacher
> 3 Bob student
>
> table job
> 1 student
> 2 teacher
>
> If I want to insert a record to table PEOPLE :
> insert into people values('4', 'Jon', doctor)
>
> How can I do this without having to also do a separate insert into table
> JOB?
>
>
>
>
> You need to Understand the rules so you can break them intelligently,"
> the Dalai Lama
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
