我有一些SQL数据要使用PHPExcel传递给Excel文档。
共有3个表格：
-
书籍：
1.1 bookID
1.2书名
-
标签：
2.1标签ID
2.2标签名称
-
Post_Tags:
3.1 id
3.2标签ID
3.3 bookID
我希望标签在xls表的第二列，用逗号分隔。这里我有一个while循环，在while循环中。
问题：
在生成的excel表中，并不是所有书籍的标签都列在B列的单元格中，而是只有一个标签。
有什么想法吗？提前感谢：）
require_once "includes/config.php";
require_once "includes/functions.php";
require_once 'phptoxls/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$F = $objPHPExcel -> getActiveSheet();
/* START: RECORDS */
$line = 2;
$query = mysql_query('SELECT * FROM `books`') or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
$bookID = $row['bookID'];
$bookName = $row['bookName'];
$query2 = mysql_query('SELECT * FROM `books`
INNER JOIN `post_tags`
ON post_tags.bookID = books.bookID
INNER JOIN `tags`
ON tags.tagID = post_tags.tagID
WHERE books.bookID = "'.$bookID.'"') or die(mysql_error());
if(mysql_num_rows($query2) > 0){
$rowNum = mysql_num_rows($query2);
$i = 1;
while($row1 = mysql_fetch_assoc($query2)){
$tags = $row1['tagName'];
if($i < $rowNum){
$tags .= ', ';
}
}
}
$F -> setCellValue('A'.$line, $bookName)
-> setCellValue('B'.$line, $tags);
++$line;
}
/* END: RECORDS */
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="books.xls');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
您的问题在于：
while($row1 = mysql_fetch_assoc($query2)){
$tags = $row1['tagName'];
您正在为每个循环重置$tags的值。要使它与您提供的代码一起工作，只需执行以下操作：
if(mysql_num_rows($query2) > 0){
$rowNum = mysql_num_rows($query2);
$i = 1;
$tags = "";
while($row1 = mysql_fetch_assoc($query2)){
$tags .= $row1['tagName'];
if($i < $rowNum){
$tags .= ', ';
}
}
}
然而，我建议你这样做来修复它：
if(mysql_num_rows($query2) > 0){
while($row1 = mysql_fetch_assoc($query2)){
$tags[] = $row1['tagName'];
}
$tags = implode(', ',$tags);
}
首先，请更好地缩进代码，因为它几乎不可读(http://beta.phpformatter.com)。第二：我认为你只是把标签和'，'-s连在一起。老兄在你的第一段时间开始时很认真：
<?php
require_once "includes/config.php";
require_once "includes/functions.php";
require_once 'phptoxls/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$F = $objPHPExcel->getActiveSheet();
/* START: RECORDS */
$line = 2;
$query = mysql_query('SELECT * FROM `books`') or die(mysql_error());
while ($row = mysql_fetch_assoc($query)) {
$all_tags_for_one_row = '';
$bookID = $row['bookID'];
$bookName = $row['bookName'];
$query2 = mysql_query('SELECT * FROM `books`
INNER JOIN `post_tags`
ON post_tags.bookID = books.bookID
INNER JOIN `tags`
ON tags.tagID = post_tags.tagID
WHERE books.bookID = "' . $bookID . '"') or die(mysql_error());
if (mysql_num_rows($query2) > 0) {
$rowNum = mysql_num_rows($query2);
$i = 1;
while ($row1 = mysql_fetch_assoc($query2)) {
$tags = $row1['tagName'];
if ($i < $rowNum) {
$all_tags_for_one_row .= ($i != $rowNum ? $tags . ', ' : $tags );
}
$i++;
}
}
$F->setCellValue('A' . $line, $bookName);
$F->setCellValue('B' . $line, $all_tags_for_one_row);
++$line;
}
/* END: RECORDS */
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="books.xls');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>