PHP / MySQLi Excel export has no column headers

Question

I have some code I used to export to Excel using MySQL. I'm switching the syntax over to MySQLi, and it works except it's not printing the column headers in Excel.

I am new to MySQLi.

Here is the code (I'll try to leave out unnecessary code):

 <?php
 include("../include/database.php");
 global $ts;

 $ts = date('mdY-His');
 session_start();
 $where = $_SESSION['where'];

 $sql = "SELECT * FROM `main_table` WHERE " . $where . "";
 $result = mysqli_query($dbc, $sql) or die(mysqli_error());

 header("Content-Type: application/xls"); 
 header("Content-Disposition: attachment; filename=importdetails-".$ts.".xls");  
 header("Pragma: no-cache"); 
 header("Expires: 0");
 header("Content-Transfer-Encoding: binary ");

 // here is the formatting for Excel

 $sep = "\t"; //tabbed character

 // printing the column names

 for ($i = 0; $i < mysqli_num_fields($result); $i++) {
   echo mysqli_fetch_field($result,$i) . "\t";
 }

 print("\n");   

 // I believe the error is in the FOR loop above
 // Here is the remaining code

 while($row = mysqli_fetch_row($result))
 {
   $schema_insert = "";
   for($j = 0; $j < mysqli_num_fields($result); $j++)
   {
     if(!isset($row[$j]))
       $schema_insert .= "NULL".$sep;
     elseif ($row[$j] != "")
       $schema_insert .= "$row[$j]".$sep;
     else
       $schema_insert .= "".$sep;
   }
   $schema_insert = str_replace($sep."$", "", $schema_insert);
   $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
   $schema_insert .= "\t";
   print(trim($schema_insert));
   print "\n";
 }
 ?>

Using the code above, I can get it to export an Excel document, but there are no column headers. In the FOR loop, in the ECHO statement, I had mysql_field_name, so I switched it to mysqli_field_name, only realizing mysqli_field_name doesn't exist. So I used mysqli_fetch_field.

Why am I not getting the Excel column names?

Edit

This is what worked for me:

 <?php
 include("../include/database.php");
 global $ts; 

 $ts = date('mdY-His');
 session_start();
 $where = $_SESSION['where'];

 $sql = "SELECT * FROM `vsl_profile` WHERE " . $where . ""; 

 header("Content-Type: application/xls"); 
 header("Content-Disposition: attachment; filename=importdetails-".$ts.".xls");  
 header("Pragma: no-cache"); 
 header("Expires: 0");
 header("Content-Transfer-Encoding: binary ");

 $sep = "\t"; //tabbed character

 if($result = $dbc->query($sql))
 {
   while($finfo = $result->fetch_field())
   {
     printf($finfo->name . $sep);
   }
 }

 print("\n");    

 while($row = mysqli_fetch_row($result))
 {
   $schema_insert = "";
   for($j = 0; $j < mysqli_num_fields($result); $j++)
   {
     if(!isset($row[$j]))
       $schema_insert .= "NULL".$sep;
     elseif ($row[$j] != "")
       $schema_insert .= "$row[$j]".$sep;
     else
       $schema_insert .= "".$sep;
   }
   $schema_insert = str_replace($sep."$", "", $schema_insert);
   $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
   $schema_insert .= "\t"; 
   print(trim($schema_insert));
   print "\n";
 }
 ?>

Answer 1

I believe mysql_fetch_field only takes one parameter.

Try something like:

while ($finfo = mysqli_fetch_field($result)) {
    echo $finfo->name;
}

Taken from this page doc page: http://php.net/manual/en/mysqli-result.fetch-field.php