PHP while语句回显重复项 [英] PHP while statement echoes duplicates
问题描述
我是PHP的新手，我花了几个小时研究并试图弄清我做错了什么.我要加入几个表，以使用事务表中的多个事务填充客户端配置文件页面.页面的其余部分正在按预期使用查询，但是使用while语句时，结果中的每个事务重复的次数等于结果中事务的总数.例如，如果结果具有3个事务编号(1、2、3)，则while语句将回显"1、1、1、2、2、2、3、3、3".而不只是"1、2、3".
I'm new to PHP and I have spent several hours researching and trying to figure out what I'm doing wrong. I'm joining several tables to populate a client profile page with multiple transactions from a transactions table. The rest of the page is working with the query as expected, but when using a while statement, each transaction in the result is repeating equal to the total number of transactions in the result. For instance, if the result has 3 transaction numbers (1, 2, 3), the while statement is echoing "1, 1, 1, 2, 2, 2, 3, 3, 3" instead of just "1, 2, 3".
我知道这很简单，但是却需要我拔头发.请帮助:
I know it is something simple, but it has me pulling my hair out. Please help:
if(!isset($_GET['PeopleID'])){
header('Location: people.php');
die();
} else {
if(!filter_var($_GET['PeopleID'], FILTER_VALIDATE_INT)){
header('Location: people.php');
die();
} else {
$PeopleID = filter_var($_GET['PeopleID'], FILTER_VALIDATE_INT);
$query = "SELECT *
FROM
people
LEFT JOIN notes ON
PersonID = PeopleID
LEFT JOIN filenumbers ON
id = PeopleID
LEFT JOIN transactions ON
transactions.FileNumberID = filenumbers.FileNumberID
LEFT JOIN filingdetails ON
filingdetails.FileID = filenumbers.FileNumberID
WHERE PeopleID=:PeopleID";
$result = $db_connection->prepare($query);
$result->execute([
'PeopleID' => $PeopleID
]);
}
}
while($row = $result -> fetch()){
echo $row['TransactionNumber'] . ", ";
}
推荐答案
好的...因此，这是您 SQL 查询的预期行为.
Okay... So, this is expected behaviour from your SQL query.
当您以自己的方式 LEFT JOIN 时，最终会多次获得所有数据.最好用一个例子来解释...
When you LEFT JOIN in the manner that you have you end up getting all of the data multiple times over. This is best explained with an example...
假设您有三个表:
table1 =>您的主表
table1 => your primary table
table2 =>与 table1
table3 =>与 table1
table1
id, value
1, a
2, b
3, c
table2
id, fkey, value
1, a, xxxx
2, a, cxcz
3, a, bdtht
4, a, nbtyt
5, b, ngftht
6, b, thhgfj
7, b, gjfhj
8, c, hjghj
9, c, jhgjh
table3
id, fkey, value
10, 2, sdgvgd
11, 2, gjkmhkjk
12, 3, kjhkl
13, 3, kljj
接下来，我们要使用 LEFT JOIN < select1 从 table1 中选择数据，同时并入 table2 中的数据./p>
Next we want to SELECT data from table1 whilst incorporating data from table2, using a LEFT JOIN
SELECT
table1.id, table1.value, table2.id, table2.value
FROM table1
LEFT JOIN table2 ON
table2.fkey = table1.id
上面的 SQL 的输出类似于下面的内容...
The output of the above SQL would look something like the below...
table1.id, table1.value, table2.id, table2.value
1, a, 1, xxxx
1, a, 2, cxcz
1, a, 3, bdtht
1, a, 4, nbtyt
2, b, 5, ngftht
2, b, 6, thhgfj
2, b, 7, gjfhj
3, c, 8, hjghj
3, c, 9, jhgjh
请注意，以上输出实际上是 table1 中的每个选定行乘以 table2 中的对应行数；或者...
Notice that the above output is effectively every selected row in table1 multiplied by the number of corresponding rows in table2; OR...
every row in table1 * table2 rows with fkey=table1.id
所以，这很简单.如果我们现在看一个包含三个表和两个 LEFT JOINs
So, that's simple enough. What if we now look at an example with the three tables and two LEFT JOINs
SELECT
table1.id, table1.value, table2.id, table2.value
FROM table1
LEFT JOIN table2 ON
table2.fkey = table1.id
LEFT JOIN table3 ON
table3.fkey = table1.id
table1.id, table1.value, table2.id, table2.value, table3.id, table3.value
1, a, 1, xxxx, null, null
1, a, 2, cxcz, null, null
1, a, 3, bdtht, null, null
1, a, 4, nbtyt, null, null
2, b, 5, ngftht, 10, 2, sdgvgd
2, b, 5, ngftht, 11, 2, gjkmhkjk
2, b, 6, thhgfj, 10, 2, sdgvgd
2, b, 6, thhgfj, 11, 2, gjkmhkjk
2, b, 7, gjfhj, 10, 2, sdgvgd
2, b, 7, gjfhj, 11, 2, gjkmhkjk
3, c, 8, hjghj, 12, 3, kjhkl
3, c, 8, hjghj, 13, 3, kljj,
3, c, 9, jhgjh, 12, 3, kjhkl
3, c, 9, jhgjh, 13, 3, kljj
在这里您可以看到我们现在的输出等效于:
Here you can see that we now have output equivalent to:
every row in table1 * table2 rows with fkey=table1.id * table3 rows with fkey=table1.id
这篇关于PHP while语句回显重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
