MYSQL to Excel

Posted: Fri Jun 29, 2007 8:30 am

I've been developing a Paypal application which stores registrants for an event in a table on my site. I don't have the time to develop all the permutations of flexible reporting that the users would like plus they need to use the data on a laptop at a park where there is no Internet Access. So I was looking at ways to export to Excel. Phpmyadmin has several ways in the Export area but I'm not about to give users access to that. So I googled and found a program that would do it (supposedly) on PHPfreaks.com (full credits in the program listing).

First I'll list the program, then a few comments after.

Code:

<?php


//  from http://www.phpfreaks.com/tutorials/114/0.php


  if ( !defined('MODULE_FILE') )


{


   die("You can't access this file directly...");


}





require_once('mainfile.php');


$module_name = basename(dirname(__FILE__));


get_lang($module_name);





// include_once('header.php');


global $db, $prefix;


$data = '';


$header = '';


  $select = 'SELECT * FROM '.$prefix.'_century';


$export = $db->sql_query($select);


$fields = $db->sql_numfields($export);


for ($i = 0; $i < $fields; $i++) {


    //$header .= $db->sql_fieldname($export, $i) . "\t";


    $header .= mysql_field_name($export, $i) . "\t";


}


//echo 'header ' . $header . '<br>';


while($row = mysql_fetch_array($export, MYSQL_ASSOC)) {


    $line = '';


    foreach($row as $key => $value) {


            if ((!isset($value)) OR ($value == "")) {


            $value = "\t";


        } else {


            $value = str_replace('"', '""', $value);


            $value = '"' . $value . '"' . "\t";


        }


        $line .= $value;


    }


/*    for ($i = 0; $i < $fields; $i++) {


     $value = $row[mysql_field_name($export, $i)] . "\t";


       if ((!isset($value)) OR ($value == "")) {


            $value = "\t";


        } else {


            $value = str_replace('"', '""', $value);


            $value = '"' . $value . '"' . "\t";


        }


        $line .= $value;





   } */


    $data .= trim($line)."\n";


}


$data = str_replace("\r","",$data);





if ($data == "") {


    $data = "\n(0) Records Found!\n";


}








header("Content-type: application/x-msdownload");


header("Content-Disposition: attachment; filename=centuryextract.xls");


header("Pragma: no-cache");


header("Expires: 0");


print "$header\n$data";








?>

Comments: while I adopted some Nuke standards, you really need to comment the inclusion of header.php out. Also the standard mysql object for fetchrow from /db/mysql.php returns both a numeric index and the field names from the result set (I hope I'm saying that right). If you look in the sql_fetchrow function you'll see that it really does a fetch_array. There is no way that I can see to pass it the MYSQL_ASSOC parameter that's needed. I guess you could program around this but I just used the native call where I could pass the parameter instead. The commented out block of code where I did "$row[mysql_field_name($export, $i)] " was my first attempt to solve this problem and it appears to work but I went back and dug thru the PHP manual to try to make the original code sort of work.

Also for debugging you will want to comment out the header statements at the end and put header.php back in and echo out values, keys etc until you get things looking good. Then take your echoes out and put the header back in and, if you are running Excel a complete Excel spreadsheet will magically appear on your screen with field names in the first row and values following. The good thing about this is that it should work with any MYSQL table with minimal adjustments and heck you could even parameterize the table itself allowing the user to export pretty much any table in the system.

Posted: Sat Jun 30, 2007 12:55 pm

fkelly, I don't have time to comment on this just now, but I had noticed that CodeIgniter had either some helper functions or classes for putting data into Excel format (I think). In addition, I have had success at work actually sending excel HTML formatted data and it handles it quite nicely (including the formatting). Just some quick thoughts.

Posted: Sat Jun 30, 2007 2:38 pm

No problem M. Actually the program I listed does the trick quite well for me. I have it hidden in the administrative part of a "Purchase" module I created, so only the admin for that module has access and all he has to do is click on a button to create an Excel file and he's sitting in Excel with a complete file loaded a second later. By virtue of the fact that the program "discovers" the field names automatically it could be extended to any other MYSQL table quite easily.