1

I have data that includes a day column. There are upwards of 400 row for each day.

I'd like to export each day's data in to their own Excel sheet.

To group the data I'm using SELECT * FROM mydata GROUP BY DATE(mydata.day);.

How can I get all that grouped data and do the insert in to the excel doc, with each dates data in its own sheet? Right now my attempt looks like it will only insert data for one day.

Essentially, I'll need a for each to go through each grouped data.. as in, for each day, loop through and output the data in its sheet.

require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';

@mysql_select_db($db['name']);

$query = mysql_query('SELECT * FROM mydata GROUP BY DATE(mydata.day)');
)
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Create a new worksheet
$objPHPExcel->createSheet();

$sheet=0; //this will need to be outside a loop so I can $sheet++ to set the new ActiveSheetIndex

// Set excelrow to 2 so we skip the line of headings
$excelrow=2;

while($row = mysql_fetch_array($query)){

  // Set sheet index
  $objPHPExcel->setActiveSheetIndex($sheet);

  // Set the sheet title
  $objPHPExcel->getActiveSheet()->setTitle($row['day']);

  // Set sheet headings
  $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Name');
  $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Email');

  // Get the data
  $objPHPExcel->getActiveSheet()->SetCellValue('A'.$excelrow, $row['name']);
  $objPHPExcel->getActiveSheet()->SetCellValue('B'.$excelrow, $row['email']); 

  $excelrow++; 
} 

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="name_of_file.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
DT.DTDG
  • 765
  • 1
  • 13
  • 31
  • GROUP BY will just provide you a single row for each date and allow you to use aggregate functions like `count()`, `min()`, `max()`, etc. You'll need to do a query using `WHERE day = ...` for each date to get all the rows from that date. – Devon Bessemer Apr 30 '15 at 05:43
  • @Devon Thanks for the explanation Devon. How could I use `WHERE day = ` when I don't know the value of day, especially in a loop. Could you provide an answer showing this? – DT.DTDG Apr 30 '15 at 05:51
  • 1
    Well you could use the date from the GROUP BY query which will provide a list of dates. So you'd just need two query statements and two loops. You should look at using mysqli or PDO though, mysql_ is deprecated in PHP. – Devon Bessemer Apr 30 '15 at 05:53
  • Thanks @Devon. Want to provide that as an answer so I can accept? If you're able to write up a quick example that would be much appreciated as well! Thanks again – DT.DTDG Apr 30 '15 at 06:03

0 Answers0