Exporting Mysql table result to Excel,CSV or PDF

Question

I have a report generated from mysql database, but i want the user to be able to export this to an excel or pdf file. I need help on this. Pls see the code below:

    <h2>Staff Register</h2>
       <table align="justify" border="1" cellspacing="0" cellpadding="0" hspace="5px" vspace="5px">

        <tr>
        <th nowrap="nowrap"><div align="justify">Reg Date</div></th>
        <th nowrap="nowrap"><div align="justify">Staff No</div></th>
        <th nowrap="nowrap"><div align="justify">Surname</div></th>
        <th nowrap="nowrap"><div align="justify">Firstname </div></th>
         <th nowrap="nowrap"><div align="justify">Othername </div></th>
        <th nowrap="nowrap"><div align="justify">Phone No</div></th>
        <th nowrap="nowrap"><div align="justify">Sex</div></th>
        <th nowrap="nowrap"><div align="justify">Age </div></th>
        <th nowrap="nowrap"><div align="justify">Home Address </div></th>
        <th nowrap="nowrap"><div align="justify">E Mail </div></th>
        <th nowrap="nowrap"><div align="justify">Kin Name </div></th>
         <th nowrap="nowrap"><div align="justify">Kin Add </div></th>
          <th nowrap="nowrap"><div align="justify">Kin Phone </div></th>
           <th nowrap="nowrap"><div align="justify">Marital Status </div></th>
       </tr>

   <?php 
   // Perform Inspection 
    $confirm_select = "SELECT * FROM staff_tab  WHERE (status=1) ORDER BY staffno ASC";

    $query=$connection->query($confirm_select);

     while($result=mysqli_fetch_array($query)){
        echo "<tr>";
         echo "<td align='justify' nowrap='nowrap'>".$result['reg_date']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['staffno']."</td>";
         echo "<td align='justify' nowrap='nowrap'>".$result['surname']."</td>";
         echo "<td align='justify' nowrap='nowrap'>".$result['firstname']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['othername']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['mobile']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['sex']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['age']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['homeadd']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['emailadd']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['nextkin']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['kinadd']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['kinphone']."</td>";
        echo "<td align='justify' nowrap='nowrap'>".$result['marital_status']."</td>";

    }
    //to get total row count
    $res =$connection->query("SELECT staffno FROM staff_tab WHERE status=1");
    $pat= ($connection->affected_rows);
?>
</table>

//code for Exporting this table to Excel, CSV or PDF format.

http://stackoverflow.com/questions/4249432/export-to-csv-via-php — AVM, Oct 31 '14 at 07:02

ArtisticPhoenix · Accepted Answer · 2014-10-31T21:40:41.603

1

Just make a separate php file like this

header('Content-type: text/csv');
header('Content-Disposition: attachment; filename="filename.csv"');

$f = fopen('php://output', 'w');

$confirm_select = "SELECT * FROM staff_tab  WHERE (status=1) ORDER BY staffno ASC";
$query=$connection->query($confirm_select);

while($result=mysqli_fetch_array($query)){
    fputcsv( $f, $result );
}

Of course you have to connect to the database first. And link this to a button / form or what have you, I would do an iframe, etc.

Just for reference. Because its under used.

http://php.net/manual/en/wrappers.php.php

edited Oct 31 '14 at 21:40

answered Oct 31 '14 at 07:00

ArtisticPhoenix

21,464
2
24
38

@ArtisticPhoenix,I have done it that way, but it is not working. I added an hyperlink to under the table and created a new page with the code above: header('Content-type: text/csv'); header('Content-Disposition: attachment; filename="filename.csv"'); $f = fopen('staff_reg_report.php'); $confirm_select = "SELECT * FROM staff_tab WHERE (status=1) ORDER BY staffno ASC"; $query=$connection->query($confirm_select); while($result=mysqli_fetch_array($query)){ fputcsv( $f, $result ); } ?> – David Mukoro Oct 31 '14 at 08:24
@David Mukoro Sorry I missed the file mode fopen('php://output', 'w'); 'php://output' is a stream wrapper, and this one sends it directly to the output. This bypasses the need for a file altogether, and any code to remove said file after downloading. It also uses less resources. – ArtisticPhoenix Oct 31 '14 at 21:39

score 0 · Answer 2 · answered Oct 31 '14 at 09:44

I found this as the best answer to my question from solution to another person question. It works perfectly

<?php
    require_once("/includes/session.php");
     require_once("/includes/db_connection.php");
     require_once("/includes/functions.php");
    // Table Name that you want
    // to export in csv
    $ShowTable = "staff_tab";
     $today=date("dmY");
    $FileName = "StaffRecord".$today.". csv";
    $file = fopen($FileName,"w");

    $sql = mysqli_query($connection,("SELECT * FROM $ShowTable LIMIT 500"));
    $row = mysqli_fetch_assoc($sql);
    // Save headings alon
    $HeadingsArray=array();
    foreach($row as $name => $value){
    $HeadingsArray[]=$name;
    }
    fputcsv($file,$HeadingsArray);
    // Save all records without headings

    while($row = mysqli_fetch_assoc($sql)){
    $valuesArray=array();
    foreach($row as $name => $value){
    $valuesArray[]=$value;
    }
    fputcsv($file,$valuesArray);
    }
    fclose($file);

    header("Location: $FileName");

    echo "Complete Record saves as CSV in file: <b style=\"color:red;\">$FileName</b>";
    ?>

Exporting Mysql table result to Excel,CSV or PDF

2 Answers2