PHP MySQL Select Query Join Statement to Display Data Once Multiple Rows Table
0
I am trying to display customer orders that are stored in various tables in the database. I come to an issue when there is more than 1 type of items purchased in the order. Each item in the order is stored on separate rows in the Transaction table and use the same PO, but have different TransPO.
Here are the table samples
Table: Order
|-----------------------------------------|
| id | PO | Total | CreatedTime |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 |
|-----------------------------------------|
Table: CheckoutStatus
|----------------------------------|
| id | PO | PaymentMethod |
| 1 | AF24 | PayPal |
|----------------------------------|
Table: Transaction
|----------------------------------------|
| id | TransPO | PO | Price | QTY |
| 1 | AF21 | AF24 | 3.00 | 1 |
| 2 | AF22 | AF24 | 4.00 | 1 |
| 3 | AF23 | AF24 | 3.00 | 1 |
|----------------------------------------|
Using this Select statement in PHP retrieves the data from all tables, but displays the Order and CheckoutStatus data 3 times.
SELECT
`Order`.*, `CheckoutStatus`.*, `Transaction`.*
FROM
`Order`
INNER JOIN `CheckoutStatus`
ON
Order.PO = CheckoutStatus.PO
INNER JOIN `Transaction`
ON
Order.PO = Transaction.PO
WHERE
Order.PO = 'AF24'
The data retrieved
|--------------------------------------------------------------------------------------------------------------|
| id | PO | Total | CreatedTime | id | PO | PaymentMethod | id | TransPO | PO | Price | QTY |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 1 | AF21 | AF24 3.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 2 | AF22 | AF24 4.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 3 | AF23 | AF24 3.00 | 1 |
|--------------------------------------------------------------------------------------------------------------|
For web display I need to show the Order and CheckoutStatus data once and show the 3 rows of the Transaction table.
This is what I need
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
but what I'm getting is data displaying 3 times
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
What edits are needed in the Select statement to get the result I need?
php mysql select inner-join
edited Jan 19 at 11:03
Hp_issei
41228
asked Jan 19 at 6:45
MikeMike
350216
add a comment |
0
I am trying to display customer orders that are stored in various tables in the database. I come to an issue when there is more than 1 type of items purchased in the order. Each item in the order is stored on separate rows in the Transaction table and use the same PO, but have different TransPO.
Here are the table samples
Table: Order
|-----------------------------------------|
| id | PO | Total | CreatedTime |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 |
|-----------------------------------------|
Table: CheckoutStatus
|----------------------------------|
| id | PO | PaymentMethod |
| 1 | AF24 | PayPal |
|----------------------------------|
Table: Transaction
|----------------------------------------|
| id | TransPO | PO | Price | QTY |
| 1 | AF21 | AF24 | 3.00 | 1 |
| 2 | AF22 | AF24 | 4.00 | 1 |
| 3 | AF23 | AF24 | 3.00 | 1 |
|----------------------------------------|
Using this Select statement in PHP retrieves the data from all tables, but displays the Order and CheckoutStatus data 3 times.
SELECT
`Order`.*, `CheckoutStatus`.*, `Transaction`.*
FROM
`Order`
INNER JOIN `CheckoutStatus`
ON
Order.PO = CheckoutStatus.PO
INNER JOIN `Transaction`
ON
Order.PO = Transaction.PO
WHERE
Order.PO = 'AF24'
The data retrieved
|--------------------------------------------------------------------------------------------------------------|
| id | PO | Total | CreatedTime | id | PO | PaymentMethod | id | TransPO | PO | Price | QTY |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 1 | AF21 | AF24 3.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 2 | AF22 | AF24 4.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 3 | AF23 | AF24 3.00 | 1 |
|--------------------------------------------------------------------------------------------------------------|
For web display I need to show the Order and CheckoutStatus data once and show the 3 rows of the Transaction table.
This is what I need
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
but what I'm getting is data displaying 3 times
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
What edits are needed in the Select statement to get the result I need?
php mysql select inner-join
edited Jan 19 at 11:03
Hp_issei
41228
asked Jan 19 at 6:45
MikeMike
350216
1
The query is fine. Pick that up in PHP and then format it the way you want. SQL is not the place to make those edits.
– juergen d
Jan 19 at 6:59
add a comment |
0
0
0
I am trying to display customer orders that are stored in various tables in the database. I come to an issue when there is more than 1 type of items purchased in the order. Each item in the order is stored on separate rows in the Transaction table and use the same PO, but have different TransPO.
Here are the table samples
Table: Order
|-----------------------------------------|
| id | PO | Total | CreatedTime |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 |
|-----------------------------------------|
Table: CheckoutStatus
|----------------------------------|
| id | PO | PaymentMethod |
| 1 | AF24 | PayPal |
|----------------------------------|
Table: Transaction
|----------------------------------------|
| id | TransPO | PO | Price | QTY |
| 1 | AF21 | AF24 | 3.00 | 1 |
| 2 | AF22 | AF24 | 4.00 | 1 |
| 3 | AF23 | AF24 | 3.00 | 1 |
|----------------------------------------|
Using this Select statement in PHP retrieves the data from all tables, but displays the Order and CheckoutStatus data 3 times.
SELECT
`Order`.*, `CheckoutStatus`.*, `Transaction`.*
FROM
`Order`
INNER JOIN `CheckoutStatus`
ON
Order.PO = CheckoutStatus.PO
INNER JOIN `Transaction`
ON
Order.PO = Transaction.PO
WHERE
Order.PO = 'AF24'
The data retrieved
|--------------------------------------------------------------------------------------------------------------|
| id | PO | Total | CreatedTime | id | PO | PaymentMethod | id | TransPO | PO | Price | QTY |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 1 | AF21 | AF24 3.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 2 | AF22 | AF24 4.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 3 | AF23 | AF24 3.00 | 1 |
|--------------------------------------------------------------------------------------------------------------|
For web display I need to show the Order and CheckoutStatus data once and show the 3 rows of the Transaction table.
This is what I need
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
but what I'm getting is data displaying 3 times
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
What edits are needed in the Select statement to get the result I need?
php mysql select inner-join
edited Jan 19 at 11:03
Hp_issei
41228
asked Jan 19 at 6:45
MikeMike
350216
I am trying to display customer orders that are stored in various tables in the database. I come to an issue when there is more than 1 type of items purchased in the order. Each item in the order is stored on separate rows in the Transaction table and use the same PO, but have different TransPO.
Here are the table samples
Table: Order
|-----------------------------------------|
| id | PO | Total | CreatedTime |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 |
|-----------------------------------------|
Table: CheckoutStatus
|----------------------------------|
| id | PO | PaymentMethod |
| 1 | AF24 | PayPal |
|----------------------------------|
Table: Transaction
|----------------------------------------|
| id | TransPO | PO | Price | QTY |
| 1 | AF21 | AF24 | 3.00 | 1 |
| 2 | AF22 | AF24 | 4.00 | 1 |
| 3 | AF23 | AF24 | 3.00 | 1 |
|----------------------------------------|
Using this Select statement in PHP retrieves the data from all tables, but displays the Order and CheckoutStatus data 3 times.
SELECT
`Order`.*, `CheckoutStatus`.*, `Transaction`.*
FROM
`Order`
INNER JOIN `CheckoutStatus`
ON
Order.PO = CheckoutStatus.PO
INNER JOIN `Transaction`
ON
Order.PO = Transaction.PO
WHERE
Order.PO = 'AF24'
The data retrieved
|--------------------------------------------------------------------------------------------------------------|
| id | PO | Total | CreatedTime | id | PO | PaymentMethod | id | TransPO | PO | Price | QTY |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 1 | AF21 | AF24 3.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 2 | AF22 | AF24 4.00 | 1 |
| 1 | AF24 | 10.00 | 2019-01-17 20:17:36 | 1 | AF24 | PayPal | 3 | AF23 | AF24 3.00 | 1 |
|--------------------------------------------------------------------------------------------------------------|
For web display I need to show the Order and CheckoutStatus data once and show the 3 rows of the Transaction table.
This is what I need
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
but what I'm getting is data displaying 3 times
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 1
TransPO: AF21
PO: AF24
Price: 3.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 2
TransPO: AF22
PO: AF24
Price: 4.00
QTY: 1
*Order*
ID: 1
PO: AF24
Total: 10.00
CreatedTime: 2019-01-17 20:17:36
*CheckoutStatus*
ID: 1
PO: AF24
PaymentMethod: PayPal
*Transaction*
ID: 3
TransPO: AF23
PO: AF24
Price: 3.00
QTY: 1
What edits are needed in the Select statement to get the result I need?
php mysql select inner-join
php mysql select inner-join
edited Jan 19 at 11:03
Hp_issei
41228
asked Jan 19 at 6:45
MikeMike
350216
edited Jan 19 at 11:03
Hp_issei
41228
asked Jan 19 at 6:45
MikeMike
350216
edited Jan 19 at 11:03
Hp_issei
41228
edited Jan 19 at 11:03
Hp_issei
41228
edited Jan 19 at 11:03
Hp_issei
41228
41228
asked Jan 19 at 6:45
MikeMike
350216
asked Jan 19 at 6:45
MikeMike
350216
asked Jan 19 at 6:45
MikeMike
350216
350216
1
The query is fine. Pick that up in PHP and then format it the way you want. SQL is not the place to make those edits.
– juergen d
Jan 19 at 6:59
add a comment |
1
The query is fine. Pick that up in PHP and then format it the way you want. SQL is not the place to make those edits.
– juergen d
Jan 19 at 6:59
1
1
The query is fine. Pick that up in PHP and then format it the way you want. SQL is not the place to make those edits.
– juergen d
Jan 19 at 6:59
The query is fine. Pick that up in PHP and then format it the way you want. SQL is not the place to make those edits.
– juergen d
Jan 19 at 6:59
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
});
}
});
draft saved
draft discarded
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%2f54264734%2fphp-mysql-select-query-join-statement-to-display-data-once-multiple-rows-table%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
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54264734%2fphp-mysql-select-query-join-statement-to-display-data-once-multiple-rows-table%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
1
The query is fine. Pick that up in PHP and then format it the way you want. SQL is not the place to make those edits.
– juergen d
Jan 19 at 6:59