Export MySqli query

Topics: Developer Forum, Project Management Forum, User Forum
Aug 12, 2010 at 12:56 AM

Hi, I am very new to this. Your help would be greatly appreciated. I would like to know how to export data from a MySql table into an excel spreadsheet. I have the knowledge to get the information from the database to the spreadsheet but how do I have the code repeat until all data is echoed onto the page?

Here is what I have already. This produces only a single row of results from the database...just need to know how to get it all onto the spreadsheet!

<?php

/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
require_once '../Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

require_once('../includes/connection.php');

$result = mysqli_query($connection,"SELECT * FROM students ORDER BY school ASC")
or die(mysqli_error($connection));

while($row = mysqli_fetch_array($result)) {

//get all rows you want from the tutors table

$school = $row['school'];

}

// Set properties
$objPHPExcel->getProperties()->setCreator("Somebody")
							 ->setLastModifiedBy("Somebody")
							 ->setTitle($school."Student List")
							 ->setSubject($school."Student List")
							 ->setDescription("Student List at ".$school)
							 ->setKeywords("Student List")
							 ->setCategory("Student List");


// Add some data

$result = mysqli_query($connection,"SELECT * FROM students ORDER BY school ASC")
or die(mysqli_error($connection));

while($row = mysqli_fetch_array($result)) {

//get all rows you want from the tutors table

$id = $row['id'];
$full_name = $row['full_name'];
$school = $row['school'];
$year = $row['school_year'];
$class = $row['class'];
$contact = $row['contact'];
$phone_h = $row['phone_h'];
$phone_m = $row['phone_m'];
$tutor_1 = $row['tutor_1'];

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', $full_name)
            ->setCellValue('B1', $contact)
            ->setCellValue('C1', $year.$class)
            ->setCellValue('D1', $tutor_1);

}

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');

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

Aug 20, 2010 at 1:00 PM

I also don't get this working..

Coordinator
Aug 20, 2010 at 1:10 PM

Increment the row number for the Excel cell references for each row that you read from the database

$row = 1;
while($row = mysqli_fetch_array($result)) {

   //get all rows you want from the tutors table

   $id = $row['id'];
   $full_name = $row['full_name'];
   $school = $row['school'];
   $year = $row['school_year'];
   $class = $row['class'];
   $contact = $row['contact'];
   $phone_h = $row['phone_h'];
   $phone_m = $row['phone_m'];
   $tutor_1 = $row['tutor_1'];

   $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$row, $full_name)
            ->setCellValue('B'.$row, $contact)
            ->setCellValue('C'.$row, $year.$class)
            ->setCellValue('D'.$row, $tutor_1);
   $row++;
}

Aug 20, 2010 at 1:49 PM

haha thanks! That was stupid :P Put everything in one cell haha