Is it possible to export the results of a search into a excel document?

Question

I have a functioning search bar and a functioning excel writer but I'm unaware on what query I would use to export results from a search that has been made. Is this possible?

The code that I've got for the search bar is:

   <?php

    include ('database_conn.php');

    $output = '';

    if(isset($_POST['search'])) {
      $search = $_POST['search'];
      $search = preg_replace("#[^0-9a-z]i#","", $search);

      $query = mysqli_query($conn, "SELECT * FROM attendance WHERE stud_id LIKE '%$search%'") or die ("Could not search");
      $count = mysqli_num_rows($query);

      if($count == 0){
        $output = "There was no search results!";

      }else{

        while ($row = mysqli_fetch_array($query)) {


          $stud_id = $row ['stud_id'];
          $module = $row ['module'];
          $attendance_status = $row ['attendance_status'];

          $output .='<div> '.$stud_id.'    '.$module.'     '.$attendance_status.'</div>';
        }

      }
    }

    ?>

and then the code that I have for the excel writer is: (database connection is already on)

      $output = '';
      if(isset($_POST["export"]))
   {

   $result=mysqli_query($conn, "SELECT * FROM attendance  ");
   if(mysqli_num_rows($result) > 0)
   {
    $output .= '
     <table class="table" bordered="1">  
                      <tr>  
                           <th>Name</th>  
                           <th>Module</th>  
                           <th>Status</th>
                           <th>Date</th>   
                      </tr>
    ';
    while($row = mysqli_fetch_array($result))
    {
     $output .= '
      <tr>  
                           <td>'.$row["stud_id"].'</td>  
                           <td>'.$row["module"].'</td>  
                           <td>'.$row["attendance_status"].'</td>  
                           <td>'.$row["date"].'</td>  
                      </tr>
     ';
    }
    $output .= '</table>';
    header('Content-Type: application/xls');
    header('Content-Disposition: attachment; filename=download.xls');
    echo $output;
   }
  }
  ?>

I'm aware at the moment that my export button will just export everything from the attendance table, I'm not sure which query I would use to make it export the search results.

Answer 1

Instead of a custom class, I recommend using PhpSpreadsheet (the successor of PHPExcel). It allows you to add formatting, font styles, save as XLSX, etc.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Otherwise create a CSV string, set the content headers to write a XLS file, and Excel will read it just fine. E.g.

function export_excel_csv() {
    $conn = mysql_connect("localhost","root","");
    $db = mysql_select_db("database",$conn);
    $rec = mysql_query("SELECT * FROM table") or die (mysql_error());        
    $num_fields = mysql_num_fields($rec);        
    for($i = 0; $i < $num_fields; $i++ )
        $header .= mysql_field_name($rec,$i)."\\t";        
    while($row = mysql_fetch_row($rec)) {
        $line = '';
        foreach($row as $value)  {                   
            if((!isset($value)) || ($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 No Record Found!\n";                        

    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=reports.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\\n$data";
}

Regarding your question about the header type, check this post and try:

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');