Highlighted

$output lists only one record

New Here ,
Dec 03, 2017

Copy link to clipboard

Copied

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?

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);

?>

Views

883

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

$output lists only one record

New Here ,
Dec 03, 2017

Copy link to clipboard

Copied

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?

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);

?>

Views

884

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 03, 2017 0
Adobe Community Professional ,
Dec 03, 2017

Copy link to clipboard

Copied

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);

?>

Nancy O'Shea, ACP
Alt-Web Design & Publishing

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
New Here ,
Dec 03, 2017

Copy link to clipboard

Copied

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);

?>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
Adobe Community Professional ,
Dec 03, 2017

Copy link to clipboard

Copied

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

Nancy O'Shea, ACP
Alt-Web Design & Publishing

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
New Here ,
Dec 03, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
Adobe Community Professional ,
Dec 03, 2017

Copy link to clipboard

Copied

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

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

Nancy O'Shea, ACP
Alt-Web Design & Publishing

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
New Here ,
Dec 03, 2017

Copy link to clipboard

Copied

I already have that line in the code.

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

Frank

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
Adobe Community Professional ,
Dec 03, 2017

Copy link to clipboard

Copied

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

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

Nancy O'Shea, ACP
Alt-Web Design & Publishing

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
New Here ,
Dec 03, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2017 0
New Here ,
Dec 04, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
Adobe Community Professional ,
Dec 04, 2017

Copy link to clipboard

Copied

which php script did you run ?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
New Here ,
Dec 04, 2017

Copy link to clipboard

Copied

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);

?>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
Adobe Community Professional ,
Dec 04, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
New Here ,
Dec 04, 2017

Copy link to clipboard

Copied

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

Are you saying that code is incorrect?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
Adobe Community Professional ,
Dec 04, 2017

Copy link to clipboard

Copied

I never said that... you should read my previous message, the one from

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
New Here ,
Dec 04, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
Adobe Community Professional ,
Dec 04, 2017

Copy link to clipboard

Copied

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 ?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
New Here ,
Dec 04, 2017

Copy link to clipboard

Copied

Yes. Thank you.

Frank

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
Adobe Community Professional ,
Dec 04, 2017

Copy link to clipboard

Copied

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


Ben

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
LEGEND ,
Dec 05, 2017

Copy link to clipboard

Copied

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);

?>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 05, 2017 0
New Here ,
Dec 05, 2017

Copy link to clipboard

Copied

Thanks!

That worked.

Frank

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 05, 2017 0
New Here ,
Dec 05, 2017

Copy link to clipboard

Copied

I just got a fix from another community member. He made a slight change to my code and now it works.

So, I won't be needing your coding.

Thanks

Frank

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 05, 2017 0
Adobe Community Professional ,
Dec 05, 2017

Copy link to clipboard

Copied

no problem it's up to you, the files was on line...

have a nice day

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 05, 2017 0
New Here ,
Dec 05, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 05, 2017 0
Adobe Community Professional ,
Dec 05, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 05, 2017 0
New Here ,
Dec 06, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 06, 2017 0
Adobe Community Professional ,
Dec 06, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 06, 2017 0
New Here ,
Dec 06, 2017

Copy link to clipboard

Copied

Here is my htaccess file.

The export procedure that is the subject of this thread is being used in a Joomla application, not wordpress.

So what do I add to this file?

Frank

Options +FollowSymLinks

#Alternate default index page

DirectoryIndex index.php

RewriteEngine On

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 06, 2017 0
Adobe Community Professional ,
Dec 04, 2017

Copy link to clipboard

Copied

well if your goal is to export a brand new create CSV file and integrating Headers ROW/COLS in the CSV , I wont be able to help you the way you do it... (I don't say that it is not possible that way, But pêrsonnaly I don't use it that way

first I create array that handles the different headers needed depending on the export template

say

$headers = array( "headertype1" => array(), "headertype2" => array());

on each array I have two definitions,

1 - the fields to be extracted, and 2 the Label of those fields

those definitions are string semi colon separation

i.e

"headertype1" => array("db_fields" => 'field1;field2;field3', "db_headers" => 'label 1; label 2; label 3');

well to write the CSV

first connect your database

include_once('/'.trim( $_SERVER['DOCUMENT_ROOT'], '/' ).'/shared/php/__connect.php');

I often a shared space for deploying the PHP

the trim is to allow the document root to be extracted as well from a xampp localhost as from linux host

then before creating the CSV file I set the appropiate headers/cols

first I set the RS choice corresponding to the header type

and the liste of datas to be choose in (if needed... not necessary... it acts like a subfilter)

$RS = 'headertype1'; // or whatever choice

$liste = 'x,y,z';

$fields = explode(';',$headers[$RS]['db_fields']);

$entete = $headers[$RS]['db_headers'];

then I apply the SQL queries

$sql ="SELECT * FROM `tab_something` WHERE `ch_tab_id` IN (".$liste.") ORDER BY `ch_tab_id` ASC"; // this a foo exemple

then I execute the request

$items = $dbh->prepare($sql);

$items->execute();

$CSV = '';

the loop is made to fill up the CSV as a string

I choose the star * for beeing a line separator... you can also use the å which is also a caracter that is not use in general... except if you're physician... or danish...

while($item = $items->fetch(PDO::FETCH_OBJ)){

        $CSVtemp  = '';

        foreach ($fields as $field) {

            if ($CSVtemp != '') {

                $CSVtemp .= ';';

            }

            $CSVtemp .= $item->{$field};

           

        }

        if ($CSV != '') {

            $CSV .= "*";

        }

        $CSV .= $CSVtemp;

    }

  

the you add the headers

$CSV = $entete . '*' . $CSV;

if you need the BOM Sign... add the following...

$CSV="\xEF\xBB\xBF".$CSV;

name you CSV file

    $filename = 'export_'.date('Y-m-d__h-i-s').'.csv';

create the path and open the file

    $fichier = '../../EXPORTS/'.$filename;

    $fp = fopen($fichier, 'w');

create all the declarations depending on the specific char used earlier * or å...

    $declarations = explode('*',$CSV);

now write all your lines in a loop

    foreach ($declarations as $declaration) {

        $record = explode(';',$declaration);

        fputcsv($fp, $record, ";",'"');

    }

   

fclose($fp);

echo $filename;

that should do the job... at least it works like a charm since I use it...

now the fact of applying the array on top... let you play with as many template as you will need for your client needs

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0
Most Valuable Participant ,
Dec 04, 2017

Copy link to clipboard

Copied

Linux is case sensitive

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 04, 2017 0