Query produces data, but the export is blank

Question

I was recently tasked to update some older sites from MySQL to MySQLi.

Though slow and steady, the update has been ok until I ran into an issue when exporting some data to an excel document.

This code was written by a previous developer. There's a lot going on in the file, and I hope I'm grabbing the part that is supposed to be creating the excel document:

<?php
  $export = mysqli_query ( $session->connDB(),$sql ) or die ( "Sql error : " . mysqli_error( ) );
  $fields = mysqli_num_fields ( $export );
  $num_rows = mysqli_num_rows( $export );

  $pattern = '/[A-Z0-9][A-Z0-9._-]+@[A-Z0-9][A-Z0-9.-]{0,61}[A-Z0-9]\.[A-Z.]{2,6}/i'; //email
  $phonpat = '/(\(?([0-9]{3})+\)?[\-\s\.]?([0-9]{3})+[\-\s\.]?([0-9]{4})(( |-|\.)?[ext\.]+ ?\d+)|\(?([0-9]{3})+\)?[\-\s\.]?([0-9]{3})+[\-\s\.]?([0-9]{4}))/i'; //telephone
  $phPat = '/([0-9]{3})([0-9]{3})([0-9]{4})/';
  $vippatt = '/VIP/i';

  for($f=0; $f<$fields; $f++){
    $header.='"'.mysqli_fetch_fields($export, $f).'"'."\t"; 
  }

  for($i=0; $i<$num_rows; $i++){
    for($x=0; $x<$fields; $x++){
      $email = mysqli_fetch_assoc($export,$i,"EMAIL");
      $phone = mysqli_fetch_assoc($export,$i,"PHONE");
      $viprm = mysqli_fetch_assoc($export,$i,"VIP");

      preg_match ($pattern, $email, $matches);
      preg_match ($phonpat, $phone, $phoneno);
      preg_match ($vippatt, $viprm, $vpmatch);

      if(isset($matches[0])) {$emal=strtolower($matches[0]);} else {$emal="";}
      if(isset($vpmatch[0])) {$vips=strtoupper($vpmatch[0]);} else {$vips="";}
      if(isset($phoneno[0])) {$phne=preg_replace($phPat,'($1) $2-$3 ',formatPhone($phoneno[0],false,false));} else {$phne="";}
      
      if(mysqli_fetch_fields($export, $x)=='EMAIL'){
        $fld=$emal;
      } else {
        if(mysqli_fetch_fields($export, $x)=='PHONE'){
          $fld=$phne;
        } else {
          if(mysqli_fetch_fields($export, $x)=='VIP'){
            $fld=$vips;
          } else {
            if(mysqli_fetch_fields($export, $x)=='UNITS'){
              $fld=1;
            } else {
              $fld = mysqli_fetch_assoc($export,$i,mysqli_fetch_fields($export, $x));
            }
          }
        }
      }
      $data.= '"'.$fld.'"'."\t";
    }
    $data.="\n";
  }
?>

Here is where the code checks if the data is blank or not, and then exports the spreadsheet:

<?php
  if ($data == "") {
    $data = "\nNo records found for your search parameters.\n\n".$sql;
  } else {
    echo "should show data";
  }

  global $time;
  $time = time();
  header("Content-Disposition: attachment; filename=CargoManagementCustomReport-$time.xls");
  header("Pragma: no-cache");
  header("Expires: 0");
  print "$header\n$data";
?>

When the spreadsheet gets exported, I see "should show data". This tells me the $data variable obviously has data. It's just not getting into the spreadsheet.

If you'll notice in the above, I'm using mysqli_fetch_fields. This was used to replace mysql_field_name (in my attempt to update to MySQLi).

I also tried mysqli_fetch_field, but got the same results.

I am getting no errors, but the spreadsheet is still blank.

I can echo $sql to get the query, and I can run the query in the database and it returns data.

What am I doing wrong and how can I fix it?

Answer 1

That whole code is gibberish, so I hope I understood what it is that it was meant to do.

Here are the main problems:

1. mysqli_fetch_fields() takes only 1 argument and returns an array of objects. You can't cast an array to a string. I assume you wanted to get the field name.
2. mysqli_fetch_assoc() takes only 1 argument and returns an array of data in an associative array as the name suggests. It also moves the internal pointer to the next row every time it is called. You are trying to use it as if it was mysql_result().
3. Your nested loops are very messy. I replaced them with simple foreach loops and replaced the nested if statements with a switch. While I would normally stay away from such constructs, this is the easiest way to migrate this code.

After removing all the mysqli nonsense, the code is now readable. It iterates over every field of every row, applying some transformations to some fields and concatenating the result into a string.

Fixed code:

$conn = $session->connDB();
$export = mysqli_query($conn, $sql);

$pattern = '/[A-Z0-9][A-Z0-9._-]+@[A-Z0-9][A-Z0-9.-]{0,61}[A-Z0-9]\.[A-Z.]{2,6}/i'; //email
$phonpat = '/(\(?([0-9]{3})+\)?[\-\s\.]?([0-9]{3})+[\-\s\.]?([0-9]{4})(( |-|\.)?[ext\.]+ ?\d+)|\(?([0-9]{3})+\)?[\-\s\.]?([0-9]{3})+[\-\s\.]?([0-9]{4}))/i'; //telephone
$phPat = '/([0-9]{3})([0-9]{3})([0-9]{4})/';
$vippatt = '/VIP/i';

foreach (mysqli_fetch_fields($result) as $field) {
    $header .= '"' . $field->name . '"' . "\t";
}

$data = '';
foreach ($export as $row) {
    foreach ($rows as $fieldName => $value) {
        switch ($fieldName) {
            case 'EMAIL':
                preg_match($pattern, $value, $matches);
                $data .= '"' . (isset($matches[0]) ? strtolower($matches[0]) : '') . '"' . "\t";
                break;
            case 'PHONE':
                preg_match($phonpat, $value, $phoneno);
                $phne = "";
                if (isset($phoneno[0])) {
                    $phne = preg_replace($phPat, '($1) $2-$3 ', formatPhone($phoneno[0], false, false));
                }
                $data .= '"' . $phne . '"' . "\t";
                break;
            case 'VIP':
                preg_match($vippatt, $value, $vpmatch);
                $data .= '"' . (isset($vpmatch[0]) ? strtolower($vpmatch[0]) : '') . '"' . "\t";
                break;
            case 'UNITS':
                $data .= '"1"' . "\t";
                break;
            default:
                $data .= '"' . $value . '"' . "\t";
                break;
        }
    }

    $data .= "\n";
}