Mysql download script to extract rows from table
Moderator: General Moderators
Mysql download script to extract rows from table
Hi everyone
I need some help please.
I have created the following php script that connects to and then downloads the contents of a mysql table to csv format. This means that when the user clicks on it it opens in excel. The problem I have is that when there is a comma in the field and I open the CSV in excel the fields do not line up. How can I change my code to account for this?
My efforts so far has been futile.
Oh, I am a newbie at PHP programming.
<?php
$SQL_HOST = 'localhost';
$DB_USER = 'localuser';
$DB_PASSWORD = 'localpwd';
$DB_SELECTED = 'localdb';
$DB_MYTABLE = 'localtable';
$file = 'export';
$link = mysql_connect($SQL_HOST, $DB_USER, $DB_PASSWORD) or die("Can not connect to DB." . mysql_error());
mysql_select_db($DB_SELECTED) or die("Can not connect to TABLE.");
$result = mysql_query("SHOW COLUMNS FROM ".$DB_MYTABLE."");
$i = 0;
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 ".$DB_MYTABLE."");
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;
?>
Thanks for any help
Kobus
I need some help please.
I have created the following php script that connects to and then downloads the contents of a mysql table to csv format. This means that when the user clicks on it it opens in excel. The problem I have is that when there is a comma in the field and I open the CSV in excel the fields do not line up. How can I change my code to account for this?
My efforts so far has been futile.
Oh, I am a newbie at PHP programming.
<?php
$SQL_HOST = 'localhost';
$DB_USER = 'localuser';
$DB_PASSWORD = 'localpwd';
$DB_SELECTED = 'localdb';
$DB_MYTABLE = 'localtable';
$file = 'export';
$link = mysql_connect($SQL_HOST, $DB_USER, $DB_PASSWORD) or die("Can not connect to DB." . mysql_error());
mysql_select_db($DB_SELECTED) or die("Can not connect to TABLE.");
$result = mysql_query("SHOW COLUMNS FROM ".$DB_MYTABLE."");
$i = 0;
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 ".$DB_MYTABLE."");
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;
?>
Thanks for any help
Kobus
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Mysql download script to extract rows from table
Encapsulate each field in double quotes.
i.e.
"field1", "field2", "field3"
"field1", "field2", "field3"
"field1", "field2", "field3"
Therefore if you have a comma in one of the fields it does not matter.
i.e.
"field1", "field2", "field3"
"field1", "field2", "field3"
"field1", "field2", "field3"
Therefore if you have a comma in one of the fields it does not matter.
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: Mysql download script to extract rows from table
Or write your data to a real Excel file, rather than to a CSV file
Re: Mysql download script to extract rows from table
hi
Thank you for the reply, but where in my script would I do this?
Kobus
Thank you for the reply, but where in my script would I do this?
Kobus
Re: Mysql download script to extract rows from table
Hi Mark
How would i do that?
Kobus
How would i do that?
Kobus
-
mickeyunderscore
- Forum Contributor
- Posts: 129
- Joined: Sat Jan 31, 2009 9:00 am
- Location: UK
Re: Mysql download script to extract rows from table
As mentioned, you need to surround the data with double quotes ("), you also need to escape all double quotes with another double quote, so:
Should become:
Code: Select all
$csv_output .= $row['Field'].", ";Code: Select all
$csv_output .= '"'.str_replace('"', '""', $row['Field']).'",';Re: Mysql download script to extract rows from table
Hi mickeyunderscore
Thank you for this, but did not work. When i export to csv and then open in excel still gives me new column after the comma say in the address.
Kobus
Thank you for this, but did not work. When i export to csv and then open in excel still gives me new column after the comma say in the address.
Kobus
-
mickeyunderscore
- Forum Contributor
- Posts: 129
- Joined: Sat Jan 31, 2009 9:00 am
- Location: UK
Re: Mysql download script to extract rows from table
Did you also change
To:
This should work for you, as I have the same thing in my CSV class and it exports a lot of data on a daily basis and works fine.
Code: Select all
$csv_output .= $rowr[$j].", ";Code: Select all
$csv_output .= '"'.str_replace('"', '""', $rowr[$j]).'",';Re: Mysql download script to extract rows from table
Hi
silly me, yes that works a treat, thank you very much for the help.
Kobus
silly me, yes that works a treat, thank you very much for the help.
Kobus
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: Mysql download script to extract rows from table
Using a library like PHPExcel which provides a set of functions to build a workbook and write it to an Excel filekbensch wrote:How would i do that?markbaker wrote:Or write your data to a real Excel file, rather than to a CSV file
- Skoalbasher
- Forum Contributor
- Posts: 147
- Joined: Thu Feb 07, 2008 8:09 pm
Re: Mysql download script to extract rows from table
Is it possible to do this as a backup? Like download as an excel file or whatnot (this is actually something i need to do), and then upload it back if need be?
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Mysql download script to extract rows from table
I would keep it as a csv if you are going to use it for a backup.
You could setup a cron job to send you the database dump every night.
A database dump would be better than csv, because it would contain the structure as well as the data.
You could setup a cron job to send you the database dump every night.
A database dump would be better than csv, because it would contain the structure as well as the data.
- Skoalbasher
- Forum Contributor
- Posts: 147
- Joined: Thu Feb 07, 2008 8:09 pm
Re: Mysql download script to extract rows from table
That's sweet. What's a "cron job" though? Is that something that you set up to run at a certain time?jaoudestudios wrote:I would keep it as a csv if you are going to use it for a backup.
You could setup a cron job to send you the database dump every night.
A database dump would be better than csv, because it would contain the structure as well as the data.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Mysql download script to extract rows from table
Yep, you schedule a cron to run at a certain time, or day etc...there are plenty of options. It can run anything, it is basic but quite amazing.
If you only want the latest (i.e. most up-to-date database dump) then that alone is suitable (you will still have to get the file off the server). But if you want to keep each version from each night but dont want thousands of files, then svn solves this issue well and it is useful for many other things too. You are probably thinking well, the latest version is fine, but want happens if your data gets corrupted or polluted? And you did not find out till a few days later, then your latest database dump would be useless.
If you only want the latest (i.e. most up-to-date database dump) then that alone is suitable (you will still have to get the file off the server). But if you want to keep each version from each night but dont want thousands of files, then svn solves this issue well and it is useful for many other things too. You are probably thinking well, the latest version is fine, but want happens if your data gets corrupted or polluted? And you did not find out till a few days later, then your latest database dump would be useless.
- Skoalbasher
- Forum Contributor
- Posts: 147
- Joined: Thu Feb 07, 2008 8:09 pm
Re: Mysql download script to extract rows from table
You know where I could go to find something about that?
My stuff is hosted on another server that I pay for. The data i'm going to be storing is not super sensative, but is super important. So a backup everyday would be a plus. Or something the user could back up.
I'd like to make an app that works with my website and that the user can sync their database with. But that's going to be much much further down the road. Need to get the site developed and running first.
My stuff is hosted on another server that I pay for. The data i'm going to be storing is not super sensative, but is super important. So a backup everyday would be a plus. Or something the user could back up.
I'd like to make an app that works with my website and that the user can sync their database with. But that's going to be much much further down the road. Need to get the site developed and running first.