Export mysqli data to Excel

Recently the folks that run the server where my website is housed, upgraded both PHP and MYSQL. That meant that I had to also upgrade from mysql to mysqli. My website is written in Perl, but I store sales data in a mysql database and present past sales data in tables using PHP and mysql. I have managed to modify all of the tables and they work well.

However, on one table the user can click a button to export the data into a cvs file that will open in Excel. This export option had been working fine for several years, until the upgrade and I have been unable to fix it.

If I have error checking turned on I receive several errors that confuse me as to the problem. If I bypass the error checking, the export continues by opening Excel, but the spreadsheet is blank with no data.

Here are the errors:

*Notice: Undefined variable: link in /home/jeffer36/public_html/export-excel-admin.php on line 21

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in /home/jeffer36/public_html/export-excel-admin.php on line 21

Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, null given in /home/jeffer36/public_html/export-excel-admin.php on line 22

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /home/jeffer36/public_html/export-excel-admin.php on line 34*

The undefined variable error is odd because the variable is defined above.

Anyway here is my code:

<?php     ini_set('display_errors', 1);  ini_set('display_startup_errors', 1);  error_reporting(E_ALL);  $host = 'localhost'; // MYSQL database host adress   $db = 'jeffer36_closedauctions'; // MYSQL database name   $user = 'jeffer36'; // Mysql Datbase user   $pass = 'Fishhead1'; // Mysql Datbase password      // Connect to the database   $link = mysqli_connect("localhost", "jeffer36", "Fishhead1", "jeffer36_closedauctions");  mysqli_select_db($link, "jeffer36_closedauctions");     $table="closedauctions"; // this is the tablename that you want to export to csv from mysql.     exportMysqlToCsv($table);     function exportMysqlToCsv($table, $filename = "export.csv") {       $csv_terminated = "\n";       $csv_separator = ",";       $csv_enclosed = '"';       $csv_escaped = "\\";       $sql_query = "select ID, category, seller, title, bids, pounds, description, winningbid, price_per_pound, buyerusername, month, day, year from $table";    // Gets the data from the database   $result = mysqli_query($link, $sql_query);       $fields_cnt = mysqli_num_fields($result);         $schema_insert = '';         for ($i = 0; $i < $fields_cnt; $i++){       $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,                       stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;                   $schema_insert .= $l;                   $schema_insert .= $csv_separator;       } // end for              $out = trim(substr($schema_insert, 0, -1));            $out .= $csv_terminated;                     // Format the data            while ($row = mysqli_fetch_array($result)){                   $schema_insert = '';                   for ($j = 0; $j < $fields_cnt; $j++){                           if ($row[$j] == '0' || $row[$j] != ''){                                     if ($csv_enclosed == ''){                                           $schema_insert .= $row[$j];}                   else {                                           $schema_insert .= $csv_enclosed .                                            str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;}                  } else {                                       $schema_insert .= '';}                                      if ($j < $fields_cnt - 1){                                        $schema_insert .= $csv_separator;}                                }   // end for                  $out .= $schema_insert;                $out .= $csv_terminated;}        //end while              header("Cache-Control: must-revalidate, post-check=0, pre-check=0");            header("Content-Length:" . strlen($out));            header("Content-type: text/csv");            header('Content-Disposition: attachment; filename="export.csv"');                    echo $out;            exit;          }?>  

Any help getting this working again would be greatly appreciated.

Pete

https://stackoverflow.com/questions/66485300/export-mysqli-data-to-excel March 05, 2021 at 09:07AM