Tuesday, 26 April 2016

EXPORT DATA TO EXCEL IN PHP

Question: I have recently made a website and have a members table in my sql. I have an admin page that has the ability to download that with column names; however I used to use INTO OUTFILE but that is not allowed on my host. I came up with this code to download the file:
<?php
if(file_exists('downloads/members_'.date("m-d-Y").'.csv') == true){
    header("Location: /downloads/members_".date("m-d-Y").".csv");
} 
$output=fopen('downloads/members_'.date("m-d-Y").'.csv','w+');
fputcsv($output,array(
                 'memberID','username','password','email',
                 'active','resetToken','resetComplete',
                 'support','supportToken'
             )
);
$rows =mysql_query("SELECT * FROM members;");
while($row=mysql_fetch_assoc($rows))
    fputcsv($output,$row);
header("Location: /downloads/members_".date("m-d-Y").".csv");
exit;
?>

This just outputs the column names and I have never used fputcsv before and am a tad confused on what I am doing wrong. I get the file to download but not with the table in it.
[EDIT] I just tried it with a valid sql query and it does not work. updated code above. Also There is stuff in the members table.
Answer: Try this and read the comments carefully:
<?php
try {
    /**************************************
    * Create sqllite3 database and        *
    * open connections                    *
    * Coded by Mawia HL for SO Answer     *
    **************************************/ 
    // Create (connect to) SQLite database in file
    $file_db = new PDO('sqlite:messaging.sqlite3');
    // Set errormode to exceptions
    $file_db->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
    $file_db->query("CREATE TABLE IF NOT EXISTS `members` (
               member_id INTEGER PRIMARY KEY, 
               username TEXT, 
               password TEXT, 
               email TEXT, 
               active TEXT, 
               resetToken TEXT, 
               resetComplete TEXT, 
               support TEXT, 
               supportToken TEXT
            )");
     $insert = "INSERT INTO members (member_id, username, password, 
                  email,active,resetToken,resetComplete,support,supportToken) 
         VALUES ('1', 'John', 'john123', 'john@gmail.com','2','4757ghf',
                '657hfd','657hdhd','857tydhdn')";
     $file_db->query($insert);
}catch(PDOException $e) {
    // Print PDOException message
     echo $e->getMessage();
  }
function getRow($result) {
     return $result->fetch(PDO::FETCH_ASSOC);
}
if (isset($_POST['download'])) {
    $result = $file_db->query('SELECT * FROM members');
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename=yourcsv.csv');
    header('Cache-Control: no-cache, no-store, must-revalidate');
    header('Pragma: no-cache');
    header('Expires: 0');
    $csvoutput = fopen('php://output', 'w');
    $row = getRow($result);
    $headers = array_keys($row);//columns from db e.g, memberID','username',...
    fputcsv($csvoutput, $headers);
    fputcsv($csvoutput, $row);
    while ($row = getRow($result)) {
        fputcsv($csvoutput, $row);
    }
    fclose($csvoutput);
    exit;
   }
$listing = $file_db->query('SELECT * FROM members');
?>
<h1>Members Listing</h1>
    <?php while ($row = getRow($listing)) { ?>
    <h2><?php echo $row['member_id']; ?></h2>
    <ul>
        <li>Member Name: <?php echo $row['username']; ?></li>
        <li>Password: <?php echo $row['password']; ?></li>
        <li>Email: <?php echo $row['email']; ?></li>
        <li>Active: <?php echo $row['active']; ?></li>
    </ul>
   <hr>
    <?php } ?>
   <form method="post">
        <fieldset>
            <legend>Download Results in CSV Format</legend>
            <p>
                <input type="submit" name="download"
                    id="download" value="Download File">
            </p>
        </fieldset>
    </form>

Here is real-time demo with sqllite3 database. Uncomment the create database and insert statement in order to see the working demo. I am using sqllite3 because it is testable online. You can easily integrate it in mysql database or any other databases.