PHP/SQL: 3 tables, one bridge table with a one-to-one and one-to-many relationship
I'm trying to join 3 tables together: A Contacts table, a Classes table and a StudentsInClasses bridge table that has a one-to-one relationship with Contacts and a one-to-many relationship with Classes.
My desired output is this:
- Peter
- Math 101
- French
- English 202
- Physics
- Tom Smith
- French
- English 202
- Paul
- English 202
- Physics
- Harry
- Math 101
- Physics
But instead I'm getting this (my var_dump may have different info):
- Peter
- Math 101
- Tom Smith
- French
- Paul
- English 202
- Harry
- Math 101
Here are my tables StudentsInClasses, Contacts, Classes (sorry for formatting issues, I can't upload pics yet).
StudentsInClassID ContactID ClassID.
1 3 1
2 7 4
3 1 4
4 3 1
5 7 2
6 7 2
7 1 2
11 23 4
10 23 5
ContactID ContactName ContactEmail
1 Peter Homeatgoogledotca
3 Tom Tomhotmaildotcom
7 Paul mehomedotcom
23 Harry Harryhomedotcom
ClassID ClassName ClassRoom
1 Math-101 273
2 English-202 444
4 French 111
5 Physics R444
My SQL Query:
$getInfo = $conn->prepare("
SELECT ClassName, ContactName FROM StudentsInClasses
JOIN Contacts ON StudentsInClasses.ContactID = Contacts.ContactID
JOIN Classes ON StudentsInClasses.ClassID = Classes.ClassID
GROUP BY ContactName");
$getInfo->execute();
$getInfo = $getInfo->fetchAll();
My var_dump produces this (you can see I'm only getting a one-to-one relationship with Classes where I should be getting 2-3 classes for each contact):
Harry
array(4) { ["ClassName"]=> string(6) "French" [0]=> string(6) "French" ["ContactName"]=> string(5) "Harry" [2]=> string(5) "Harry" }
Paul
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(9) "Paul" [2]=> string(9) "Paul" }
Peter
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(5) "Peter" [2]=> string(5) "Peter" }
Tom Smith
array(4) { ["ClassName"]=> string(8) "Math 101" [0]=> string(8) "Math 101" ["ContactName"]=> string(9) "Tom Smith" [2]=> string(9) "Tom Smith" }
Every person should have 2-3 classes, but I'm only getting one.
I've been searching S.O. and Google for a solution, but I'm getting nowhere.
Thanks in advance to anyone who can help! 8-)
php sql join
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
add a comment |
I'm trying to join 3 tables together: A Contacts table, a Classes table and a StudentsInClasses bridge table that has a one-to-one relationship with Contacts and a one-to-many relationship with Classes.
My desired output is this:
- Peter
- Math 101
- French
- English 202
- Physics
- Tom Smith
- French
- English 202
- Paul
- English 202
- Physics
- Harry
- Math 101
- Physics
But instead I'm getting this (my var_dump may have different info):
- Peter
- Math 101
- Tom Smith
- French
- Paul
- English 202
- Harry
- Math 101
Here are my tables StudentsInClasses, Contacts, Classes (sorry for formatting issues, I can't upload pics yet).
StudentsInClassID ContactID ClassID.
1 3 1
2 7 4
3 1 4
4 3 1
5 7 2
6 7 2
7 1 2
11 23 4
10 23 5
ContactID ContactName ContactEmail
1 Peter Homeatgoogledotca
3 Tom Tomhotmaildotcom
7 Paul mehomedotcom
23 Harry Harryhomedotcom
ClassID ClassName ClassRoom
1 Math-101 273
2 English-202 444
4 French 111
5 Physics R444
My SQL Query:
$getInfo = $conn->prepare("
SELECT ClassName, ContactName FROM StudentsInClasses
JOIN Contacts ON StudentsInClasses.ContactID = Contacts.ContactID
JOIN Classes ON StudentsInClasses.ClassID = Classes.ClassID
GROUP BY ContactName");
$getInfo->execute();
$getInfo = $getInfo->fetchAll();
My var_dump produces this (you can see I'm only getting a one-to-one relationship with Classes where I should be getting 2-3 classes for each contact):
Harry
array(4) { ["ClassName"]=> string(6) "French" [0]=> string(6) "French" ["ContactName"]=> string(5) "Harry" [2]=> string(5) "Harry" }
Paul
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(9) "Paul" [2]=> string(9) "Paul" }
Peter
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(5) "Peter" [2]=> string(5) "Peter" }
Tom Smith
array(4) { ["ClassName"]=> string(8) "Math 101" [0]=> string(8) "Math 101" ["ContactName"]=> string(9) "Tom Smith" [2]=> string(9) "Tom Smith" }
Every person should have 2-3 classes, but I'm only getting one.
I've been searching S.O. and Google for a solution, but I'm getting nowhere.
Thanks in advance to anyone who can help! 8-)
php sql join
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
add a comment |
I'm trying to join 3 tables together: A Contacts table, a Classes table and a StudentsInClasses bridge table that has a one-to-one relationship with Contacts and a one-to-many relationship with Classes.
My desired output is this:
- Peter
- Math 101
- French
- English 202
- Physics
- Tom Smith
- French
- English 202
- Paul
- English 202
- Physics
- Harry
- Math 101
- Physics
But instead I'm getting this (my var_dump may have different info):
- Peter
- Math 101
- Tom Smith
- French
- Paul
- English 202
- Harry
- Math 101
Here are my tables StudentsInClasses, Contacts, Classes (sorry for formatting issues, I can't upload pics yet).
StudentsInClassID ContactID ClassID.
1 3 1
2 7 4
3 1 4
4 3 1
5 7 2
6 7 2
7 1 2
11 23 4
10 23 5
ContactID ContactName ContactEmail
1 Peter Homeatgoogledotca
3 Tom Tomhotmaildotcom
7 Paul mehomedotcom
23 Harry Harryhomedotcom
ClassID ClassName ClassRoom
1 Math-101 273
2 English-202 444
4 French 111
5 Physics R444
My SQL Query:
$getInfo = $conn->prepare("
SELECT ClassName, ContactName FROM StudentsInClasses
JOIN Contacts ON StudentsInClasses.ContactID = Contacts.ContactID
JOIN Classes ON StudentsInClasses.ClassID = Classes.ClassID
GROUP BY ContactName");
$getInfo->execute();
$getInfo = $getInfo->fetchAll();
My var_dump produces this (you can see I'm only getting a one-to-one relationship with Classes where I should be getting 2-3 classes for each contact):
Harry
array(4) { ["ClassName"]=> string(6) "French" [0]=> string(6) "French" ["ContactName"]=> string(5) "Harry" [2]=> string(5) "Harry" }
Paul
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(9) "Paul" [2]=> string(9) "Paul" }
Peter
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(5) "Peter" [2]=> string(5) "Peter" }
Tom Smith
array(4) { ["ClassName"]=> string(8) "Math 101" [0]=> string(8) "Math 101" ["ContactName"]=> string(9) "Tom Smith" [2]=> string(9) "Tom Smith" }
Every person should have 2-3 classes, but I'm only getting one.
I've been searching S.O. and Google for a solution, but I'm getting nowhere.
Thanks in advance to anyone who can help! 8-)
php sql join
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
I'm trying to join 3 tables together: A Contacts table, a Classes table and a StudentsInClasses bridge table that has a one-to-one relationship with Contacts and a one-to-many relationship with Classes.
My desired output is this:
- Peter
- Math 101
- French
- English 202
- Physics
- Tom Smith
- French
- English 202
- Paul
- English 202
- Physics
- Harry
- Math 101
- Physics
But instead I'm getting this (my var_dump may have different info):
- Peter
- Math 101
- Tom Smith
- French
- Paul
- English 202
- Harry
- Math 101
Here are my tables StudentsInClasses, Contacts, Classes (sorry for formatting issues, I can't upload pics yet).
StudentsInClassID ContactID ClassID.
1 3 1
2 7 4
3 1 4
4 3 1
5 7 2
6 7 2
7 1 2
11 23 4
10 23 5
ContactID ContactName ContactEmail
1 Peter Homeatgoogledotca
3 Tom Tomhotmaildotcom
7 Paul mehomedotcom
23 Harry Harryhomedotcom
ClassID ClassName ClassRoom
1 Math-101 273
2 English-202 444
4 French 111
5 Physics R444
My SQL Query:
$getInfo = $conn->prepare("
SELECT ClassName, ContactName FROM StudentsInClasses
JOIN Contacts ON StudentsInClasses.ContactID = Contacts.ContactID
JOIN Classes ON StudentsInClasses.ClassID = Classes.ClassID
GROUP BY ContactName");
$getInfo->execute();
$getInfo = $getInfo->fetchAll();
My var_dump produces this (you can see I'm only getting a one-to-one relationship with Classes where I should be getting 2-3 classes for each contact):
Harry
array(4) { ["ClassName"]=> string(6) "French" [0]=> string(6) "French" ["ContactName"]=> string(5) "Harry" [2]=> string(5) "Harry" }
Paul
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(9) "Paul" [2]=> string(9) "Paul" }
Peter
array(4) { ["ClassName"]=> string(11) "English 202" [0]=> string(11) "English 202" ["ContactName"]=> string(5) "Peter" [2]=> string(5) "Peter" }
Tom Smith
array(4) { ["ClassName"]=> string(8) "Math 101" [0]=> string(8) "Math 101" ["ContactName"]=> string(9) "Tom Smith" [2]=> string(9) "Tom Smith" }
Every person should have 2-3 classes, but I'm only getting one.
I've been searching S.O. and Google for a solution, but I'm getting nowhere.
Thanks in advance to anyone who can help! 8-)
php sql join
php sql join
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
asked Nov 27 '18 at 18:56
P. RyanP. Ryan
63
63
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53506382%2fphp-sql-3-tables-one-bridge-table-with-a-one-to-one-and-one-to-many-relationsh%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53506382%2fphp-sql-3-tables-one-bridge-table-with-a-one-to-one-and-one-to-many-relationsh%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
