Making Excel file with PHPExcel, each column writing twice
SOLVED HERE: I just needed to change this line
while ($row = mysql_fetch_array($result)) {
to
开发者_Python百科 while ($row = mysql_fetch_row($result)) {
I am using PHPExcel to pull data from mySQL database to create the XLS file. I'm writing two columns: TITLE and VIEWS. (title is an actual column in the db table, views is a count of how many times each title appears in the table).
The data writes to the XLS file, the problem is that each column is writing twice:
|-------A-------|-------B-------|-------C-------|-------D-------|
5 ---TITLE---|---VIEWS---| 6 ---Title 1---|----Title 1----|-------3--------|-------3-------| 7 ---Title 2---|----Title 2----|-------6--------|-------6-------| 8 ---Title 3---|----Title 3----|-------4--------|-------4-------|How do I fix this so that it displays each column once?
my code:
$sql = "
SELECT
titles.title,
COUNT(DISTINCT history.id) AS `count`
FROM
user_history titles
LEFT OUTER JOIN
user_history history
ON
titles.title = history.title
";
$sql .= "
GROUP BY
titles.title
ORDER BY
titles.title
";
$result = mysql_query($sql);
$headings = array('Title', 'Views');
if ($result = mysql_query($sql) or die(mysql_error())) {
$rowNumber = 5;
$col = 'A';
foreach($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading);
$col++;
}
// Loop through the result set
$rowNumber = 6;
while ($row = mysql_fetch_array($result)) {
$col = 'A';
foreach($row as $cell) {
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
$col++;
}
$rowNumber++;
}
Because your join is lossless.
You are getting redundant information twice
Just do this for your query:
SELECT
titles.title,
COUNT(DISTINCT titles.id) AS `count`
FROM user_history titles
GROUP BY titles.title
ORDER BY titles.title;
精彩评论