解释
我有一个包含用户的表和另一个存储有用户id的证书的表。1个用户可以拥有多个证书。我需要选择一次用户,并按用户id列出所有证书,但我无法正确实现,只为一个用户选择一个证书。
样本数据
这个非常简化的样本数据,实际上有150多个字段和5个表
我有以下表格:
用户
Id FirstName LastName
1 John Smith
2 Lisa Washington
证书
UserId CertificateName
1 Foo
1 Bar
1 Something
2 FooBar
2 Bizz
PHP看起来像:
$UserID = get_current_user_id();
$GetUserId = $_GET["Id"];
if(isset($UserID)) {
$users = $con->prepare("
SELECT u.FirstName
,u.LastName
,c.CertificateName
FROM Users u
LEFT JOIN Certificates c ON u.Id = c.UserID
WHERE u.Id = '$GetUserId'
LIMIT 1
");
$users->execute();
$users->bind_result(
$FirstName,
$LastName,
$CertificateName
);
} else {
echo "There is no User ID detected, try to refresh browser.";
}
<html>
....
<?php
while ($users->fetch()) {
?>
<div class="grid-item"><?php echo $FirstName; ?></div>
<div class="grid-item"><?php echo $LastName; ?></div>
<div class="certificates"><?php echo $CertificateName; ?></div>
<?php
}
?>
现在的输出
如果我通过GetUserId = 1
,我得到的结果是:
John Smith
Foo
期望输出
它应该列出特定用户的所有证书。
John Smith
Foo
Bar
Something
我尝试了什么
我曾尝试在while循环中使用foreach
循环来获取证书,但通过这种方式,它根本没有选择任何证书。你有什么想法吗?我怎样才能做到?
这应该有效,您可能需要更改您喜欢的其他分隔符。
SELECT u.FirstName,
u.LastName,
GROUP_CONCAT(c.CertificateName SEPARATOR '<br>') AS CertificateName
FROM Users u
LEFT JOIN Certificates c ON u.Id = c.UserID
WHERE u.Id = '$GetUserId'
LIMIT 1
编辑:根据@Matei Mihai的正确评论,你应该绑定参数,而不是把它们放在sql可注入的查询中
$users = $con->prepare("
SELECT u.FirstName,
u.LastName,
GROUP_CONCAT(c.CertificateName SEPARATOR '<br>') AS CertificateName
FROM Users u
LEFT JOIN Certificates c ON u.Id = c.UserID
WHERE u.Id = ?
LIMIT 1
");
$users->bind_param('i',$GetUserId);
$users->execute();
试试这个:
SELECT
*
FROM
Users usr
INNER JOIN
Certificates cert
ON
usr.Id = cert.UserId
WHERE
usr.Id = '1'
要获得此OUTPUT-1:
Id FirstName LastName UserId CertificateName
1 John Smith 1 Foo
1 John Smith 1 Bar
1 John Smith 1 Something
您的输出设置将输出John Smith的多个副本,如下所示:
John Smith
Foo
John Smith
Bar
John Smith
Something
如果你想要的是
John Smith
Foo
Bar
Something
然后,在输出之前,您需要先将查询读取到数组中:
<?php
$certArray = array();
while ($users->fetch()) {
$certArray[] = $CertificateName;
}
?>
<div class="grid-item"><?php echo $FirstName; ?></div>
<div class="grid-item"><?php echo $LastName; ?></div>
<?php
foreach($certArray as $CertificateName) {
?>
<div class="certificates"><?php echo $CertificateName; ?></div>
<?php
}
?>
或者将其分解为2个查询,如果您需要,我将留给其他人来解释。
这样的东西应该可以工作。我使用了结构化编程和mysqli语法,但我相信你可以使用它。
<?php
//Assuming $con is your DB connector
$q = 'SELECT * FROM Users';
$r = mysqli_query($con, $q);
while($list = mysqli_fetch_assoc($r)) { ?>
<tr>
<td><?php echo $list['id']; ?></td>
<td><?php echo $list['FirstName']; ?></td>
<td><?php echo $list['LastName']; ?></td>
<td>
<?php
//echo the certificate names
$userId = $list['id'];
$q1 = 'SELECT * from Certificates WHERE UserId = '.$userId.'';
$r1 = mysqli_query($con, $q1);
while($certif = mysqli_fetch_assoc($r1)){
echo $certif['CertificateName'].'<br /> ';
}
?>
</td>
</tr>
<?php }?>