Mysql download script to extract rows from table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

kbensch
Forum Newbie
Posts: 6
Joined: Thu Jan 29, 2009 11:42 am

Mysql download script to extract rows from table

Post by kbensch »

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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Mysql download script to extract rows from table

Post by jaoudestudios »

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.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Mysql download script to extract rows from table

Post by Mark Baker »

Or write your data to a real Excel file, rather than to a CSV file
kbensch
Forum Newbie
Posts: 6
Joined: Thu Jan 29, 2009 11:42 am

Re: Mysql download script to extract rows from table

Post by kbensch »

hi

Thank you for the reply, but where in my script would I do this?

Kobus
kbensch
Forum Newbie
Posts: 6
Joined: Thu Jan 29, 2009 11:42 am

Re: Mysql download script to extract rows from table

Post by kbensch »

Hi Mark

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

Post by mickeyunderscore »

As mentioned, you need to surround the data with double quotes ("), you also need to escape all double quotes with another double quote, so:

Code: Select all

$csv_output .= $row['Field'].", ";
Should become:

Code: Select all

$csv_output .= '"'.str_replace('"', '""', $row['Field']).'",';
kbensch
Forum Newbie
Posts: 6
Joined: Thu Jan 29, 2009 11:42 am

Re: Mysql download script to extract rows from table

Post by kbensch »

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
mickeyunderscore
Forum Contributor
Posts: 129
Joined: Sat Jan 31, 2009 9:00 am
Location: UK

Re: Mysql download script to extract rows from table

Post by mickeyunderscore »

Did you also change

Code: Select all

$csv_output .= $rowr[$j].", ";
To:

Code: Select all

$csv_output .= '"'.str_replace('"', '""',  $rowr[$j]).'",';
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.
kbensch
Forum Newbie
Posts: 6
Joined: Thu Jan 29, 2009 11:42 am

Re: Mysql download script to extract rows from table

Post by kbensch »

Hi

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

Post by Mark Baker »

kbensch wrote:
markbaker wrote:Or write your data to a real Excel file, rather than to a CSV file
How would i do that?
Using a library like PHPExcel which provides a set of functions to build a workbook and write it to an Excel file
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Mysql download script to extract rows from table

Post by Skoalbasher »

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?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Mysql download script to extract rows from table

Post by jaoudestudios »

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.
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Mysql download script to extract rows from table

Post by Skoalbasher »

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.
That's sweet. What's a "cron job" though? Is that something that you set up to run at a certain time?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Mysql download script to extract rows from table

Post by jaoudestudios »

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.
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Mysql download script to extract rows from table

Post by Skoalbasher »

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.
Post Reply