$output lists only one record

[pabirds]

I have the following code in a php file for exporting data to a csv file.

<?php require_once('../connections/PaSiteGuide.php'); ?>

<?php

// output headers so that the file is downloaded rather than displayed

header('Content-Type: text/csv; charset=utf-8');

header('Content-Disposition: attachment; filename=BOP.csv');

// create a file pointer connected to the output stream

$output = fopen('php://output', 'w');

// output the column headings

fputcsv($output, array('ID','Common Name', 'Scientific Name', 'Breeds', 'Abundance', 'Occurrence', 'Seasonal Status','AOU_Order','AOU Supplement'));

// fetch the data

mysqli_select_db($conn, $database_PaSiteGuide);

$query_rsrows = sprintf('SELECT birdsofpa.ID, birdsofpa.CommonName, birdsofpa.SciName, birdsofpa.Breeding, birdsofpa.Abundance, birdsofpa.Occurrence, birdsofpa.SeasonalStatus,

aou_list.AOU_Order, aou_list.Updated FROM birdsofpa, aou_list WHERE birdsofpa.ID =  aou_list.SPNO ORDER BY aou_list.AOU_Order ASC');

$rows = mysqli_query($conn, $query_rsrows) or die(mysqli_connect_error());

$row_rsrows = mysqli_fetch_assoc($rows);

$totalRows_rsrows = mysqli_num_rows($rows);

// loop over the rows, outputting them

while ($rows = mysqli_fetch_assoc($rows)) fputcsv($output, $rows);

?>

<?php

mysqli_free_result($rows);

?>

It outputs the file, but only the column titles and one row of data ( and the row it puts out is not the first row based on the ORDER BY specified).

So how do I make it do all of the rows and in the correct order?

[Nancy OShea]

See if this helps.

<?php

$con = mysqli_connect("localhost","your_username","your_password") or die ("could not connect to mysql");

mysqli_select_db($con, "your_database") or die ("no database found");

$result = mysqli_query($con,"SELECT * FROM your_tablename ORDER BY your_fieldname");

$row = array($result);

$fp = fopen('your_csv_file.csv', 'w');

while($row = $result->fetch_assoc()) {

    //Fetch data as an associate array so we can reference field by name

    fwrite($fp, implode(',', $row) . "\r\n");

}

fclose($fp);

mysqli_close($con);

?>

[pabirds]

I tried your suggested code -- see below, but it tried to save it with the name of the php file rather than the csv filename that I entered (BOP).

When I tried to open the downloaded file with a spreadsheet, it was blank.

<?php require_once('../connections/PaSiteGuide.php'); ?>

<?php

mysqli_select_db($conn, $database_PaSiteGuide)or die ("no database found");

$result = mysqli_query($conn,"SELECT birdsofpa.ID, birdsofpa.CommonName, birdsofpa.SciName, birdsofpa.Breeding, birdsofpa.Abundance, birdsofpa.Occurrence, birdsofpa.SeasonalStatus,

aou_list.AOU_Order, aou_list.Updated FROM birdsofpa, aou_list WHERE birdsofpa.ID =  aou_list.SPNO ORDER BY aou_list.AOU_Order ASC");

$row = array($result);

$fp = fopen('BOP.csv', 'w');

while($row = $result->fetch_assoc()) {

    //Fetch data as an associate array so we can reference field by name

    fwrite($fp, implode(',', $row) . "\r\n");

}

fclose($fp);

?>

[Nancy OShea]

I'm using the exact same code I posted and it works great for me on Linux server.

The PHP script is in a PHP file.   Results are posted to an existing CSV file when the PHP script is run.

My SQL query is less complicated than yours.  That might be where things are breaking down.  But that's only a guess.

Nancy

[pabirds]

I don't want it to write to an existing file.

I want it to create a new file BOP.csv.

Is that the problem?

Frank

[Nancy OShea]

Right.  You need a file to be opened before you can write or put data to it.

$fp = fopen('your_csv_file.csv', 'w');

[pabirds]

I already have that line in the code.

$fp = fopen('BOP.csv', 'w');

Frank

[Nancy OShea]

The CSV has to exist and be writable on the server.

https://www.virendrachandak.com/techtalk/creating-csv-file-using-php-and-mysql/

[pabirds]

My original code using $output and fputcsv created and downloaded the data as a csv file to the user's computer, it just didn't include all of the records. It didn't need an existing file on the server.

The code you suggested seems to be doing something else, putting data into an existing file on the server.

The only problem with my original code was the number of records (and order).

Can you suggest what might be wrong with my original code as far as the number of records exported is concerned (why it isn't looping)?

Frank

[pabirds]

Okay. I created a file named BOP.csv and placed it in the root folder of the server and another copy in the same folder as the php file (I wasn't sure where to put it).

When I run the PHP file I still get a popup asking whether to open or save "BOPDowwload.php" (the name of the file running the procedure), rather than BOP.csv.

[B i r n o u]

which php script did you run ?

[pabirds]

I used the following:

<?php require_once('../connections/PaSiteGuide.php'); ?>

<?php

mysqli_select_db($conn, $database_PaSiteGuide)or die ("no database found");

$result = mysqli_query($conn,"SELECT birdsofpa.ID, birdsofpa.CommonName, birdsofpa.SciName, birdsofpa.Breeding, birdsofpa.Abundance, birdsofpa.Occurrence, birdsofpa.SeasonalStatus, aou_list.AOU_Order, aou_list.Updated FROM birdsofpa, aou_list WHERE birdsofpa.ID =  aou_list.SPNO ORDER BY aou_list.AOU_Order ASC");

$row = array($result);

$fp = fopen('BOP.csv', 'w');

while($row = $result->fetch_assoc()) {

    //Fetch data as an associate array so we can reference field by name

    fwrite($fp, implode(',', $row) . "\r\n");

}

fclose($fp);

?>

[B i r n o u]

well in this script I don't see any feature that will add the hearders, then in the csv format you use the comma as a separated value... and I don't see any control to add lines... you should try the script that I send you... if you need any explanation or working files... please don't hesitate, I will redrive it there...

[pabirds]

I was using the code suggested by Nancy OShea on this thread.

Are you saying that code is incorrect?

[B i r n o u]

I never said that... you should read my previous message, the one from Birnou Sébarte     4 déc. 2017 08:51

[pabirds]

Can you (or can you recommend someone -- i'll pay) write this page for me? I thought exporting a sql query to a csv file would be a lot simpler. My code-writing skills are not up to the task.

[B i r n o u]

you wont have to pay for anything... I al ready gave you the code... so as it seams that it is complex for you to extract it from the message, don't worry, if you don't mind I will post it tomorrow for you to pick everything from a blank example.

right now I'm back from a waterpolo training... it's later, and I will do that tomorrow, if is this ok for you ?

[pabirds]

Yes. Thank you.

Frank

[BenPleysier]

As an alternative, you could try https://www.dmxzone.com/go/32575/dmxzone-data-exporter .

[osgood_ · Accepted solution]

This should give you what you need, it's just a tweak of your original code.......

<?php

// connect to database - change details to those of your own

$conn = new mysqli('servername' , 'username' , 'password' , 'database_name');

// output headers so that the file is downloaded rather than displayed

header('Content-Type: text/csv; charset=utf-8');

header('Content-Disposition: attachment; filename=BOP.csv');

// create a file pointer connected to the output stream

$output = fopen('php://output', 'w');

// output the column headings

fputcsv($output, array('ID','Common Name', 'Scientific Name', 'Breeds', 'Abundance', 'Occurrence', 'Seasonal Status','AOU_Order','AOU Supplement'));

// get data to display on page

$list_data = $conn->query("SELECT birdsofpa.ID, birdsofpa.CommonName, birdsofpa.SciName, birdsofpa.Breeding, birdsofpa.Abundance, birdsofpa.Occurrence, birdsofpa.SeasonalStatus,

aou_list.AOU_Order, aou_list.Updated FROM birdsofpa, aou_list WHERE birdsofpa.ID =  aou_list.SPNO ORDER BY aou_list.AOU_Order ASC") or die($conn->error);

$num_rows = $list_data->num_rows;

// loop over the rows, outputting them

while ($row = $list_data->fetch_assoc()) fputcsv($output, $row);

?>

[pabirds]

Thanks!

That worked.

Frank

[pabirds]

That worked. Thanks.

Is there a way to force the Save/Open dialogue box to open?

Right now, it automatically saves the file to the user's default download folder.

Frank

[B i r n o u]

did you try adding , just the basic description, at least

AddType text/csv .csv

to the .htaccess file

that should bring the dialog box to choose the appropriate application to open it... and in the same time the save one...

[pabirds]

What is the exact text that I should put in the htaccess file?

All of the entries I see begin with rewriterule or rewritecond...

Frank

[B i r n o u]

create lines either before # BEGIN WordPress or after # END WordPress

and paste

AddType text/csv .csv

that's it... if that doesn't change anything you can then rmove the added line or add a # just in front (# is a commented line indicator in the apache directives world... Directive Index - Apache HTTP Server Version 2.2