| Send .csv file to browser [message #170003] |
Tue, 05 October 2010 14:19  |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
I want to use MySQL to get a list of email addresses and names and then
output a .csv file to the browser so that it can be saved and then
imported into Thunderbird etc.
Having googled around I think the solution will be:
1. Create and open a local file for writing
2. Write the data to the local file and close it
3. Use appropriate header files and then readfile()
4. Delete the local file
Questions:
1. Is this a reasonable approach? Or should I be outputting to the
browser on the fly? Or using PEAR or something like that?
2. Can the headers appear appear anywhere in the script or must they be
be the first lines?
Other questions depend on your answers to those two, so I'll keep them to
myself for the moment.
<off topic>
I've exported from TB to see what its .csv output file looks like.
1. Do I really need to include all those fields?
2. Will, for example, OE import them properly?
</OT>
tia
Derek
|
|
|
|
|
|
| Re: Send .csv file to browser [message #170005 is a reply to message #170004] |
Tue, 05 October 2010 14:55  |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 10:20 AM, Derek Turner wrote:
> On Tue, 05 Oct 2010 14:19:04 +0000, Derek Turner wrote:
>
>
>>
>> 3. Use appropriate header files and then readfile()
>
> Sorry lines/statements
Why not simply
$theData = getDataFromTableAsArrayOfNonAssociativeArrays();
foreach ($theData as $rec) {
$line = implode(',', $rec);
print $line . '<br>';
}
The user can then cut and paste it into a file or you can put in code
for the user to download that piece.
Another approach is to output an Excel file and the user can then output
from that Excel file to a .csv file.
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170006 is a reply to message #170005] |
Tue, 05 October 2010 15:06 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Tue, 05 Oct 2010 10:55:44 -0400, sheldonlg wrote:
> The user can then cut and paste it into a file or you can put in code
> for the user to download that piece.
>
> Another approach is to output an Excel file and the user can then output
> from that Excel file to a .csv file.
Why not? Because it's not very elegant, is it?
|
|
|
|
| Re: Send .csv file to browser [message #170007 is a reply to message #170006] |
Tue, 05 October 2010 15:11 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 11:06 AM, Derek Turner wrote:
> On Tue, 05 Oct 2010 10:55:44 -0400, sheldonlg wrote:
>
>> The user can then cut and paste it into a file or you can put in code
>> for the user to download that piece.
>>
>> Another approach is to output an Excel file and the user can then output
>> from that Excel file to a .csv file.
>
> Why not? Because it's not very elegant, is it?
WGAS? Why does something have to be "elegent"? All it has to do is:
1 - Work reliably.
2 - Be maintainable.
Besides, haven't you ever heard of K.I.S.S. or Occam's razor?
(WGAS means "who gives a shit").
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170008 is a reply to message #170006] |
Tue, 05 October 2010 15:14 |
Captain Paralytic
Messages: 204
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 5 Oct, 16:06, Derek Turner <frde...@cesmail.net> wrote:
> On Tue, 05 Oct 2010 10:55:44 -0400, sheldonlg wrote:
>> The user can then cut and paste it into a file or you can put in code
>> for the user to download that piece.
>
>> Another approach is to output an Excel file and the user can then output
>> from that Excel file to a .csv file.
>
> Why not? Because it's not very elegant, is it?
Well before anyone else wastes time coming up with other answers, you
had better tell us your elegance criteria.
Personally I think that Shelly's solution is very compact and with the
suggested download code would fit what you want, except that his
solution does not require the intermediate local file and readfile().
|
|
|
|
| Re: Send .csv file to browser [message #170009 is a reply to message #170003] |
Tue, 05 October 2010 15:14 |
C. (http://symcbean.b
Messages: 1
Registered: October 2010
Karma: 0
|
Junior Member |
|
|
On 5 Oct, 15:19, Derek Turner <frde...@cesmail.net> wrote:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
>
> Having googled around I think the solution will be:
>
> 1. Create and open a local file for writing
>
> 2. Write the data to the local file and close it
>
> 3. Use appropriate header files and then readfile()
>
> 4. Delete the local file
>
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
>
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
>
> Other questions depend on your answers to those two, so I'll keep them to
> myself for the moment.
>
> <off topic>
>
> I've exported from TB to see what its .csv output file looks like.
>
> 1. Do I really need to include all those fields?
>
> 2. Will, for example, OE import them properly?
>
> </OT>
>
> tia
>
> Derek
Why create a temporary file? Then you've all the problems about
finding somewhere writeable and deleting it at the right time. I'd go
with just sending it directly the browser.
Although if its a one-off, then it might be easier to just run a query
in phpmyadmin then exporting it as CSV rather than writing a whole new
script.
C.
|
|
|
|
| Re: Send .csv file to browser [message #170010 is a reply to message #170003] |
Tue, 05 October 2010 15:20 |
Michael
Messages: 9
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On 10/5/2010 9:19 AM, Derek Turner wrote:
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
Not necessary to use a local file. You can just stream it straight to
the browser with the appropriate headers. Something like this will
cause the browser to prompt for saving the CSV file. Others may have
refining suggestions, but this has worked for me.
session_cache_limiter('public');
header("Content-type: text/csv");
header("Content-description: File Transfer");
header("Content-disposition: attachment; filename=\"SOMEFILENAME.csv\"");
// Maybe some other caching headers...
header("Pragma: public");
header("Cache-control: max-age=0");
header("Expires: 0");
// Then just echo out your data
foreach ($data as $d)
{
echo implode("," $d) . "\n";
}
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
They don't have to be the first line of the script, but they *do* have
to be the first line of output. That includes making sure there's no
blank space or line break before the first opening <?php
--
Michael
|
|
|
|
| Re: Send .csv file to browser [message #170011 is a reply to message #170003] |
Tue, 05 October 2010 15:25 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 5 Oct 2010 14:19:04 GMT, Derek Turner wrote:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
>
> Having googled around I think the solution will be:
>
> 1. Create and open a local file for writing
>
> 2. Write the data to the local file and close it
>
> 3. Use appropriate header files and then readfile()
>
> 4. Delete the local file
Too much work. It'll be easier and simpler to skip the local file part,
and just send the appropriate HTTP headers, then the file contents
directly.
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
Just send it. This isn't some kind of file that you have to modify the
beginning of it in accordence with what you put into it later, so a
single pass straight out the chute is better.
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
Headers must be sent before you send *anything* else. Even that trailing
extra line ending in one of your inlcuded support files.
> Other questions depend on your answers to those two, so I'll keep them to
> myself for the moment.
>
> <off topic>
>
> I've exported from TB to see what its .csv output file looks like.
>
> 1. Do I really need to include all those fields?
Probably. CSV is a very positionally-oriented "protocol". If it's
looking for phone number in the 37th column, then it's going to load
what's in column 37 into a phone number. You can't generally mess around
with it. But what do you care how many fields you're sending. You're
writing a program to do it, so you have only to enter it once.
--
"Friendship is born at that moment when one person says to another,
'What! You too? I thought I was the only one!'"
--C.S. Lewis
|
|
|
|
| Re: Send .csv file to browser [message #170012 is a reply to message #170003] |
Tue, 05 October 2010 15:44 |
PlastiqueMAN
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On 2010-10-05 16:19:04 +0200, Derek Turner said:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
>
> Having googled around I think the solution will be:
>
> 1. Create and open a local file for writing
You should avoid using temp files for this. Unless the file never
changes, then just upload the file and use readfile() or what ever.
>
> 2. Write the data to the local file and close it
>
> 3. Use appropriate header files and then readfile()
>
> 4. Delete the local file
>
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
>
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
>
> Other questions depend on your answers to those two, so I'll keep them to
> myself for the moment.
>
> <off topic>
>
> I've exported from TB to see what its .csv output file looks like.
>
> 1. Do I really need to include all those fields?
If TB sais so, then you probably need to include it. I'd check out the
vcard rfc if I where you.
http://www.ietf.org/rfc/rfc2426.txt
>
> 2. Will, for example, OE import them properly?
probably not...
>
> </OT>
>
> tia
>
> Derek
I'd do something like:
// not tested
$query = "SELECT whatever FROM table WHERE whatever = something;";
$pdo = new PDO( /* connection stuff*/ );
$stm = $pdo->query($query, PDO::FETCH_ASSOC);
header('Content-Type: text/csv; charset=UTF-8'); // replace UTF-8 with
whatever charset you use
header('Content-Disposition: attachment; filename=mycsvfile.csv');
foreach ($stm->fetchAll() as $csv_line)
{
echo implode(',' $csv_line) . "\r\n";
}
|
|
|
|
| Re: Send .csv file to browser [message #170013 is a reply to message #170003] |
Tue, 05 October 2010 15:55 |
PlastiqueMAN
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On 2010-10-05 16:19:04 +0200, Derek Turner said:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
>
> Having googled around I think the solution will be:
>
> 1. Create and open a local file for writing
>
> 2. Write the data to the local file and close it
>
> 3. Use appropriate header files and then readfile()
>
> 4. Delete the local file
>
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
>
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
PHP usually sends headers just before any content is sent to the browser.
if you:
echo "some text";
header('Content-type: text/plain');
you'll get a warning, and the header is never sent.
if you wrapp your code in an output buffer, you can place the headers anywhere.
ob_start();
echo "some text";
header('Content-type: text/plain');
ob_end_flush();
>
> Other questions depend on your answers to those two, so I'll keep them to
> myself for the moment.
>
> <off topic>
>
> I've exported from TB to see what its .csv output file looks like.
>
> 1. Do I really need to include all those fields?
>
> 2. Will, for example, OE import them properly?
>
> </OT>
>
> tia
>
> Derek
|
|
|
|
| Re: Send .csv file to browser [message #170014 is a reply to message #170005] |
Tue, 05 October 2010 16:05 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 10:55 AM, sheldonlg wrote:
> On 10/5/2010 10:20 AM, Derek Turner wrote:
>> On Tue, 05 Oct 2010 14:19:04 +0000, Derek Turner wrote:
>>
>>
>>>
>>> 3. Use appropriate header files and then readfile()
>>
>> Sorry lines/statements
>
> Why not simply
>
> $theData = getDataFromTableAsArrayOfNonAssociativeArrays();
> foreach ($theData as $rec) {
> $line = implode(',', $rec);
> print $line . '<br>';
> }
>
> The user can then cut and paste it into a file or you can put in code
> for the user to download that piece.
>
> Another approach is to output an Excel file and the user can then output
> from that Excel file to a .csv file.
>
>
Right idea, but some problems. First of all, what happens if a field
contains a comma i.e. "Smith, Jr.". Text fields also need to be
surrounded by double quotes, and any double quotes in the text field
must be doubled to be a valid CSV file. Also, some places use a
Not hard - just a matter of looking at each field, and if it is numeric,
insert it in the stream. If it is text, replace all occurrences of '"'
with '""' and insert it with surrounding quotes. Of course, use commas
to separate the fields.
Then just output directly with the appropriate headers.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170017 is a reply to message #170014] |
Tue, 05 October 2010 16:52 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Tue, 05 Oct 2010 12:05:34 -0400, Jerry Stuckle wrote:
> Right idea, but some problems. First of all, what happens if a field
> contains a comma i.e. "Smith, Jr.".
Never thought of that, it's not something we would even consider in
Britain, nor 2nd or 3rd.
> Text fields also need to be
> surrounded by double quotes, and any double quotes in the text field
> must be doubled to be a valid CSV file.
When TB exports a csv it doesn't quote text fields so presumably it
doesn't need them quoted to import them:
<export>
First Name,Last Name,Display Name,Nickname,Primary Email,Secondary
Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile
Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home
Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work
Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth
Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes,
,,Tim(at)Wynsors(dot)com,,newsletter(at)mail(dot)wynsors(dot)com,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,247Spares,,no-reply(at)247spares(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,Novatech,,ewmail(at)novatech(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,newsletter(at)maplin(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
</export>
> Also, some places use a
Use a what?
>
> Then just output directly with the appropriate headers.
Yes, I like the idea of an output buffer downthread.
|
|
|
|
| Re: Send .csv file to browser [message #170018 is a reply to message #170008] |
Tue, 05 October 2010 17:54 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 11:14 AM, Captain Paralytic wrote:
> On 5 Oct, 16:06, Derek Turner<frde...@cesmail.net> wrote:
>> On Tue, 05 Oct 2010 10:55:44 -0400, sheldonlg wrote:
>>> The user can then cut and paste it into a file or you can put in code
>>> for the user to download that piece.
>>
>>> Another approach is to output an Excel file and the user can then output
>>> from that Excel file to a .csv file.
>>
>> Why not? Because it's not very elegant, is it?
>
> Well before anyone else wastes time coming up with other answers, you
> had better tell us your elegance criteria.
>
> Personally I think that Shelly's solution is very compact and with the
> suggested download code would fit what you want, except that his
> solution does not require the intermediate local file and readfile().
Thanks Captain. Simple to me equals elegant.
BTW, I thought of even simplifying it (after the suitable db login stuff):
$sql = "The actual sequel call";
$rsc = mysql_query($sql);
while ($row = mysql_fetch_array($rsc)) {
print implode(',', $row) . '<br>';
}
No need to even create an array of rows.
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170019 is a reply to message #170014] |
Tue, 05 October 2010 18:02 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 12:05 PM, Jerry Stuckle wrote:
> On 10/5/2010 10:55 AM, sheldonlg wrote:
>> On 10/5/2010 10:20 AM, Derek Turner wrote:
>>> On Tue, 05 Oct 2010 14:19:04 +0000, Derek Turner wrote:
>>>
>>>
>>>>
>>>> 3. Use appropriate header files and then readfile()
>>>
>>> Sorry lines/statements
>>
>> Why not simply
>>
>> $theData = getDataFromTableAsArrayOfNonAssociativeArrays();
>> foreach ($theData as $rec) {
>> $line = implode(',', $rec);
>> print $line . '<br>';
>> }
>>
>> The user can then cut and paste it into a file or you can put in code
>> for the user to download that piece.
>>
>> Another approach is to output an Excel file and the user can then output
>> from that Excel file to a .csv file.
>>
>>
>
> Right idea, but some problems. First of all, what happens if a field
> contains a comma i.e. "Smith, Jr.". Text fields also need to be
> surrounded by double quotes, and any double quotes in the text field
> must be doubled to be a valid CSV file. Also, some places use a
>
> Not hard - just a matter of looking at each field, and if it is numeric,
> insert it in the stream. If it is text, replace all occurrences of '"'
> with '""' and insert it with surrounding quotes. Of course, use commas
> to separate the fields.
>
> Then just output directly with the appropriate headers.
>
Thanks Jerry. I didn't do any testing or deep thinking or even see it
it would work. (If I had to do this and test I would have hit those
problems and come up with those solutions.) I just was presenting a
simple approach. The OP was way over-complicating the problem.
BTW, the other thing to be careful about is what if the field is NULL?
You then want to be sure that you get two successive commas (nothing in
this one).
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170021 is a reply to message #170017] |
Tue, 05 October 2010 18:06 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 12:52 PM, Derek Turner wrote:
> On Tue, 05 Oct 2010 12:05:34 -0400, Jerry Stuckle wrote:
>
>> Right idea, but some problems. First of all, what happens if a field
>> contains a comma i.e. "Smith, Jr.".
>
> Never thought of that, it's not something we would even consider in
> Britain, nor 2nd or 3rd.
>
>
>> Text fields also need to be
>> surrounded by double quotes, and any double quotes in the text field
>> must be doubled to be a valid CSV file.
>
> When TB exports a csv it doesn't quote text fields so presumably it
> doesn't need them quoted to import them:
What if I have in there that my nickname is Shelly ("sheldonlg")? How
does the db have the " in the table? How is that retrieved? How does
that appear when written to the browser? What if single quotes are used
as in O'Reilly? That is what Jerry is talking about. You always have
to be concerned with things like this.
>
> <export>
> First Name,Last Name,Display Name,Nickname,Primary Email,Secondary
> Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile
> Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home
> Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work
> Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth
> Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes,
> ,,Tim(at)Wynsors(dot)com,,newsletter(at)mail(dot)wynsors(dot)com,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> ,,247Spares,,no-reply(at)247spares(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> ,,Novatech,,ewmail(at)novatech(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> ,,,,newsletter(at)maplin(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> </export>
>
>> Also, some places use a
>
> Use a what?
>
>>
>> Then just output directly with the appropriate headers.
>
> Yes, I like the idea of an output buffer downthread.
>
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170024 is a reply to message #170017] |
Wed, 06 October 2010 00:18 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 12:52 PM, Derek Turner wrote:
> On Tue, 05 Oct 2010 12:05:34 -0400, Jerry Stuckle wrote:
>
>> Right idea, but some problems. First of all, what happens if a field
>> contains a comma i.e. "Smith, Jr.".
>
> Never thought of that, it's not something we would even consider in
> Britain, nor 2nd or 3rd.
>
>
>> Text fields also need to be
>> surrounded by double quotes, and any double quotes in the text field
>> must be doubled to be a valid CSV file.
>
> When TB exports a csv it doesn't quote text fields so presumably it
> doesn't need them quoted to import them:
>
The use of quotes is an option in some products, as long as you don't
have a comma in the text. But it's better to be safe and use them for
all strings. And maybe you don't see it in a name right now, but never?
What happens if an American comes over then and tries to sign up? Or
how about an address, i.e. "123 Main St., Suite 3" or a list of hobbies
(football, cricket, chess) or any of a number of different things.
Unless you specifically check for commas when the data is entered and
forbid their use, it can happen.
> <export>
> First Name,Last Name,Display Name,Nickname,Primary Email,Secondary
> Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile
> Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home
> Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work
> Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth
> Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes,
> ,,Tim(at)Wynsors(dot)com,,newsletter(at)mail(dot)wynsors(dot)com,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> ,,247Spares,,no-reply(at)247spares(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> ,,Novatech,,ewmail(at)novatech(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> ,,,,newsletter(at)maplin(dot)co(dot)uk,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
> </export>
>
>> Also, some places use a
>
> Use a what?
>
>>
>> Then just output directly with the appropriate headers.
>
> Yes, I like the idea of an output buffer downthread.
>
A hanging clause I didn't entirely delete :)
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170025 is a reply to message #170017] |
Wed, 06 October 2010 00:21 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 12:52 PM, Derek Turner wrote:
>
> Yes, I like the idea of an output buffer downthread.
>
Unnecessary overhead. If you design your page correctly, your headers
will be sent followed by your data.
Most cases where I've seen output buffering used it's because someone
didn't properly plan the page. Only very occasionally is it needed, and
then generally when working with other products which generate their own
output.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170026 is a reply to message #170021] |
Wed, 06 October 2010 03:50 |
Adam Harvey
Messages: 25
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On Tue, 05 Oct 2010 14:06:45 -0400, sheldonlg wrote:
> On 10/5/2010 12:52 PM, Derek Turner wrote:
>> When TB exports a csv it doesn't quote text fields so presumably it
>> doesn't need them quoted to import them:
>
> What if I have in there that my nickname is Shelly ("sheldonlg")? How
> does the db have the " in the table? How is that retrieved? How does
> that appear when written to the browser? What if single quotes are used
> as in O'Reilly? That is what Jerry is talking about. You always have
> to be concerned with things like this.
Indeed, which is why I'd go with the built-in fputcsv() function which
should do sensible things with quoting; ie something like this:
// fputcsv() can only write to a file/stream handle, so let's open one up.
$fp = fopen('php://temp', 'r+');
// Loop over the data using whatever the appropriate database function is.
// We'll assume $rs is a MySQL result resource, but it doesn't really
// matter where it comes from.
while ($row = mysql_fetch_array($rs)) {
fputcsv($fp, $rec);
}
// Rewind the stream handle and actually output the CSV data.
// No temporary file required!
header('Content-Type: text/csv');
rewind($fp);
fpassthru($fp);
fclose($fp);
Adam
|
|
|
|
| Re: Send .csv file to browser [message #170030 is a reply to message #170014] |
Wed, 06 October 2010 09:01 |
Captain Paralytic
Messages: 204
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 5 Oct, 17:05, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> On 10/5/2010 10:55 AM, sheldonlg wrote:
>
>
>
>
>
>> On 10/5/2010 10:20 AM, Derek Turner wrote:
>>> On Tue, 05 Oct 2010 14:19:04 +0000, Derek Turner wrote:
>
>>>> 3. Use appropriate header files and then readfile()
>
>>> Sorry lines/statements
>
>> Why not simply
>
>> $theData = getDataFromTableAsArrayOfNonAssociativeArrays();
>> foreach ($theData as $rec) {
>> $line = implode(',', $rec);
>> print $line . '<br>';
>> }
>
>> The user can then cut and paste it into a file or you can put in code
>> for the user to download that piece.
>
>> Another approach is to output an Excel file and the user can then output
>> from that Excel file to a .csv file.
>
> Right idea, but some problems. First of all, what happens if a field
> contains a comma i.e. "Smith, Jr.". Text fields also need to be
> surrounded by double quotes, and any double quotes in the text field
> must be doubled to be a valid CSV file. Also, some places use a
>
> Not hard - just a matter of looking at each field, and if it is numeric,
> insert it in the stream. If it is text, replace all occurrences of '"'
> with '""' and insert it with surrounding quotes. Of course, use commas
> to separate the fields.
>
> Then just output directly with the appropriate headers.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================
Whenever I can, I substitute tab delimited for comma delimited as it
tends to bypass most of these problems (particularly around Excel
which is not capable of correctly handling csv - or even tab delimited
- data properly)
|
|
|
|
| Re: Send .csv file to browser [message #170031 is a reply to message #170026] |
Wed, 06 October 2010 11:19 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 11:50 PM, Adam Harvey wrote:
> On Tue, 05 Oct 2010 14:06:45 -0400, sheldonlg wrote:
>> On 10/5/2010 12:52 PM, Derek Turner wrote:
>>> When TB exports a csv it doesn't quote text fields so presumably it
>>> doesn't need them quoted to import them:
>>
>> What if I have in there that my nickname is Shelly ("sheldonlg")? How
>> does the db have the " in the table? How is that retrieved? How does
>> that appear when written to the browser? What if single quotes are used
>> as in O'Reilly? That is what Jerry is talking about. You always have
>> to be concerned with things like this.
>
> Indeed, which is why I'd go with the built-in fputcsv() function which
> should do sensible things with quoting; ie something like this:
>
>
> // fputcsv() can only write to a file/stream handle, so let's open one up.
> $fp = fopen('php://temp', 'r+');
>
> // Loop over the data using whatever the appropriate database function is.
> // We'll assume $rs is a MySQL result resource, but it doesn't really
> // matter where it comes from.
> while ($row = mysql_fetch_array($rs)) {
> fputcsv($fp, $rec);
> }
>
> // Rewind the stream handle and actually output the CSV data.
> // No temporary file required!
> header('Content-Type: text/csv');
> rewind($fp);
> fpassthru($fp);
> fclose($fp);
>
>
> Adam
I wouldn't create a temp file for it. The code to output it directly to
the browser. It's not that hard.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170034 is a reply to message #170003] |
Wed, 06 October 2010 14:42 |
alvaro.NOSPAMTHANX
Messages: 277
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
El 05/10/2010 16:19, Derek Turner escribió/wrote:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
>
> Having googled around I think the solution will be:
>
> 1. Create and open a local file for writing
>
> 2. Write the data to the local file and close it
>
> 3. Use appropriate header files and then readfile()
>
> 4. Delete the local file
>
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
The main benefits of generating a temp file are:
1. You don't have to hold the complete file in memory
2. You can reuse the output
However, readfile() loads the whole file into memory and you delete the
file after sending it.
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
Headers always come first in almost everything (HTTP, e-mail, etc.).
With certain settings, PHP is able to cache regular output until you
send the last header but it normally makes coding a bit more complicate
and, in your case, you probably don't want to echo the complete file
before the download even starts.
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
|
|
|
|
| Re: Send .csv file to browser [message #170036 is a reply to message #170034] |
Wed, 06 October 2010 15:26 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
Álvaro G. Vicario wrote:
> El 05/10/2010 16:19, Derek Turner escribió/wrote:
>> I want to use MySQL to get a list of email addresses and names and then
>> output a .csv file to the browser so that it can be saved and then
>> imported into Thunderbird etc.
>>
>> Having googled around I think the solution will be:
>>
>> 1. Create and open a local file for writing
>>
>> 2. Write the data to the local file and close it
>>
>> 3. Use appropriate header files and then readfile()
>>
>> 4. Delete the local file
>>
>> Questions:
>>
>> 1. Is this a reasonable approach? Or should I be outputting to the
>> browser on the fly? Or using PEAR or something like that?
>
> The main benefits of generating a temp file are:
>
> 1. You don't have to hold the complete file in memory
That's not mandatory anyway, if you are e.g. prepping a db for a dump
You can simply output headers, and then stream the data out..as you read
and prep itt..
> 2. You can reuse the output
If that's relevant, yes.
>
>
>
|
|
|
|
| Re: Send .csv file to browser [message #170038 is a reply to message #170007] |
Wed, 06 October 2010 16:25 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Tue, 05 Oct 2010 11:11:24 -0400, sheldonlg wrote:
> On 10/5/2010 11:06 AM, Derek Turner wrote:
>> On Tue, 05 Oct 2010 10:55:44 -0400, sheldonlg wrote:
>>
>>> The user can then cut and paste it into a file or you can put in code
>>> for the user to download that piece.
>>>
>>> Another approach is to output an Excel file and the user can then output
>>> from that Excel file to a .csv file.
>>
>> Why not? Because it's not very elegant, is it?
>
> WGAS? Why does something have to be "elegent"? All it has to do is:
>
> 1 - Work reliably.
> 2 - Be maintainable.
>
> Besides, haven't you ever heard of K.I.S.S. or Occam's razor?
>
> (WGAS means "who gives a shit").
'cause the method fails miserably at 1.
--------------------------
$ cat foo.php
<?php
$recs = array();
$recs[] = "This is the first line.";
$recs[] = "Line the second, number 2.";
$recs[] = "Line 3, continuing.";
print_r($recs);
$line = implode(',', $recs);
print_r($line);
?>
$ php -f foo.php
Array
(
[0] => This is the first line.
[1] => Line the second, number 2.
[2] => Line 3, continuing.
)
This is the first line.,Line the second, number 2.,Line 3, continuing.$
--------------------------
How many variable columns are there in that output. Three? Five? None?
--
100. Finally, to keep my subjects permanently locked in a mindless
trance, I will provide each of them with free unlimited Internet
access.
--Peter Anspach's list of things to do as an Evil Overlord
|
|
|
|
| Re: Send .csv file to browser [message #170039 is a reply to message #170038] |
Wed, 06 October 2010 16:28 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/6/2010 12:25 PM, Peter H. Coffin wrote:
> On Tue, 05 Oct 2010 11:11:24 -0400, sheldonlg wrote:
>> On 10/5/2010 11:06 AM, Derek Turner wrote:
>>> On Tue, 05 Oct 2010 10:55:44 -0400, sheldonlg wrote:
>>>
>>>> The user can then cut and paste it into a file or you can put in code
>>>> for the user to download that piece.
>>>>
>>>> Another approach is to output an Excel file and the user can then output
>>>> from that Excel file to a .csv file.
>>>
>>> Why not? Because it's not very elegant, is it?
>>
>> WGAS? Why does something have to be "elegent"? All it has to do is:
>>
>> 1 - Work reliably.
>> 2 - Be maintainable.
>>
>> Besides, haven't you ever heard of K.I.S.S. or Occam's razor?
>>
>> (WGAS means "who gives a shit").
>
> 'cause the method fails miserably at 1.
>
> --------------------------
> $ cat foo.php
> <?php
> $recs = array();
> $recs[] = "This is the first line.";
> $recs[] = "Line the second, number 2.";
> $recs[] = "Line 3, continuing.";
> print_r($recs);
> $line = implode(',', $recs);
> print_r($line);
> ?>
> $ php -f foo.php
> Array
> (
> [0] => This is the first line.
> [1] => Line the second, number 2.
> [2] => Line 3, continuing.
> )
> This is the first line.,Line the second, number 2.,Line 3, continuing.$
> --------------------------
>
> How many variable columns are there in that output. Three? Five? None?
>
Which has the same problem as Sheldon's - parsing the .csv will give:
This is the first line.
Line the second
number 2
Line 3
continuing.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170040 is a reply to message #170003] |
Wed, 06 October 2010 16:36 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/5/2010 10:19 AM, Derek Turner wrote:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
>
> Having googled around I think the solution will be:
>
> 1. Create and open a local file for writing
>
> 2. Write the data to the local file and close it
>
> 3. Use appropriate header files and then readfile()
>
> 4. Delete the local file
>
> Questions:
>
> 1. Is this a reasonable approach? Or should I be outputting to the
> browser on the fly? Or using PEAR or something like that?
>
> 2. Can the headers appear appear anywhere in the script or must they be
> be the first lines?
>
> Other questions depend on your answers to those two, so I'll keep them to
> myself for the moment.
>
> <off topic>
>
> I've exported from TB to see what its .csv output file looks like.
>
> 1. Do I really need to include all those fields?
>
> 2. Will, for example, OE import them properly?
>
> </OT>
>
> tia
>
> Derek
OK, since no one else posted it and I have some time:
$sql = 'query here'';
result = mysql_query($sql);
if ($result) {
$outfile = fopen('php://output', 'w');
header('Content-Type: text/csv');
header('Content-disposition: attachment; filename=data.csv');
while ($row = mysql_fetch_array($rsc)) {
fputcsv($outfile, $row);
}
fclose($outfile);
}
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170041 is a reply to message #170040] |
Wed, 06 October 2010 17:52 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/6/2010 12:36 PM, Jerry Stuckle wrote:
> On 10/5/2010 10:19 AM, Derek Turner wrote:
>> I want to use MySQL to get a list of email addresses and names and then
>> output a .csv file to the browser so that it can be saved and then
>> imported into Thunderbird etc.
>>
>> Having googled around I think the solution will be:
>>
>> 1. Create and open a local file for writing
>>
>> 2. Write the data to the local file and close it
>>
>> 3. Use appropriate header files and then readfile()
>>
>> 4. Delete the local file
>>
>> Questions:
>>
>> 1. Is this a reasonable approach? Or should I be outputting to the
>> browser on the fly? Or using PEAR or something like that?
>>
>> 2. Can the headers appear appear anywhere in the script or must they be
>> be the first lines?
>>
>> Other questions depend on your answers to those two, so I'll keep them to
>> myself for the moment.
>>
>> <off topic>
>>
>> I've exported from TB to see what its .csv output file looks like.
>>
>> 1. Do I really need to include all those fields?
>>
>> 2. Will, for example, OE import them properly?
>>
>> </OT>
>>
>> tia
>>
>> Derek
>
> OK, since no one else posted it and I have some time:
>
> $sql = 'query here'';
> result = mysql_query($sql);
> if ($result) {
> $outfile = fopen('php://output', 'w');
> header('Content-Type: text/csv');
> header('Content-disposition: attachment; filename=data.csv');
> while ($row = mysql_fetch_array($rsc)) {
> fputcsv($outfile, $row);
> }
> fclose($outfile);
> }
>
Two errors that immediately pop up.
Your second line is missing the dollar sign.
Your mysql_fetch_array uses $rsc, not $result.
Also, I posted something similar where I used $rsc.
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170042 is a reply to message #170041] |
Wed, 06 October 2010 18:06 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/6/2010 1:52 PM, sheldonlg wrote:
> On 10/6/2010 12:36 PM, Jerry Stuckle wrote:
>> On 10/5/2010 10:19 AM, Derek Turner wrote:
>>> I want to use MySQL to get a list of email addresses and names and then
>>> output a .csv file to the browser so that it can be saved and then
>>> imported into Thunderbird etc.
>>>
>>> Having googled around I think the solution will be:
>>>
>>> 1. Create and open a local file for writing
>>>
>>> 2. Write the data to the local file and close it
>>>
>>> 3. Use appropriate header files and then readfile()
>>>
>>> 4. Delete the local file
>>>
>>> Questions:
>>>
>>> 1. Is this a reasonable approach? Or should I be outputting to the
>>> browser on the fly? Or using PEAR or something like that?
>>>
>>> 2. Can the headers appear appear anywhere in the script or must they be
>>> be the first lines?
>>>
>>> Other questions depend on your answers to those two, so I'll keep
>>> them to
>>> myself for the moment.
>>>
>>> <off topic>
>>>
>>> I've exported from TB to see what its .csv output file looks like.
>>>
>>> 1. Do I really need to include all those fields?
>>>
>>> 2. Will, for example, OE import them properly?
>>>
>>> </OT>
>>>
>>> tia
>>>
>>> Derek
>>
>> OK, since no one else posted it and I have some time:
>>
>> $sql = 'query here'';
>> result = mysql_query($sql);
>> if ($result) {
>> $outfile = fopen('php://output', 'w');
>> header('Content-Type: text/csv');
>> header('Content-disposition: attachment; filename=data.csv');
>> while ($row = mysql_fetch_array($rsc)) Sor
>> fputcsv($outfile, $row);
>> }
>> fclose($outfile);
>> }
>>
>
> Two errors that immediately pop up.
>
> Your second line is missing the dollar sign.
> Your mysql_fetch_array uses $rsc, not $result.
>
> Also, I posted something similar where I used $rsc.
>
Sorry - just a quick update - didn't have time to check it. And yes, I
did copy a couple of your lines of MySQL code.
But the key here is opening php://output and writing directly to it with
fputcsv().
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170043 is a reply to message #170031] |
Thu, 07 October 2010 02:42 |
Adam Harvey
Messages: 25
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On Wed, 06 Oct 2010 07:19:10 -0400, Jerry Stuckle wrote:
> I wouldn't create a temp file for it. The code to output it directly to
> the browser. It's not that hard.
php://temp only creates a real temporary file if the file size goes
beyond a certain threshold -- by default, 2 megs. Otherwise it behaves
exactly the same way as php://memory, that is, as an in-memory file-like
stream.
Adam
|
|
|
|
| Re: Send .csv file to browser [message #170044 is a reply to message #170039] |
Thu, 07 October 2010 02:55 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Wed, 06 Oct 2010 12:28:30 -0400, Jerry Stuckle wrote:
> On 10/6/2010 12:25 PM, Peter H. Coffin wrote:
>> On Tue, 05 Oct 2010 11:11:24 -0400, sheldonlg wrote:
>>>
>>> WGAS? Why does something have to be "elegent"? All it has to do is:
>>>
>>> 1 - Work reliably.
>>> 2 - Be maintainable.
>>>
>>> Besides, haven't you ever heard of K.I.S.S. or Occam's razor?
>>>
>>> (WGAS means "who gives a shit").
>>
>> 'cause the method fails miserably at 1.
>>
>> --------------------------
>> $ cat foo.php
>> <?php
>> $recs = array();
>> $recs[] = "This is the first line.";
>> $recs[] = "Line the second, number 2.";
>> $recs[] = "Line 3, continuing.";
>> print_r($recs);
>> $line = implode(',', $recs);
>> print_r($line);
>> ?>
>> $ php -f foo.php
>> Array
>> (
>> [0] => This is the first line.
>> [1] => Line the second, number 2.
>> [2] => Line 3, continuing.
>> )
>> This is the first line.,Line the second, number 2.,Line 3, continuing.$
>> --------------------------
>>
>> How many variable columns are there in that output. Three? Five? None?
>>
> Which has the same problem as Sheldon's - parsing the .csv will give:
>
> This is the first line.
> Line the second
> number 2
> Line 3
> continuing.
Exactly. implode()ing with commas makes suck-tastic CSV. fputcsv() is
not perfect, but it's a hell of a lot better, and (combining with clever
file handles like $fh = fopen("php://output", 'w'); ) doesn't take all
that much more code to implement.
--
72. If all the heroes are standing together around a strange device and
begin to taunt me, I will pull out a conventional weapon instead of
using my unstoppable superweapon on them.
--Peter Anspach's list of things to do as an Evil Overlord
|
|
|
|
| Re: Send .csv file to browser [message #170045 is a reply to message #170043] |
Thu, 07 October 2010 03:13 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/6/2010 10:42 PM, Adam Harvey wrote:
> On Wed, 06 Oct 2010 07:19:10 -0400, Jerry Stuckle wrote:
>> I wouldn't create a temp file for it. The code to output it directly to
>> the browser. It's not that hard.
>
> php://temp only creates a real temporary file if the file size goes
> beyond a certain threshold -- by default, 2 megs. Otherwise it behaves
> exactly the same way as php://memory, that is, as an in-memory file-like
> stream.
>
> Adam
Which still doesn't solve the problem of unnecessarily creating a temp file.
It's not needed at all and a waste of system resources.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Send .csv file to browser [message #170046 is a reply to message #170040] |
Thu, 07 October 2010 10:25 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Wed, 06 Oct 2010 12:36:04 -0400, Jerry Stuckle wrote:
>
> OK, since no one else posted it and I have some time:
>
<snip very useful material>
Many thanks, Jerry, I'll give it a go and report back.
|
|
|
|
| Re: Send .csv file to browser [message #170047 is a reply to message #170046] |
Thu, 07 October 2010 12:08 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Thu, 07 Oct 2010 10:25:50 +0000, Derek Turner wrote:
> <snip very useful material>
>
> Many thanks, Jerry, I'll give it a go and report back.
<?php
include("/home/cantabi1/admin_connect.php");
$sql = "SELECT `givenName`, `familyName`, `email`, `mobile`, `landLine`
FROM `member` WHERE `active` = 1";
$result = mysql_query($sql, $conn) or die (mysql_error());
if ($result) {
$outfile = fopen('php://output', 'w');
header('Content-Type: text/csv');
header('Content-disposition: attachment; filename=data.csv');
while ($row = mysql_fetch_array($result)) {
fputcsv($outfile, $row);
}
fclose($outfile);
}
?>
Produces a csv file just fine BUT each variable is doubled up i.e.
Derek,Derek,Turner,Turner, etc. can anyone see why?
|
|
|
|
| Re: Send .csv file to browser [message #170048 is a reply to message #170047] |
Thu, 07 October 2010 12:19 |
alvaro.NOSPAMTHANX
Messages: 277
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
El 07/10/2010 14:08, Derek Turner escribió/wrote:
> On Thu, 07 Oct 2010 10:25:50 +0000, Derek Turner wrote:
>
>> <snip very useful material>
>>
>> Many thanks, Jerry, I'll give it a go and report back.
>
> <?php
> include("/home/cantabi1/admin_connect.php");
> $sql = "SELECT `givenName`, `familyName`, `email`, `mobile`, `landLine`
> FROM `member` WHERE `active` = 1";
> $result = mysql_query($sql, $conn) or die (mysql_error());
> if ($result) {
> $outfile = fopen('php://output', 'w');
> header('Content-Type: text/csv');
> header('Content-disposition: attachment; filename=data.csv');
> while ($row = mysql_fetch_array($result)) {
> fputcsv($outfile, $row);
> }
> fclose($outfile);
> }
> ?>
>
> Produces a csv file just fine BUT each variable is doubled up i.e.
> Derek,Derek,Turner,Turner, etc. can anyone see why?
Try this and you'll see why:
while ($row = mysql_fetch_array($result)) {
print_r($result);
}
You can just use mysql_fetch_row().
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
|
|
|
|
| Re: Send .csv file to browser [message #170049 is a reply to message #170048] |
Thu, 07 October 2010 12:41 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/7/2010 8:19 AM, "Álvaro G. Vicario" wrote:
> El 07/10/2010 14:08, Derek Turner escribió/wrote:
>> On Thu, 07 Oct 2010 10:25:50 +0000, Derek Turner wrote:
>>
>>> <snip very useful material>
>>>
>>> Many thanks, Jerry, I'll give it a go and report back.
>>
>> <?php
>> include("/home/cantabi1/admin_connect.php");
>> $sql = "SELECT `givenName`, `familyName`, `email`, `mobile`, `landLine`
>> FROM `member` WHERE `active` = 1";
>> $result = mysql_query($sql, $conn) or die (mysql_error());
>> if ($result) {
>> $outfile = fopen('php://output', 'w');
>> header('Content-Type: text/csv');
>> header('Content-disposition: attachment; filename=data.csv');
>> while ($row = mysql_fetch_array($result)) {
>> fputcsv($outfile, $row);
>> }
>> fclose($outfile);
>> }
>> ?>
>>
>> Produces a csv file just fine BUT each variable is doubled up i.e.
>> Derek,Derek,Turner,Turner, etc. can anyone see why?
>
> Try this and you'll see why:
>
> while ($row = mysql_fetch_array($result)) {
> print_r($result);
> }
>
> You can just use mysql_fetch_row().
>
>
Good point! From the manual (www.php.net):
array mysql_fetch_array ( resource $result [, int $result_type =
MYSQL_BOTH ] )
so the default is for both.
This would work if you added MYSQL_NUM as the optional second argument
-- or as you correctly stated just use mysql_fetch_row.
--
Shelly
|
|
|
|
| Re: Send .csv file to browser [message #170050 is a reply to message #170047] |
Thu, 07 October 2010 12:49 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/7/2010 8:08 AM, Derek Turner wrote:
> On Thu, 07 Oct 2010 10:25:50 +0000, Derek Turner wrote:
>
>> <snip very useful material>
>>
>> Many thanks, Jerry, I'll give it a go and report back.
>
> <?php
> include("/home/cantabi1/admin_connect.php");
> $sql = "SELECT `givenName`, `familyName`, `email`, `mobile`, `landLine`
> FROM `member` WHERE `active` = 1";
> $result = mysql_query($sql, $conn) or die (mysql_error());
> if ($result) {
> $outfile = fopen('php://output', 'w');
> header('Content-Type: text/csv');
> header('Content-disposition: attachment; filename=data.csv');
> while ($row = mysql_fetch_array($result)) {
> fputcsv($outfile, $row);
> }
> fclose($outfile);
> }
> ?>
>
> Produces a csv file just fine BUT each variable is doubled up i.e.
> Derek,Derek,Turner,Turner, etc. can anyone see why?
>
Sorry, as I said - it was a quick post because I'm very busy right now.
But I hope you get the point.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Send .csv file to browser - Thanks [message #170086 is a reply to message #170003] |
Sat, 09 October 2010 14:30 |
Derek Turner
Messages: 48
Registered: October 2010
Karma: 0
|
Member |
|
|
On Tue, 05 Oct 2010 14:19:04 +0000, Derek Turner wrote:
> I want to use MySQL to get a list of email addresses and names and then
> output a .csv file to the browser so that it can be saved and then
> imported into Thunderbird etc.
May I say a huge 'thank-you' to everyone who contributed to this thread
and helped me to get this working just fine. I love this group!
|
|
|
|
| Re: Send .csv file to browser - Thanks [message #170087 is a reply to message #170086] |
Sat, 09 October 2010 15:11 |
sheldonlg
Messages: 166
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/9/2010 10:30 AM, Derek Turner wrote:
> On Tue, 05 Oct 2010 14:19:04 +0000, Derek Turner wrote:
>
>> I want to use MySQL to get a list of email addresses and names and then
>> output a .csv file to the browser so that it can be saved and then
>> imported into Thunderbird etc.
>
> May I say a huge 'thank-you' to everyone who contributed to this thread
> and helped me to get this working just fine. I love this group!
You're welcome. I wish more people who received help here would have
the courtesy to post a thank you after their problem has been solved.
--
Shelly
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]