Extract data from MySQL into CSV
Moderator: General Moderators
Extract data from MySQL into CSV
I'm not sure if this thread goes here or the PHP section or perhaps no where at all...but here's what I want to do. I hope you will be kind enough to show me or point me to where this task can be done.
I have a website that gathers and store information. Every now and then I want to extract information from the MySQL database. However, during the extraction of data, I want to extract into a .csv file where the admin user can download the file and open it in Excel. Can this be done in PHP, MySQL, or is it a third party software that do this?
Many thanks in advance.
ljCharlie
I have a website that gathers and store information. Every now and then I want to extract information from the MySQL database. However, during the extraction of data, I want to extract into a .csv file where the admin user can download the file and open it in Excel. Can this be done in PHP, MySQL, or is it a third party software that do this?
Many thanks in advance.
ljCharlie
-
leenoble_uk
- Forum Contributor
- Posts: 108
- Joined: Fri May 03, 2002 10:33 am
- Location: Cheshire
- Contact:
One way to do it.
Make a plain text file and type this on one line and save it.
Say you save this script in the directory /Users/me/sqlscripts/backup.sql
then you need to set up a cron like this
0 0 * * * /usr/local/bin/mysql -u username --password=yourpass databasename < /Users/me/sqlscripts/backup.sql
Where the path to your mysql installation is there instead of /usr/local/bin/mysql.
This would now backup your database to the /path/where/you/want/ as a file called it.csv every day at midnight.
You can then open the csv file in excel. Of course you might be unhappy about storing the connection password that way. You could always set up a user with only SELECT privileges.
Also you'll have to make sure the directory has write privileges for the mysql user or it won't work.
Make a plain text file and type this on one line and save it.
Code: Select all
SELECT * INTO OUTFILE "/path/where/you/want/it.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM myTable;then you need to set up a cron like this
0 0 * * * /usr/local/bin/mysql -u username --password=yourpass databasename < /Users/me/sqlscripts/backup.sql
Where the path to your mysql installation is there instead of /usr/local/bin/mysql.
This would now backup your database to the /path/where/you/want/ as a file called it.csv every day at midnight.
You can then open the csv file in excel. Of course you might be unhappy about storing the connection password that way. You could always set up a user with only SELECT privileges.
Also you'll have to make sure the directory has write privileges for the mysql user or it won't work.
Easy...
Save this code in a file called export_csv.php
Then, create a link to the file. Not a normal link though, you need to trick the browser into thinking it is downloading a CSV and not a PHP file to do this.
Normally, your link would look like this
http://www.someurl/some/path/to/script/export_csv.php
but what you need to do is add an extra bit on the end like this to fool the browser
http://www.someurl/some/path/to/script/ ... export.csv
Should work a treat
Mark
Save this code in a file called export_csv.php
Code: Select all
<?php
//Send the headers
Header ("Content-type: text/csv");
// Do your DB connection stuff here
// Do your query
$query = "SELECT * FROM sometable ORDER BY somecolumn";
// Execute the query
$result = mysql_query($query) or die(mysql_error());
// Output results separted with commas
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "".$line['id'].",".$line['company'].",".$line['name'].",".$line['position'].",".$line['address'].",".$line['notes'].",".$line['tel'].",".$line['direct_line'].",".$line['fax'].",".$line['mobile'].",".$line['email'].",".$line['web']."\n";
}
?>Normally, your link would look like this
http://www.someurl/some/path/to/script/export_csv.php
but what you need to do is add an extra bit on the end like this to fool the browser
http://www.someurl/some/path/to/script/ ... export.csv
Should work a treat
Mark
Many thanks for your help. I have seen this sort of things on another website that allows me to query the database and then after querying the database, a link is provided on the page for me to download the .csv file. I guess the biggest part I don't know is the query to OUTFILE as csv file. Now, once I have query to Outfile and save as a test.csv file, I can use php to provide a link to that file for the user to download, correct?
Well, I will give that a try.
Again, thanks!
ljCharlie
Well, I will give that a try.
Again, thanks!
ljCharlie
-
leenoble_uk
- Forum Contributor
- Posts: 108
- Joined: Fri May 03, 2002 10:33 am
- Location: Cheshire
- Contact:
You can ignore my post. Just do what Bech100 said. This file will become your CSV file and download whenever you link to it.
I've done this on one of my sites before but forgot about it because I was concentrating on the download location for an admin you mentioned.
Bech's way is simpler and will create the CSV file on the fly with the current info. Mine will provide a daily backup at a specific time.
Bech100 - Does the trick with the URL you used there fix the stupid behaviour of IE which leaves the php extension on the file even if you attach this header:
Header("Content-Disposition: attachment; filename=spreadsheet.csv");
I've done this on one of my sites before but forgot about it because I was concentrating on the download location for an admin you mentioned.
Bech's way is simpler and will create the CSV file on the fly with the current info. Mine will provide a daily backup at a specific time.
Bech100 - Does the trick with the URL you used there fix the stupid behaviour of IE which leaves the php extension on the file even if you attach this header:
Header("Content-Disposition: attachment; filename=spreadsheet.csv");
Many thanks for both of your help. Bech100, your code seemed very straight forward and I like that...however, I did not see where you specify the name of the CSV file. In your code you used the export.csv as an example file name, but I did not see you specify the "export.csv" anywhere in your code so how do I know which file to link to? On other thing, can you explain why we need to declare the Header ("Content-type: text/csv"); ?
Thanks!
ljCharlie
Thanks!
ljCharlie
Not sure to be honest, never used it for anything else, i think it would though. There is another fix i use to solve that problem!leenoble_uk wrote:Bech100 - Does the trick with the URL you used there fix the stupid behaviour of IE which leaves the php extension on the file even if you attach this header:
Code: Select all
header("Content-Type: application/octet-stream");
header("Content-Type: application/pdf");
header("Content-Length: $size");
// IE5.5 just downloads index.php if we don't do this
if(preg_match("/MSIE 5.5/", $HTTP_USER_AGENT)) {
header("Content-Disposition: filename=$file");
} else {
header("Content-Disposition: attachment; filename=$file");
}
header("Content-Transfer-Encoding: binary");The name of the file you will download is at the end of the second URL in my post above. Just change that to whatever you want! It doesn't need to be defined in the script anywhereljCharlie wrote:I did not see where you specify the name of the CSV file
I just use every method possible to make sure that the browser thinks it is downloading a CSV file and not a PHP fileljCharlie wrote:On other thing, can you explain why we need to declare the Header ("Content-type: text/csv");
Mark
Okay, I'm going to need your help again. I have done what Bech100 have told me but here's the error:
echo 'Click<a href="http://www.home/temp/regUsersSuccessExt ... ByDate.csv"> here</a> to download the file.';
When I lick on it, I have the above warning regarding the header information.
I would appreciated it very much if you can help me again.
Thanks!
ljCharlie
And then the link wasn't working. Here's what I have for the link.Warning: Cannot add header information - headers already sent by (output started at /www/home/temp/regUsersSuccessExtract.php:8) in /www/home/temp/regUsersSuccessExtract.php on line 51
echo 'Click<a href="http://www.home/temp/regUsersSuccessExt ... ByDate.csv"> here</a> to download the file.';
When I lick on it, I have the above warning regarding the header information.
I would appreciated it very much if you can help me again.
Thanks!
ljCharlie
So what should I do? Where should I put the header then?
Header ("Content-type:text/csv");
is the header line I use from Bech100. Currently I have the header line right below the declaration of variables and get the variables from the previous page. Should I put it above that or where?
Thanks for your suggestion.
ljCharlie
Header ("Content-type:text/csv");
is the header line I use from Bech100. Currently I have the header line right below the declaration of variables and get the variables from the previous page. Should I put it above that or where?
Thanks for your suggestion.
ljCharlie