Written by The following will allow you to export your mysql queries from mysql to a csv file that can be opened in several spreadsheet softwares. You may need to change the , (comma) to a ; (semi-colon) depending on your software.
A note for those of you using my osTicket Reports MOD: This is not what I’m using for that.
<?php
$host = 'localhost';
$user = 'userName';
$pass = 'password';
$db = 'databaseName';
$table = 'tableName';
$file = 'export';
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>
A recommendation: the $filename must contain quotes.
http://www.w3.org/Protocols/rfc2616/rfc2616-sec19.html#sec19.5.1
Some times when a filename is “test file.txt” some browsers get stuck in “test”, they miss the “file.txt” part, so the line must be
header(“Content-disposition: filename=\””.$filename.”\”.csv”);
True, but in this case there will never be a space because we’re specificallly naming the file export_aTimeStamp.csv, so there is never any space present.
I do not see a purpose for
$i++;
in the first WHILE loop.
How is $i being used?
Good point. Perhaps it was code that I used initially but ended up not using. I’ll remove it. Thanks!
the i++ is needed in the first loop to create the upper limit for the column for next loop to write out the data rows
Ahh, yes. I missed the other reference to $i
Thanks Wooster, I’ve put the code back in! 😉
Hi there, It’s been a while since anybody has asked a question on this — hopefully you’re still fielding them!
I’ve found this and find it really useful as I’m very, very foetal in my PHP development (and aim to remain that way).
What would be the best way to restrict columns that are printed in the file, I’ve tried a couple of times to no avail. Once by adding a continue statement that hides certain fields and once by editing the show columns to only WHERE field IN as it worked in MySQL workbench…
Thanks in advance!
I don’t have time to work on this right now but I would think that you are on the right track with using ‘continue’ in one or more spots.
when i am trying to generate csv file which contains more than 25k rows. it returns me blank file. But when i only pull 22000 i can see the data but not when i pull 25k or more …is it something needs to do in excel?
What is the size of the file when you have over 25k entries? Does it actually get filled or does the sql query simply fail out and the file is empty?
Firstly, Let me describe little. I have used this code to execute my CSV output. It is working fine. Problem that I have created a LEFT JOIN query. Data of that query working fine. But, Must I have to select table column. Otherwise, it is giving me the blank CSV file. How may I resolve the problem?
create the sql as a view , then you can just refence that view and not worry about the outerjoin.
let the database do the work , not your webserver.
Can you give an example? I don’t believe I’ve ever done anything with a “view”.
Thanks for the comment!
Hi all — just looking/trying the code and it works ok — but how can I select columns from a table to export rather than the whole table. 🙂
Any clues.
Thanks