MySQL to excel report

Question

dmorrison 0 Newbie Poster

13 Years Ago

Hi,

this report is taking too long to run, there is alot of data in the tables.

does anyone know a quicker way to optimize this:

<?php

// This report outputs everything to excel format 
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors',TRUE);
ini_set("memory_limit","1024M");

date_default_timezone_set('Europe/London');

/** PHPExcel */
require_once '../includes/PHPExcel.php';
include ("../../config.php");
$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED'");
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

// Do the sql query and output
$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$amount = $info['amount'];
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
$i = $i + 1;
}
$objPHPExcel->getActiveSheet()->setTitle('SOM');

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet(1)->setTitle('CHEQUES');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' and type = 'CHEQUES'");

$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

$i =2;

while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);

$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
			
$i = $i + 1;
}


// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet(2)->setTitle('BACS');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' and type = 'BACS'");

$objPHPExcel->setActiveSheetIndex(2)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

$i =2;

while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);
$amount = $info['amount'];

$objPHPExcel->setActiveSheetIndex(2)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
$i = $i + 1;
}


// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet(3)->setTitle('PO');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' and type = 'PO'");

$objPHPExcel->setActiveSheetIndex(3)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);
$amount = $info['amount'];

$objPHPExcel->setActiveSheetIndex(3)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
$i = $i + 1;
}

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(4);
$objPHPExcel->getActiveSheet(4)->setTitle('CASH');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' and type = 'CASH'");

$objPHPExcel->setActiveSheetIndex(4)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);
$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(4)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
$i = $i + 1;
}

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(5);
$objPHPExcel->getActiveSheet(5)->setTitle('ALLPAY');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' and type = 'ALLPAY'");

$objPHPExcel->setActiveSheetIndex(5)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);
$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(5)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
$i = $i + 1;
}

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(6);
$objPHPExcel->getActiveSheet(6)->setTitle('CARD');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' and type = 'CARD'");

$objPHPExcel->setActiveSheetIndex(6)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Company');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$compSearch = mysql_query("SELECT * FROM companyMapping where number = '{$info['companyNo']}'");
$company = mysql_fetch_array($compSearch, MYSQL_ASSOC);
$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(6)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$company['name']}");
$i = $i + 1;
}


// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(7);
$objPHPExcel->getActiveSheet(7)->setTitle('ALLOCATED');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'ALLOCATED' order by type");


$objPHPExcel->setActiveSheetIndex(7)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Collector')
			->setCellValue("E1", 'Allocated to')
			->setCellValue("F1", 'Type')
			->setCellValue("G1", 'Date allocation was requested');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$repInfo2 = mysql_query("SELECT * FROM susdata WHERE unallDataID = '{$info['id']}'");
$info2 = mysql_fetch_array($repInfo2, MYSQL_ASSOC);

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(7)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$info2['yourname']}")
			->setCellValue("E$i", "{$info2['allocate_to']}")
			->setCellValue("F$i", "{$info2['type']}")
			->setCellValue("G$i", "{$info2['date_submitted']}");
$i = $i + 1;
}

// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Suspense_Master.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');


exit;
?>

php

Edited 13 Years Ago by dmorrison because: n/a

1 Contributor
1 Reply
99 Views
7 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by dmorrison

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dmorrison 0 Newbie Poster · Answer 1 · 2010-09-27T17:42:14+00:00

dmorrison 0 Newbie Poster

13 Years Ago

can anyone help