MySQL中有三个表：Employees、Branches、Departments。我需要以以下方式显示信息：
亚特兰大分行
配送部
菲利普·J·弗莱
电话：123456工程部
Turanga Leela
电话：123457
Bender Rodriguez
电话：123458
当前的简单PHP代码：
1） 从三个表中获取行（带有JOIN的简单SELECT查询）
2） 将它们放入行（mysql_fetch_assoc）
3） 使用PHP While循环显示
结果如下：
亚特兰大分行
配送部
菲利普·J·弗莱
电话：123456亚特兰大分公司
工程部
图兰加·利拉
电话：123457
亚特兰大分公司
工程部
Bender Rodriguez
电话：123458
您可以推荐什么技术（JS、jQuery、Ajax）或方法，这样我就可以只使用一个查询提取行信息，而不会重复Branch名称和Department名称？
更新：如果我将分支名称放在循环之外（使用While循环），将有多个循环：1）获取分支，2）获取部门，3）获取该部门的所有员工。环
更新：共享代码：
<?php
// Create connection
$connection = mysql_connect('localhost','root', '') or die('Connection error.');
mysql_query("SET NAMES 'utf8'", $connection);
mysql_select_db("eReference");
// Check Employees
$query = "SELECT Employees.fName, Employees.lName, Department.deptName, Branch.branchName, ".
"FROM Employees ".
"LEFT JOIN Department ".
"ON Employees.department = Department.id ".
"LEFT JOIN Branch ".
"ON Employees.branch = Branch.id ;";
$result = mysql_query($query, $connection) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
?>
<h2><?php echo $row['branchName']; ?></h2>
<?php if ($row['deptName']) echo "<h3>" . $row['deptName'] . "</h3>"; ?>
<h4><?php echo $row['fName'] . " " . $row['lName']; ?></h4></p>
<?php
}
?>
<?php
$i = 1; // to be incremented after printing branchName once
while ($row = mysql_fetch_assoc($result)) {
if($i == 1) { ?>
<h2><?php echo $row['branchName']; $i ++; ?></h2>
<?php } ?>
<?php if ($row['deptName']) echo "<h3>" . $row['deptName'] . "</h3>"; ?>
<h4><?php echo $row['fName'] . " " . $row['lName']; ?></h4></p>
<?php } ?>
只需添加一个变量$i = 1，并在打印前检查它是否等于1。第一次打印后，将其递增。它只是添加了一个if语句。
希望这能有所帮助。
我就是这么做的。
用数据创建一个多维数组，并在数组中迭代以呈现输出。
就内存使用而言，这不是最有效的，但除非你有数千行数据，否则这可能不会成为问题。
这样做的好处是，html呈现代码更简单、更容易理解，而且sql和html没有混合在一起。（有利于代码维护）
<?php
// Create connection
$connection = mysql_connect('localhost','root', '') or die('Connection error.');
mysql_query("SET NAMES 'utf8'", $connection);
mysql_select_db("eReference");
// Check Employees
$query = "SELECT Employees.fName, Employees.lName, Department.deptName, Branch.branchName, ".
"FROM Employees ".
"LEFT JOIN Department ".
"ON Employees.department = Department.id ".
"LEFT JOIN Branch ".
"ON Employees.branch = Branch.id ;";
// note you probably want to add an order by statement here too, to ensure consistent sorting
$result = mysql_query($query, $connection) or die(mysql_error());
$data = array();
// build a multi-dimensional array from the result set
while ($row = mysql_fetch_assoc($result)) {
$data[($row['branchName'])][($row['deptName'])][] = array(
'name' => "{$row['fName']} {$row['lName']}",
'phone' => $row['phone'] // add phone, doesn't exist in original query, but just to illustrate how it would work
);
}
// sql finishes here
?>
<?php
// html rendering
// use htmlentities to escape any html chars, such as < > etc
foreach ($data as $branchName => $departments) {
echo '<h2>',htmlentities($branchName),'</h2>';
foreach ($departments as $deptName => $employees) {
foreach ($employees as $employee) {
echo '<h3>',htmlentities($deptName),'</h3>';
echo '<h4>',htmlentities($employee['name']),'</h4>';
echo '<h4>',htmlentities($employee['phone']),'</h4>';
}
}
}
?>
从sql中获得的结果应该是一个数组，因此使用while循环迭代抛出数组，同时回显当前索引的结果
也许这会奏效。。
<?php
$deptName;
while($row = mysql_fetch_assoc($result))
{
if ($row['deptName'])
{
if ($deptName != $row['deptName'])
{
echo "<h3>" . $row['deptName'] . "</h3>";
$deptName = $row['deptName'];
}
}
}
?>
$result = mysql_query($query, $connection) or die(mysql_error());
$newarray = array();
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
$newarray[$i]['deptName'] = $row['deptName'];
$newarray[$i]['fName'] = $row['fName'];
$newarray[$i]['lName'] = $row['lName'];
$i++;
}
<h2><?php echo $newarray[0]['branchName']; ?></h2>
while($newarray){
?>
<?php if ($newarray['deptName']) echo "<h3>" . $newarray['deptName'] . "</h3>"; ?>
<h4><?php echo $newarray['fName'] . " " . $newarray['lName']; ?></h4></p>
}
？>
所以，这就是它所做的：
$query = "SELECT Employees.lName, Employees.fName, Employees.mName, Position.position, ".
"department.deptName, department.deptId, ".
"Branch.branchName, Branch.branchId, ContactInformation.* ".
"FROM Employees ".
"LEFT JOIN Position ".
"ON Employees.position = Position.id ".
"LEFT JOIN Department ".
"ON Employees.department = Department.deptId ".
"LEFT JOIN Branch ".
"ON Employees.branch = Branch.branchId ".
"LEFT JOIN ContactInformation ".
"ON Employees.contactInformation = ContactInformation.id ".
"ORDER BY Employees.branch, Employees.department ASC;";
$result = mysql_query($query, $connection) or die(mysql_error());
$arrayOfEmployees = array();
while ($row = mysql_fetch_assoc($result)) {
$arrayOfEmployees[($row['branchName'])][($row['deptName'])][] = array(
'lName' => $row['lName'],
'fName' => $row['fName'],
'mName' => $row['mName'],
'position' => $row['position'],
'lPhone1' => $row['lPhone1'],
'lPhone2' => $row['lPhone2'],
'mPhone' => $row['mPhone'],
'fax' => $row['fax'],
'office' => $row['office'],
'email' => $row['email']
);
}
foreach($arrayOfEmployees as $branchName => $arrayOfDepartments) {
echo "<h2>".$branchName."</h2>";
foreach($arrayOfDepartments as $deptName => $arrayOfEmployeeContacts) {
echo '<h3>',htmlentities($deptName),'</h3>';
foreach($arrayOfEmployeeContacts as $employeeContacts) {
echo "<h4>".$employeeContacts["lName"]." ".$employeeContacts["fName"]." ".$employeeContacts["mName"]."</h4>";
echo "<p>";
if($employeeContacts["position"]) echo $employeeContacts["position"]."<br>";
$num = $employeeContacts["lPhone1"];
if($employeeContacts["lPhone1"]) echo "+".substr($num,0,1)." (".substr($num,1,4).") "." ".substr($num,5,2)."-".substr($num,7,2)."-".substr($num,9,2)."<br>";
$num = $employeeContacts["lPhone2"];
if($employeeContacts["lPhone2"]) echo "+".substr($num,0,1)." (".substr($num,1,4).") "." ".substr($num,5,2)."-".substr($num,7,2)."-".substr($num,9,2)."<br>";
$num = $employeeContacts["mPhone"];
if($employeeContacts["mPhone"]) echo "+".substr($num,0,1)." (".substr($num,1,3).") "." ".substr($num,4,3)."-".substr($num,7,2)."-".substr($num,9,2)."<br>";
$num = $employeeContacts["fax"];
if($employeeContacts["fax"]) echo "+".substr($num,0,1)." (".substr($num,1,4).") "." ".substr($num,5,2)."-".substr($num,7,2)."-".substr($num,9,2)."<br>";
if($employeeContacts["email"]) echo "<a href='"mailto:".$employeeContacts["email"]."'">".$employeeContacts["email"]."</a><br>";
if($employeeContacts["office"]) echo "Кабинет — ".$employeeContacts["office"];
echo "</p>";
}
}
}
经过测试。该解决方案动态运行良好——只需在数据库中添加更多的分支和部门即可。以下是我最初使用的方法的效率检查（以微秒为单位）：
Array-based Original
1 1.015 1.012
2 1.016 1.02
3 1.026 1.013
4 1.015 1.002
5 1.026 1.02
6 1.014 1.02
7 1.013 1.019
8 1.005 1.014
9 1.013 1.006
10 1.021 1.015
Average 1.0164 1.0141