[SOLVED] have php code to create an excel file with db results, can't get column headers

[jej1216]

I am not a PHP expert by any means, so here goes.......

I have php code that successfully connects to a MySQL DB and writes the data to an Excel spreadsheet, thanks to a cool php I found called class-excel-xml.inc.php by Oliver Schwarz <[email protected]>.  By including this in my php, I got the data to load to an Excel spreadsheet.  However, I need the column headers as well.  I tried creating a different array to get the headers and putting it prior to the data array, but it did not work - I got the Header array values and the data array values output to the browser, not in an Excel.  Here is my code taht correctly loads the data:

<?php
// include the php-excel class
require (dirname (__FILE__) . "/class-excel-xml.inc.php");

$connection = mysql_connect("server", "id", "password");
mysql_select_db("database", $connection);

$query = "SELECT * FROM incidents";

$result = mysql_query($query) or die($query . '<br>' . mysql_error());

$myarray = array();
while($row = mysql_fetch_array($result)) {
    $myarray[] = array($row['STAMP'],
    $row['incident_id'],        $row['fac_id'],           $row['room_descr'],        $row['oth_descr'],
    $row['person_type'],        $row['medrec_num'],       $row['acct_num'],          $row['person_name'],
    $row['person_lname'],       $row['person_street'],    $row['person_city'],       $row['person_state'],
    $row['person_zip'],         $row['person_phone'],     $row['dob'],               $row['inc_date'],
    $row['inc_time'],           $row['fall_type'],        $row['assisted'],          $row['observe'],
    $row['fall_type2'],         $row['assisted2'],        $row['observe2'],          $row['treat_var'],
    $row['med_var'],            $row['pt_outcome'],       $row['pt_outcome_oth'],    $row['ni_type'],
    $row['pt_rights'],          $row['near_miss'],        $row['equip'],             $row['environ'],
    $row['desc_oth'],           $row['inc_descr'],        $row['injury'],            $row['inj_descr'],
    $row['rel_factor_fall'],    $row['rel_factor_fall2'], $row['rel_factor_fall3'],  $row['rel_factor_fall4'],
    $row['rel_factor_meds'],    $row['rel_factor_oth'],   $row['severity'],          $row['not_type'],
    $row['note_oth'],           $row['not_time'],         $row['not_type2'],         $row['note2_oth'],
    $row['not_time2'],          $row['not_type3'],        $row['note3_oth'],         $row['not_time3'],
    $row['not_type4'],          $row['note4_oth'],        $row['not_time4'],         $row['witness1_type'],
    $row['witness1_name'],      $row['witness1_street'],  $row['witness1_city'],     $row['witness1_st'],
    $row['witness1_zip'],       $row['witness1_phone'],   $row['witness2_type'],     $row['witness2_name'],
    $row['witness2_street'],    $row['witness2_city'],    $row['witness2_st'],       $row['witness2_zip'],
    $row['witness2_phone'],     $row['reporting_name'],   $row['reporting_id'],      $row['reporting_date'],
    $row['assess_ortho'],       $row['rescore_morse'],    $row['det_contrib'],       $row['id_rec_medment'],
    $row['req_pharm_review'],   $row['rev_curr_int'],     $row['id_add_int'],        $row['addr_bwlblddr'],
    $row['assure_safety_equip'],$row['id_env_issues'],    $row['id_learn_pref'],     $row['comm_team'],
    $row['comm_fam'],           $row['notify_attending'], $row['comm_name'],         $row['actions_descr'],
    $row['followup_descr'],     $row['rm_severity'],      $row['notified_ceo'],      $row['not_ceo_datetime'],
    $row['notified_ho'],        $row['not_ho_datetime'],  $row['notified_plant'],    $row['not_plant_datetime'],
    $row['notified_food'],      $row['not_food_datetime'],$row['notified_attending'],$row['not_sattending_datetime'],
    $row['notified_ins'],       $row['not_ins_datetime'], $row['notified_pharm'],    $row['not_pharm_datetime'],
    $row['notified_dno'],       $row['not_dno_datetime'], $row['notified_dto'],      $row['not_dto_datetime'],
    $row['notified_oth'],       $row['not_oth_datetime'], $row['not_oth_descr'],     $row['root_cause_an'],
    $row['rca_date'],           $row['rm_name'],          $row['rm_date'],           $row['Done']);
}

// generate excel file
$xls = new Excel_XML;
$xls->addArray ( $myarray );
$xls->generateXML ( "incidents" );

?>

I tried adding a header array with this:

<?php
// include the php-excel class
require (dirname (__FILE__) . "/class-excel-xml.inc.php");

$connection = mysql_connect("server", "id", "password");
mysql_select_db("database", $connection);

$query = "SELECT * FROM incidents";

$result = mysql_query($query) or die($query . '<br>' . mysql_error());

$myheaderarray = array();
$myheaderarray [] = array('STAMP','incident_id','fac_id','room_descr','oth_descr','person_type','medrec_num','acct_num','person_name',
'person_lname','person_street','person_city','person_state','person_zip','person_phone','dob','inc_date',
'inc_time','fall_type','assisted','observe','fall_type2','assisted2','observe2','treat_var','med_var','pt_outcome',
'pt_outcome_oth', 'ni_type','pt_rights','near_miss','equip','environ','desc_oth','inc_descr','injury','inj_descr',
'rel_factor_fall', 'rel_factor_fall2','rel_factor_fall3','rel_factor_fall4','rel_factor_meds','rel_factor_oth',
'severity','not_type','note_oth','not_time','not_type2','note2_oth','not_time2','not_type3','note3_oth','not_time3',
'not_type4','note4_oth','not_time4','witness1_type','witness1_name','witness1_street','witness1_city','witness1_st',
'witness1_zip','witness1_phone','witness2_type','witness2_name','witness2_street','witness2_city','witness2_st',
'witness2_zip','witness2_phone','reporting_name','reporting_id','reporting_date','assess_ortho','rescore_morse',
'det_contrib','id_rec_medment','req_pharm_review','rev_curr_int','id_add_int','addr_bwlblddr','assure_safety_equip',
'id_env_issues','id_learn_pref','comm_team','comm_fam','notify_attending','comm_name','actions_descr','followup_descr',
'rm_severity','notified_ceo','not_ceo_datetime','notified_ho','not_ho_datetime','notified_plant','not_plant_datetime',
'notified_food','not_food_datetime','notified_attending','not_sattending_datetime','notified_ins','not_ins_datetime',
'notified_pharm','not_pharm_datetime','notified_dno','not_dno_datetime','notified_dto','not_dto_datetime','notified_oth',
'not_oth_datetime','not_oth_descr','root_cause_an','rca_date','rm_name','rm_date','Done');

$myarray = array();
while($row = mysql_fetch_array($result)) {
    $myarray[] = array($row['STAMP'],
    $row['incident_id'],        $row['fac_id'],           $row['room_descr'],        $row['oth_descr'],
    $row['person_type'],        $row['medrec_num'],       $row['acct_num'],          $row['person_name'],
    $row['person_lname'],       $row['person_street'],    $row['person_city'],       $row['person_state'],
    $row['person_zip'],         $row['person_phone'],     $row['dob'],               $row['inc_date'],
    $row['inc_time'],           $row['fall_type'],        $row['assisted'],          $row['observe'],
    $row['fall_type2'],         $row['assisted2'],        $row['observe2'],          $row['treat_var'],
    $row['med_var'],            $row['pt_outcome'],       $row['pt_outcome_oth'],    $row['ni_type'],
    $row['pt_rights'],          $row['near_miss'],        $row['equip'],             $row['environ'],
    $row['desc_oth'],           $row['inc_descr'],        $row['injury'],            $row['inj_descr'],
    $row['rel_factor_fall'],    $row['rel_factor_fall2'], $row['rel_factor_fall3'],  $row['rel_factor_fall4'],
    $row['rel_factor_meds'],    $row['rel_factor_oth'],   $row['severity'],          $row['not_type'],
    $row['note_oth'],           $row['not_time'],         $row['not_type2'],         $row['note2_oth'],
    $row['not_time2'],          $row['not_type3'],        $row['note3_oth'],         $row['not_time3'],
    $row['not_type4'],          $row['note4_oth'],        $row['not_time4'],         $row['witness1_type'],
    $row['witness1_name'],      $row['witness1_street'],  $row['witness1_city'],     $row['witness1_st'],
    $row['witness1_zip'],       $row['witness1_phone'],   $row['witness2_type'],     $row['witness2_name'],
    $row['witness2_street'],    $row['witness2_city'],    $row['witness2_st'],       $row['witness2_zip'],
    $row['witness2_phone'],     $row['reporting_name'],   $row['reporting_id'],      $row['reporting_date'],
    $row['assess_ortho'],       $row['rescore_morse'],    $row['det_contrib'],       $row['id_rec_medment'],
    $row['req_pharm_review'],   $row['rev_curr_int'],     $row['id_add_int'],        $row['addr_bwlblddr'],
    $row['assure_safety_equip'],$row['id_env_issues'],    $row['id_learn_pref'],     $row['comm_team'],
    $row['comm_fam'],           $row['notify_attending'], $row['comm_name'],         $row['actions_descr'],
    $row['followup_descr'],     $row['rm_severity'],      $row['notified_ceo'],      $row['not_ceo_datetime'],
    $row['notified_ho'],        $row['not_ho_datetime'],  $row['notified_plant'],    $row['not_plant_datetime'],
    $row['notified_food'],      $row['not_food_datetime'],$row['notified_attending'],$row['not_sattending_datetime'],
    $row['notified_ins'],       $row['not_ins_datetime'], $row['notified_pharm'],    $row['not_pharm_datetime'],
    $row['notified_dno'],       $row['not_dno_datetime'], $row['notified_dto'],      $row['not_dto_datetime'],
    $row['notified_oth'],       $row['not_oth_datetime'], $row['not_oth_descr'],     $row['root_cause_an'],
    $row['rca_date'],           $row['rm_name'],          $row['rm_date'],           $row['Done']);
}

// generate excel file
$xls = new Excel_XML;
$xls->addArray ( $myheaderarray );
$xls->addArray ( $myarray );
$xls->generateXML ( "incidents" );

?>

But this outputs the information to the browser, not an Excel spreadsheet.  Did I do the header array incorrectly or is there another way to do this?  Should I upload the class-excel-xml.inc.php code to show what it does?

 

TIA

[jej1216]

The problem was completely unrelated to the php code.  When I wrapped it inside html, I got the error I described.  Keeping it pure php results in success!