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.
