0

Hi Everyone!

Can anybody point me to the right direction or check what am i doing wrong. Im trying to export data from php to excel using phpexcel( which is working fine if im am only getting 1 specific record). but when i tried adding a loop to create a workbook im having a problem. the data does not match the actual record from DB

this is the script

Currently editing:  
/home/msicph/public_html/sedaatria/demo/toexcel/genexcel.php
 Encoding:    Reopen  Switch to Code Editor     Close  Save

<?php
error_reporting(E_ALL);

ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';

$today = date('Y-m-d');


$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/Billing.xls");

try {
include '../includes/global_config.php';

            $q1= $con->prepare("SELECT name FROM accounts_info"); 
            $q1->execute();
            include '../includes/pdo_config.php';
            while ($row = $q1->fetch())  {
            $name=$row['name'];




                         $stmts = $con->prepare("SELECT * FROM reservations WHERE 
                                                `duedate` = DATE_SUB(CURDATE(), INTERVAL - 3 DAY) and resfor='$name' "); 
                          $stmts->execute();



                        $objPHPExcel->getActiveSheet()->setCellValue('I1', PHPExcel_Shared_Date::PHPToExcel(time()));

                        $baseRow = 5;

                        foreach($stmts as $r => $dataRow) {
                           $billname=$dataRow['resfor'];

                            $rental_date=$dataRow['rental_date'];
                            $rental_time=$dataRow['rental_time'];
                            $row = $baseRow + $r;
                            $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);

                            $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $dataRow['resnum'])
                                                          ->setCellValue('B'.$row, $dataRow['name'])
                                                          ->setCellValue('C'.$row, date('M d, Y', strtotime($dataRow['rental_date']))." / ".$dataRow['rental_time'])
                                                          ->setCellValue('D'.$row, $dataRow['service_type'])
                                                          ->setCellValue('E'.$row, $dataRow['modetrans'])
                                                          ->setCellValue('F'.$row, $dataRow['from'])
                                                          ->setCellValue('G'.$row, $dataRow['to'])
                                                          ->setCellValue('H'.$row, $dataRow['duedate'])
                                                          ->setCellValue('I'.$row, $dataRow['total_rates']);

                                $add=$r + 10;
                                $last=$r + 5;
                                $basecell="I".$add;

                                $objPHPExcel->getActiveSheet()->setCellValue($basecell,'=SUM(I4'.':I'.$last.')');
                                $objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);

                                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                                $objWriter->save(str_replace(__FILE__,'excelfiles/'.$name.'.xls',__FILE__));

                                //$objWriter->save(str_replace('.php', '.xls', __FILE__));
                                echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;

                                // Echo done
                                echo date('H:i:s') , " Done writing file" , EOL;
                                echo 'File has been created in ' , getcwd() , EOL;

                            } //END OF FOREACH


            } //end of GET NAME



  } // TRY
 catch(PDOException $e) {
    //echo "Error: " . $e->getMessage();

}
$con = null;
2
Contributors
12
Replies
60
Views
1 Year
Discussion Span
Last Post by ehpratah
Featured Replies
  • Got it! Line 45 is the one causing the problem..i just remove that line and everything working fine. Read More

0

But when i try to get a specific resfor its working the way it should be

Working script

<?php
error_reporting(E_ALL);

ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';

$today = date('Y-m-d');


$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/Billing.xls");

try {


             include '../includes/pdo_config.php';
                         $stmts = $con->prepare("SELECT * FROM reservations WHERE 
                                                `duedate` = DATE_SUB(CURDATE(), INTERVAL - 3 DAY) and resfor='7 STONES' "); 
                          $stmts->execute();
                          $result = $stmts->setFetchMode(PDO::FETCH_ASSOC);


                        $objPHPExcel->getActiveSheet()->setCellValue('I1', PHPExcel_Shared_Date::PHPToExcel(time()));

                        $baseRow = 5;

                        foreach($stmts as $r => $dataRow) {
                           $billname=$dataRow['resfor'];

                            $rental_date=$dataRow['rental_date'];
                            $rental_time=$dataRow['rental_time'];
                            $row = $baseRow + $r;
                            $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);

                            $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $dataRow['resnum'])
                                                          ->setCellValue('B'.$row, $dataRow['name'])
                                                          ->setCellValue('C'.$row, date('M d, Y', strtotime($dataRow['rental_date']))." / ".$dataRow['rental_time'])
                                                          ->setCellValue('D'.$row, $dataRow['service_type'])
                                                          ->setCellValue('E'.$row, $dataRow['modetrans'])
                                                          ->setCellValue('F'.$row, $dataRow['from'])
                                                          ->setCellValue('G'.$row, $dataRow['to'])
                                                          ->setCellValue('H'.$row, $dataRow['duedate'])
                                                          ->setCellValue('I'.$row, $dataRow['total_rates']);


                            }
                               $add=$r + 10;
                                $last=$r + 5;
                                $basecell="I".$add;

                                $objPHPExcel->getActiveSheet()->setCellValue($basecell,'=SUM(I4'.':I'.$last.')');
                                $objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);

                                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                                $objWriter->save(str_replace(__FILE__,'excelfiles/'.$billname.'.xls',__FILE__));

                                                                //$objWriter->save(str_replace('.php', '.xls', __FILE__));
                                echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;

                                // Echo done
                                echo date('H:i:s') , " Done writing file" , EOL;
                                echo 'File has been created in ' , getcwd() , EOL;












  } // TRY
 catch(PDOException $e) {
    //echo "Error: " . $e->getMessage();

}
$con = null;
0

notice the select query on both script. I dont know if the problem is the while loop or the foreach..

0

anybody has an idea what am i doing wrong? when i try to creat a excel file base on the list of name i got from the reservations table using a loop im not getting the right data on the excel file.

0

What is this “../includes/pdo_config.php” ? It seems that in first case you include it AFTER you create the first prepared statements .

You use two different ways to retrieve the results with no apparent reason. Would be good to have some consistency on that. Of would be better to first fetch the results before looping with fetchAll() and test that there are any. More over you don't use the question mark placeholder that helps except from sanitizing to improve the performance.

0

Hi jkon i am still orienting my self to pdo and still getting the feel of it.dont know yet most of the tricks and other stuffs. The "../includes/pdo_config.php" is just the database config of one of the table.(im using 2 db on this case) by the way i updated my script and its almost working but im still getting a wild data that should not be included in one of the excel file that the script generates.

kindly look on my updated script pls

<?php
error_reporting(E_ALL);
include '../includes/pdo_config.php'; // database msicph_southwest and table reservations config
include '../includes/phpexcel_config.php'; // database msicph_globalsouthwest and table accounts_info
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';

$today = date('Y-m-d');


$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/Billing.xls");

try {
                         $getname=$conn->prepare("SELECT msicph_globalsouthwest.accounts_info.name
                                                  FROM msicph_globalsouthwest.accounts_info
                                                 WHERE msicph_globalsouthwest.accounts_info.name IN (
                                                SELECT msicph_southwest.reservations.resfor
                                                FROM msicph_southwest.reservations)"); 
                         $getname->execute();
                         while ($dataname = $getname->fetch(PDO::FETCH_ASSOC)) {

                                    $resfor=$dataname['name'];

                                    $stmts = $con->prepare("SELECT * FROM reservations WHERE 
                                                `duedate` = DATE_SUB(CURDATE(), INTERVAL - 3 DAY) and resfor='$resfor'"); 
                                    $stmts->execute();
                                    $objPHPExcel->getActiveSheet()->setCellValue('I1', PHPExcel_Shared_Date::PHPToExcel(time()));

                                    $baseRow = 5;

                                    foreach($stmts as $r => $dataRow) {
                                    $billname=$dataRow['resfor'];

                                    $rental_date=$dataRow['rental_date'];
                                    $rental_time=$dataRow['rental_time'];
                                    $row = $baseRow + $r;
                                    $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);

                                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $dataRow['resnum'])
                                                          ->setCellValue('B'.$row, $dataRow['name'])
                                                          ->setCellValue('C'.$row, date('M d, Y', strtotime($dataRow['rental_date']))." / ".$dataRow['rental_time'])
                                                          ->setCellValue('D'.$row, $dataRow['service_type'])
                                                          ->setCellValue('E'.$row, $dataRow['modetrans'])
                                                          ->setCellValue('F'.$row, $dataRow['from'])
                                                          ->setCellValue('G'.$row, $dataRow['to'])
                                                          ->setCellValue('H'.$row, $dataRow['duedate'])
                                                          ->setCellValue('I'.$row, $dataRow['total_rates']);
                                    }  // end of foreachloop


                            //END OF FOREACH
                                $add=$r + 10;
                                $last=$r + 5;
                                $basecell="I".$add;

                                $objPHPExcel->getActiveSheet()->setCellValue($basecell,'=SUM(I4'.':I'.$last.')');
                                $objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);


                                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                                $objWriter->save(str_replace(__FILE__,'excelfiles/'.$resfor.'.xls',__FILE__));

                                //$objWriter->save(str_replace('.php', '.xls', __FILE__));
                                echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;

                                // Echo done
                                echo date('H:i:s') , " Done writing file" , EOL;

                         } // end of while loop


  } // TRY
 catch(PDOException $e) {
    //echo "Error: " . $e->getMessage();

}
$con= null;

and the sample output is attached here 1.jpg2.jpg3.jpg

0

Also this are the sql dump if you wanna check it:)

CREATE TABLE IF NOT EXISTS `accounts_info` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL,
  `contact_no` varchar(50) NOT NULL,
  `address` varchar(100) NOT NULL,
  `accred_no` varchar(50) NOT NULL,
  `bill_term` int(2) NOT NULL,
  `cashbond` float NOT NULL,
  `contact_person` varchar(100) NOT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=252 ;

--
-- Dumping data for table `accounts_info`
--

INSERT INTO `accounts_info` (`aid`, `name`, `email`, `contact_no`, `address`, `accred_no`, `bill_term`, `cashbond`, `contact_person`) VALUES
(161, 'Zulu Plaza', 'test@example.com', '', '', '', 7, 0, 'vicky'),
(160, 'ZOEMIE', '', '', 'ILIG ILIGAN COVE BRGY. YAPAK MALAY AKLAN', '', 7, 0, 'MS. AGEL TIPAY')


CREATE TABLE IF NOT EXISTS `reservations` (
  `resid` int(11) NOT NULL AUTO_INCREMENT,
  `foname` varchar(50) NOT NULL,
  `resnum` varchar(20) NOT NULL,
  `resfor` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `total_adult` int(11) NOT NULL,
  `total_senior` int(11) NOT NULL,
  `total_child` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `rental_date` date NOT NULL,
  `rental_time` time NOT NULL,
  `depart_date` date NOT NULL,
  `depart_time` time NOT NULL,
  `service_type` varchar(100) NOT NULL,
  `vehicle_type` varchar(100) NOT NULL,
  `modetrans` varchar(20) NOT NULL,
  `from` varchar(100) NOT NULL,
  `to` varchar(100) NOT NULL,
  `remark` varchar(500) NOT NULL,
  `note` varchar(250) NOT NULL,
  `rates` float NOT NULL,
  `total_extras` float NOT NULL,
  `total_rates` float NOT NULL,
  `grand_total` float NOT NULL,
  `payment_type` varchar(50) NOT NULL,
  `payment_info` varchar(50) NOT NULL,
  `status` varchar(10) NOT NULL,
  `payment_stat` varchar(25) NOT NULL,
  `duedate` date NOT NULL,
  `active` int(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (`resid`),
  UNIQUE KEY `resnum` (`resnum`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `reservations`
--

INSERT INTO `reservations` (`resid`, `foname`, `resnum`, `resfor`, `name`, `total_adult`, `total_senior`, `total_child`, `email`, `rental_date`, `rental_time`, `depart_date`, `depart_time`, `service_type`, `vehicle_type`, `modetrans`, `from`, `to`, `remark`, `note`, `rates`, `total_extras`, `total_rates`, `grand_total`, `payment_type`, `payment_info`, `status`, `payment_stat`, `duedate`, `active`) VALUES
(1, '', '15091402F672', '7 STONES', 'Mendez, Neil', 2, 0, 0, 'ehpratah@gmail.com', '2015-09-14', '21:12:00', '0000-00-00', '21:12:00', 'Transfer In', 'Boat Only', 'One Way', 'Caticlan Airport', 'The District Boracay', '', '', 375, 0, 750, 0, 'Corporate Billing', '', 'Open', 'Unpaid', '2015-09-18', 1),
(2, '', '15091402925C', 'AISSATUO', 'Mendez, Jon', 2, 0, 0, 'ehpratah@gmail.com', '2015-09-14', '23:23:00', '0000-00-00', '23:23:00', 'Transfer In', 'Boat + Cab', 'One Way', 'Caticlan Airport', 'Caticlan Jetty Port', '', '', 7500, 0, 7500, 0, 'Corporate Billing', '', 'Open', 'Unpaid', '2015-09-18', 1),
(3, '', '150914023657', 'ALI TOUR', 'Gubat, Neil', 2, 0, 0, 'ehpratah@gmail.com', '2015-09-14', '00:03:00', '0000-00-00', '00:03:00', 'Transfer In', 'Boat + Cab', 'One Way', 'Caticlan Airport', 'Caticlan Jetty Port', '', '', 0, 0, 0, 0, 'Corporate Billing', '', 'Open', 'Unpaid', '2015-09-18', 1),
(4, '', '150914022693', '7 STONES', 'Filio, Jon', 2, 0, 0, 'ehpratah@gmail.com', '2015-09-14', '00:04:00', '0000-00-00', '00:04:00', 'Transfer In', 'Boat + Cab', 'One Way', 'AlargoVillaagues', '7 STONES', '', '', 0, 0, 0, 0, 'Corporate Billing', '', 'Open', 'Unpaid', '2015-09-18', 1),
(5, '', '15091402D3C7', 'AISSATUO', 'dasd, ssss', 2, 0, 0, 'ehpratah@gmail.com', '2015-09-14', '03:01:00', '0000-00-00', '03:01:00', 'Transfer In', 'Boat + Cab', 'One Way', '357 RESORT', 'AISSATOU BEACH RESORT', '', '', 0, 0, 0, 0, 'Corporate Billing', '', 'Open', 'Unpaid', '2015-09-18', 1);

Edited by pritaeas: Removed contacts.

0

Hello ehpratah, I am ready to recreate your example but I can't understand from your posts (and the image) why e.g. in the first image you wrote -this record shouldn't be here- . Also you wrote -the data does not match the actual record from DB- and -im still getting a wild data that should not be included in one of the excel file- . Tell a bit more about why these data shouldn't be included , share what you are trying to do , your logic in order to find out why your statements and the programm doesn't stand to it.

0

Hi jkon sorry for the late response. my sql dump was cleaned by a mod because of the sensitive data that was posted thats why it didn't match.basically what im trying to do is send an .xls invoice to the client 3 days before the duedate. what i am trying to do right now is collect all the reservations of each client dynamically and create individual.xls file for it. on the first image i said that the last record should not be there because its a transaction of the second image. i use the resfor field as identifier on the 2nd select statement to group all the data.

hope i explained it clearly :)

thanks,!!

0

also i'll post again the sql dump here without the contact info when i reach home. sorry im on mobile

0

Hi JKON! this is the dump file for the client name

CREATE TABLE IF NOT EXISTS `accounts_info` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL,
  `contact_no` varchar(50) NOT NULL,
  `address` varchar(100) NOT NULL,
  `accred_no` varchar(50) NOT NULL,
  `bill_term` int(2) NOT NULL,
  `cashbond` float NOT NULL,
  `contact_person` varchar(100) NOT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=253 ;

--
-- Dumping data for table `accounts_info`
--

INSERT INTO `accounts_info` (`name`) VALUES
('Zulu Plaza'),
('ZOEMIE'),
('Your Travel Planner'),
('WILLYS'),
('WHITE HOUSE'),
('WHITE CORAL'),
('WEST CLIFF VILLA'),
('Water Color'),
('Villa Romero'),
('VILLA ROMERO'),
('VICTORY BEACH'),
('VELIS INN'),
('TWO SEASONS'),
('TROPICS'),
('Travel Planner'),
('TONGLEN'),
('THE TIDES'),
('THE PALMS '),
('The Lind'),
('Tans Guesthouse'),
('Taj'),
('SURFSIDE'),
('SUR BEACH '),
('SPR Boracay'),
('SOL MARINA'),
('Sitio boracay'),
('SIMPLE TRAVEL'),
('SHANGRILA'),
('Serendipity/Secret Garden'),
('SEA WIND'),
('SEA BIRD'),
('SAND CASTLE'),
('ROYAL PARK'),
('RESIDENCIA'),
('RED COCONUT'),
('REAL MARIS'),
('RAKSO TRAVEL'),
('POP PEARL OF THE PACIFIC'),
('Paradiso'),
('PARADISE GARDEN'),
('Panorama'),
('ORCHIDS'),
('Oirent Beach'),
('ONE CRESENT'),
('OCEAN CLUB'),
('NIGI NIGI MAIN NU NOOS'),
('NANDANA'),
('My Boracay Guide'),
('MR. HOLIDAY'),
('MORNING BEACH'),
('MONACO SUITES'),
('MITOS PLACE'),
('MILFLORES'),
('MICROTEL'),
('Metropolitan Doctors'),
('MAXIMA'),
('MANILA TRAVEL '),
('MANGO RAY'),
('LUGAR BONITO'),
('LINGGANAY'),
('LE SOLEIL'),
('LAS BRISAS'),
('LA RESERVE'),
('LA ISLA BONITA'),
('LA CARMELA'),
('JONYS'),
('ISLAND STAFF'),
('ISLAND JEWEL INN'),
('Isla Gecko'),
('HOTEL SOFIA'),
('HEY JUDE-SOUTHBEACH'),
('HEY JUDE'),
('HENNAN LAGOON'),
('HENNAN GARDEN'),
('Hannah Hotel'),
('Guillius Italian'),
('GRAND VISTA'),
('GRAND PRIX HIDEAWAY'),
('GRAND BORACAY'),
('GOLDEN PHEONIX'),
('Go Discover'),
('FRIDAYS'),
('FTC BORACAY'),
('Ferra Hotel'),
('FRENDZ RESORT'),
('FAT JIMMYS'),
('FAITHFULLY'),
('FAIRWAYS'),
('ESTACIO UNO'),
('Eurotel'),
('ERNEST PLACE'),
('El Centro'),
('ECLIPSE '),
('DISTRICT'),
('DISCOVERY SHORES'),
('Dickson Travel'),
('DIAMOND WATER EDGE'),
('DAVES STRAW'),
('CROWN REGENCY 3'),
('Crystal Sand'),
('CROWN REGENCY 2'),
('CROWN REGENCY 1'),
('COHIBA'),
('COCO LOCO'),
('CLUB TEN '),
('CASA PILAR'),
('c/o Rey Puazo'),
('Casa Estrella'),
('c/o Marquez'),
('c/o Marzon-Sir Eugene'),
('c/o Kalibo Cable'),
('c/o Kalibo Tourism'),
('c/o Ed Lao'),
('c/o Juanito Go'),
('BPOD'),
('BORACAY UPTOWN'),
('BORACAY TROPICS'),
('BORACAY SUMMER PALACE'),
('BORACAY STRANDS'),
('BORACAY REGENCY'),
('Boracay Peninsula'),
('BORACAY PARADISE'),
('BORACAY ONLINE'),
('BORACAY OCEAN CLUB'),
('BORACAY MANDARIN'),
('BORACAY HOLIDAY'),
('BORACAY GRAND VISTA'),
('BORACAY DIARY'),
('BORACAY BREEZE'),
('BORACAY BEACH RESORT'),
('Boracay Beach Chalet'),
('Bora Sky'),
('BLUE WAVES'),
('BLUE VERANDA'),
('Blue Mango'),
('Blue Lotus'),
('Blue Lily'),
('Blue Horizon'),
('BLUE BAYOU'),
('BEACH COMBER'),
('BBC-BORACAY BEACH CLUB'),
('BANS'),
('BAMBOO BUNGALOWS'),
('BALINGHAI'),
('B & C Leisure'),
('Azzalea'),
('AZURRO'),
('Azul'),
('Asya Premier'),
('ASTORIA'),
('ARWANA'),
('ARGONAUTA'),
('Aquarius marina'),
('AMBASSADOR'),
('ALTA VISTA'),
('ALI TOUR'),
('AISSATUO'),
('7 STONES'),
('REGULAR GUEST'),
('BERTOS BERTAS BURTOS AGENCY'),
('NPGMENDEZ GRAVEL AND TRAVEL');
1

Got it! Line 45 is the one causing the problem..i just remove that line and everything working fine.

Votes + Comments
It says that I answered that one but you did. +1 because you answered the questions (although the error was in the use of this external class)
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.