Page 1 of 2
Extract data from MySQL into CSV
Posted: Wed May 19, 2004 8:23 am
by ljCharlie
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
Posted: Wed May 19, 2004 8:55 am
by leenoble_uk
One way to do it.
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;
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.
Posted: Wed May 19, 2004 8:57 am
by JayBird
Easy...
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";
}
?>
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
Posted: Wed May 19, 2004 9:05 am
by ljCharlie
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
Posted: Wed May 19, 2004 9:10 am
by leenoble_uk
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");
Posted: Wed May 19, 2004 9:21 am
by ljCharlie
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
Posted: Wed May 19, 2004 9:36 am
by JayBird
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:
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!
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");
ljCharlie wrote:I did not see where you specify the name of the CSV file
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 anywhere
ljCharlie wrote:On other thing, can you explain why we need to declare the Header ("Content-type: text/csv");
I just use every method possible to make sure that the browser thinks it is downloading a CSV file and not a PHP file
Mark
Posted: Wed May 19, 2004 9:59 am
by ljCharlie
Very nice! I'll give that a try and see what happens.
Thanks!
ljCharlie
Posted: Mon May 24, 2004 8:41 am
by ljCharlie
Okay, I'm going to need your help again. I have done what Bech100 have told me but here's the error:
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
And then the link wasn't working. Here's what I have for the link.
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
Posted: Mon May 24, 2004 8:44 am
by feyd
regUsersSuccessExtract.php line 8 wrote out data before the header() functions were hit.
Posted: Mon May 24, 2004 8:46 am
by ljCharlie
By the way, do I need to use echo in the While loop? This echo print out everything on the page and I don't want that. Is it something I must have?
ljCharlie
Posted: Mon May 24, 2004 8:57 am
by 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
Posted: Mon May 24, 2004 8:58 am
by feyd
either the dirty approach of using output buffering.. or ensure that no output is made until after the header() calls.
Posted: Mon May 24, 2004 9:04 am
by ljCharlie
What are the disadvantages and advantages of those two approaches? If I decide to do a output buffering, what are the steps?
ljCharlie
Posted: Mon May 24, 2004 9:12 am
by feyd
[php_man]ob_start[/php_man]
most of the time, if output buffering is used, it's avoiding sloppy code, from what I've seen.
In the long run of things, it's usually better to make sure there's no output before all headers and such have been output..