inserting records across multiple tables

Report · Aug 03, 2006

Hope someone can keep me on track here.

My current database structure is :

i think it's all starting to come back to me - it's basically just all your interlinking / lookup tables. so i've now got the following tables :

Candidates
---------------
Candidate_ID (autonumber)
Name (text)
...

1, Iain
2, Fi
3, Rob

Vacancies
--------------
Vacancy_ID (autonumber)
Vacancy (text)
...

1, Cartographer
2, Gardener
3, Occupational therapist
4, Web designer
5, Recruitment manager

Profiles
-----------
Profile_ID (autonumber)
Profile (text)
...

1, Map making
2, Web design
3, Gardening
4, Hand therapy
5, Recruitment
6, Aviation
7, Sport
8, Travel

VacancyProfiles
----------------------
Vacancy_ID (number)
Profile_ID (number)
...

1,1
1,2
4,2
2,3
3,4
5,5
5,6

CandidateProfiles
--------------------
Candidate_ID (number)
Vacancy_ID (number)
...

1,1
1,2
1,7
1,8
2,3
2,4
2,8
3,5
3,6
3,7

and from there created two queries

CandidatesQuery
--------------------

SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON Profiles.Profile_ID = CandidateProfiles.ProfileID;

1, Iain, 1, Map making
1, Iain, 2, Web design
1, Iain, 7, Sport
1, Iain, 8, Travel
2, Fi, 3, Gardening
2, Fi, 4, Hand therapy
2, Fi, 8, Travel
3, Rob, 5, Recruitment
3, Rob, 6, Aviation
3, Rob, 7, Sport

and

Vacancies_Query
-------------------

SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID = VacancyProfiles.ProfileID;
...

1, Cartographer, 1, Map making
1, Cartographer, 2,Web design
2, Gardener, 3, Gardening
3, Occupational therapist, 4, Hand therapy
4, Web designer, 2, Web design
5, Recruitment manager, 5, Recruitment
5, Recruitment manager, 6, Aviation

-----------

But I'm a bit unsure about how to do my insert (and edit) records pages. It's straightforward enough if I just want to add a new Candidate or Vacancy - just add a record into either table.

But ideally I'd want to add any associated Profiles to each new Candidate or Vacancy at the same time - which presumably would be adding records to the CandidatesProfile and VacanciesProfiles tables too.

Can this be done, and if so how does it need setting up?

Or, does it need to be a two step process, ie adding records to the Candidates table, and then adding records to the CandidatesProfiles table?

What I'm used to doing in MS Access forms would be something like using sub forms, but really not sure how to go about replicating this in a web page.

Any pointers appreciated.

Cheers,
Iain

Report · Aug 03, 2006

1. I'd be cautious about posting my full database on a google archived
group. Your database structure and table names are now public. :(

2. Try sticking to one post - I'm starting to get winded walking around. ;O)

3. We'll get to that later, i might have question sabot this post, but is it
relegated to the post I answered below?

hth,

Jon

"Iain71" <webforumsuser@macromedia.com> wrote in message
news:easfto$p6o$1@forums.macromedia.com...
> Hope someone can keep me on track here.
>
> My current database structure is :
>
> i think it's all starting to come back to me - it's basically just all
> your
> interlinking / lookup tables. so i've now got the following tables :
>
> Candidates
> ---------------
> Candidate_ID (autonumber)
> Name (text)
> ...
>
> 1, Iain
> 2, Fi
> 3, Rob
>
>
> Vacancies
> --------------
> Vacancy_ID (autonumber)
> Vacancy (text)
> ...
>
> 1, Cartographer
> 2, Gardener
> 3, Occupational therapist
> 4, Web designer
> 5, Recruitment manager
>
>
> Profiles
> -----------
> Profile_ID (autonumber)
> Profile (text)
> ...
>
> 1, Map making
> 2, Web design
> 3, Gardening
> 4, Hand therapy
> 5, Recruitment
> 6, Aviation
> 7, Sport
> 8, Travel
>
>
> VacancyProfiles
> ----------------------
> Vacancy_ID (number)
> Profile_ID (number)
> ...
>
> 1,1
> 1,2
> 4,2
> 2,3
> 3,4
> 5,5
> 5,6
>
> CandidateProfiles
> --------------------
> Candidate_ID (number)
> Vacancy_ID (number)
> ...
>
> 1,1
> 1,2
> 1,7
> 1,8
> 2,3
> 2,4
> 2,8
> 3,5
> 3,6
> 3,7
>
> and from there created two queries
>
> CandidatesQuery
> --------------------
>
> SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID,
> Profiles.Profile
> FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON
> Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON
> Profiles.Profile_ID = CandidateProfiles.ProfileID;
>
> 1, Iain, 1, Map making
> 1, Iain, 2, Web design
> 1, Iain, 7, Sport
> 1, Iain, 8, Travel
> 2, Fi, 3, Gardening
> 2, Fi, 4, Hand therapy
> 2, Fi, 8, Travel
> 3, Rob, 5, Recruitment
> 3, Rob, 6, Aviation
> 3, Rob, 7, Sport
>
> and
>
> Vacancies_Query
> -------------------
>
> SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID,
> Profiles.Profile
> FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON
> Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID =
> VacancyProfiles.ProfileID;
> ...
>
> 1, Cartographer, 1, Map making
> 1, Cartographer, 2,Web design
> 2, Gardener, 3, Gardening
> 3, Occupational therapist, 4, Hand therapy
> 4, Web designer, 2, Web design
> 5, Recruitment manager, 5, Recruitment
> 5, Recruitment manager, 6, Aviation
>
> -----------
>
> But I'm a bit unsure about how to do my insert (and edit) records pages.
> It's
> straightforward enough if I just want to add a new Candidate or Vacancy -
> just
> add a record into either table.
>
> But ideally I'd want to add any associated Profiles to each new Candidate
> or
> Vacancy at the same time - which presumably would be adding records to the
> CandidatesProfile and VacanciesProfiles tables too.
>
> Can this be done, and if so how does it need setting up?
>
> Or, does it need to be a two step process, ie adding records to the
> Candidates
> table, and then adding records to the CandidatesProfiles table?
>
> What I'm used to doing in MS Access forms would be something like using
> sub
> forms, but really not sure how to go about replicating this in a web page.
>
> Any pointers appreciated.
>
> Cheers,
> Iain
>
>

Report · Aug 03, 2006

quote:

1. I'd be cautious about posting my full database on a google archived
group. Your database structure and table names are now public. :(

2. Try sticking to one post - I'm starting to get winded walking around. ;O)

3. We'll get to that later, i might have question sabot this post, but is it
relegated to the post I answered below?

re: posting a database structure - what's the worst that could happen here? Is it a big no-no, because the database / site could be hacked, or that someone could pinch the structure (although it's a fairly standard structure?)

I guess I'm so used to seeing posts where people don't post enough details, and the first thing anyone says is 'Can you post more details?'.

Sorry about the two threads too - but they're different subjects, and I guess I've found before that if you lump things together, one or other isn't in the title, and so never gets answered.

Its the same project as the other thread - but a different issue.

I'm pretty new to PHP / mySQL, and have been fine with flat tables, used to add / delete / edit records via a web form, but am getting into uncharted territory with a relational structure using mySQL. (although i'm familiar with how relationships work using Access - it's just translating it into a web app.)

Iain

Report · Aug 03, 2006

> re: posting a database structure - what's the worst that could happen
> here? Is
> it a big no-no, because the database / site could be hacked, or that
> someone
> could pinch the structure (although it's a fairly standard structure?)
>
> I guess I'm so used to seeing posts where people don't post enough
> details,
> and the first thing anyone says is 'Can you post more details?'.

No worries. I've doen the same thing, but I just wanted to mention that if
you have your domain name, and your database details both listed in a post,
a search on google can bring that information up. Not that it's a huge
concern, but I'm kinda paranoid. :O)

> Sorry about the two threads too - but they're different subjects, and I
> guess
> I've found before that if you lump things together, one or other isn't in
> the
> title, and so never gets answered.
>
> Its the same project as the other thread - but a different issue.

hehe - I just wanted to keep you on one issue. 😉 I can't work on two at
once. I can work on the other after we get done with this one, but not both
at once.

> I'm pretty new to PHP / mySQL, and have been fine with flat tables, used
> to
> add / delete / edit records via a web form, but am getting into uncharted
> territory with a relational structure using mySQL. (although i'm familiar
> with
> how relationships work using Access - it's just translating it into a web
> app.)

Same here. It's been very hard for me to come to grips with PHP while
trying to figure out how to accomplish what I want.

For this specific question, you might seach the archives (I actually posted
a bit prematurely, before reading all of your post. I mainly just wanted to
grab your attention on the one project so we could get the other one fixed).

If you don't find anything, we'll get to it tomorrow.

I think what you're looking for will be relatively easy to do, but I just
have to look at your code a bit more. I got tied up on a STUPID error
today. :)

Report · Aug 03, 2006

quote:

No worries. I've doen the same thing, but I just wanted to mention that if
you have your domain name, and your database details both listed in a post,
a search on google can bring that information up. Not that it's a huge
concern, but I'm kinda paranoid. :O)

Fair enough, now that you mention it.

quote:

hehe - I just wanted to keep you on one issue. 😉 I can't work on two at
once. I can work on the other after we get done with this one, but not both
at once.[/q[

No probs - I always appreciate, and never presume, any help I get on the forums!

quote:

Same here. It's been very hard for me to come to grips with PHP while
trying to figure out how to accomplish what I want.

For this specific question, you might seach the archives (I actually posted
a bit prematurely, before reading all of your post. I mainly just wanted to
grab your attention on the one project so we could get the other one fixed).

If you don't find anything, we'll get to it tomorrow.

For the adding records, I've created a straight insert candidate page, that then goes to a page to insert profiles, using checkboxes, with PHP code on the page that that page is submitted to something like :

<?php
$sql = "INSERT INTO CandidateProfiles (CandidateID, ProfileID) VALUES ";

foreach ($_POST['ckbox'] As $GetIndex => $GetValue){
if ($GetValue=='on'){
$sql .= "('{$_POST['CandidateID']}', '$GetIndex'), ";
}
}

$sql = rtrim($sql," ,") ;

mysql_query($sql);
?>

Although I haven't quite got it - the URL variable isn't getting carried through. I think it's something to do with opening a session to the database, but a bit vague about what I need to do there.

quote:

I think what you're looking for will be relatively easy to do, but I just
have to look at your code a bit more. I got tied up on a STUPID error
today. 🙂

It's often the way - I spent ages the other night trying to figure out why some styling wasn't working before I spotted I'd simply missed / inadvertantly deleted a closing curly bracket at the end of the a CSS style.

Iain

Report · Aug 04, 2006

> For the adding records, I've created a straight
> http://www.searchtechuk.com/database/addCandidate.php page, that then goes
> to a
> page to insert profiles, using checkboxes, with PHP code on the page that
> that
> page is submitted to something like :
>
> <?php
> $sql = "INSERT INTO CandidateProfiles (CandidateID, ProfileID) VALUES ";
>
> foreach ($_POST['ckbox'] As $GetIndex => $GetValue){
> if ($GetValue=='on'){
> $sql .= "('{$_POST['CandidateID']}', '$GetIndex'), ";
> }
> }
>
> $sql = rtrim($sql," ,") ;
>
> mysql_query($sql);
> ?>
>
> Although I haven't quite got it - the URL variable isn't getting carried
> through. I think it's something to do with opening a session to the
> database,
> but a bit vague about what I need to do there.

ON that code above, it looks like "on" and "off" would be set as the
indexes. These won't be unique, so you'd just be re-writing on, and off's
values, no? Am I missing something? Still waking up..

Report · Aug 04, 2006

if they're for each candidate they don't need to be unique - so if there are, say, five 'profiles', any, all or none can be attached to any candidate, in a many-to-many relationship.

Iain

Report · Aug 04, 2006

OH! OK. :O) That makes things a lot easier, then.

Should have something for you soon,w ill send you ane amil
"Iain71" <webforumsuser@macromedia.com> wrote in message
news:eavspd$2vt$1@forums.macromedia.com...
> if they're for each candidate they don't need to be unique - so if there
> are, say, five 'profiles', any, all or none can be attached to any
> candidate, in a many-to-many relationship.
>
> Iain

Report · Aug 05, 2006

Well, I think I've gotten part of the way there - I'm going to mark this as answered and start a new thread as I've narrowed it down to a more specific thing I need to do. Basically got it adding a new candidate, and going to an Add profiles page that uses checkboxes for each profile.

When I select any profiles, it adds them to the Candidateprofile table, but not with the candidateID of the newly added candidate, but with a candidateID of 0 - so I need to figure out how to pass the CandidateID of the newly added candidate from the AddCandidate page to the AddProfiles page...

Iain

Report · May 20, 2008

I am creating a project management website for a friend which consists of three tables: one for customer info, one for project info, and one for survey info(completed once project is done). what I want to do is this: when the project is finished and survey filled out, When the submit button is clicked on survey form I want the field "survey complete" to have the value "Y" (for yes) to be input into the project table.
I started this site with no knowledge of php and I am using dw cs3.

Report · May 21, 2008

professorsr wrote:
> I am creating a project management website for a friend which consists of three
> tables: one for customer info, one for project info, and one for survey
> info(completed once project is done). what I want to do is this: when the
> project is finished and survey filled out, When the submit button is clicked on
> survey form I want the field "survey complete" to have the value "Y" (for yes)
> to be input into the project table.
> I started this site with no knowledge of php and I am using dw cs3.
>

Why don't you just use a hidden form field with the value Y?

Steve

inserting records across multiple tables

1 Correct answer