Export csv from mysql pipe Delimited

[moosey_man1988]

Hi Everyone

 

I'm pretty new to PHP and I have worked out how to do a CSV export from my contacts database which works perfectly.

 

but I need to export a pipe delimited csv, which im really struggling with.

 

This is how I need my csv to look like

 

1234567890|surname|forename|email@somewhere.com|test-contact|1|address|city|state|US|unit|

 

I just need help with the echoing out this file right.

 

I have a while loop that grabs the data like so

$data_string .= 
"\"" . $row['number'] ."\"
 "\"\n";

of course this only does the number but im sure you get the jist.

 

any help given I thank in advance.

 

 

 

 

[Ch0cu3r]

If you want to populate your csv file with the results from your query then use fputcsv. Example (I'm guessing your table column names)

// path to csv file to write data to
$data_file = 'myfile.csv';

$result = $mysqli->query('SELECT number, surname, forename, email, message, id, address, city, state, country, unit FROM table');

// open/create the csv file
$handle = fopen($data_file, 'w');

// loop over results from query
while($row = $result->fetch_row())
{
    // write row contents to csv file
    fputcsv($handle, $row);
}
fclose($handle);

Documentation

http://php.net/fputcsv

 

Alternatively if your mysql user has file privileges, you can have the actual query dump the data into the csv file using a SELECT INTO query

[moosey_man1988]

Excellent, will this also download the CSV, I have a submit button that performs the query and then downloads the CSv using header. will this do the same?

[Ch0cu3r]

No, fputcsv only writes the data to the file. If you want file to automatically download you need to use headers to force the csv file to be download.

[moosey_man1988]

okay, so I have got the fputcsv working, and it is pulling out what i want and i can easily manipulate it now, but the headers give me an excel file with the webpage echo's

When all I need is the CSV file that was created, If I FTP onto the server I can see the csv file with the correct fields

// path to csv file to write data to
$data_file = 'myNewfile.csv';

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename='.$data_file);

$result = $db_mysqli->query('SELECT * FROM newfies');

// open/create the csv file
$handle = fopen($data_file, 'w');

// loop over results from query
while($row = $result->fetch_row())
{
    // write row contents to csv file
    fputcsv($handle, $row, '|');
}
fclose($handle);
}

[Barand]

This methods creates and downloads in a single operation

 

http://forums.phpfreaks.com/topic/296581-how-i-can-i-let-user-to-download-a-data-from-query-in-mysql/?do=findComment&comment=1512967

[moosey_man1988]

Wow, I think im going insane, I've tried that with no luck is doesn't create a csv at all and doesn't download one.

 

here's my code.

$sql = 'SELECT * FROM newfies';

function sql2csv($db_mysqli, $sql, $filename='myNewfile.csv', $headings=0)

{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else 
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $db_mysqli->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row),'|');
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
    }
    
}

}

[Barand]

You don't show your call to the function.

 

Is this code in a file of its own?

[moosey_man1988]

Hi I've for the function to work now but it is still inserting the html code within the php pages

 

below is the end of the script which calls the function

 

include('test.php');

sql2csv($db_mysqli, $sql, $filename='', $headings=0);

}
?>

and the function is now its own page named test.php

[Barand]

File 1

<a href="file2.php">Download newfies</a>

File2.php (the download code goes in a file of its own)

<?php

$sql = 'SELECT * FROM newfies';
sql2csv($db_mysqli, $sql, $filename='myNewfile.csv', $headings=0);


function sql2csv($db_mysqli, $sql, $filename='', $headings=0)

{
if (!$filename)
    $f = 'download_' . date('ymdhi') . '.csv';
else
    $f = $filename;
$fp = fopen('php://output', 'w'); // so you can fputcsv to STDOUT
if ($fp) {
    $res = $db_mysqli->query($sql);
    if ($res) {
      header('Content-Type: text/csv');
      header('Content-Disposition: attachment; filename="'.$f.'"');
      header('Pragma: no-cache');
      header('Expires: 0');
      $row = $res->fetch_assoc();
    if ($headings) {
      fputcsv($fp, array_keys($row),'|');
}
do {
    fputcsv($fp, $row);
} while ($row = $res->fetch_assoc());

}
else echo "Error in query";
fclose($fp);
}

}



?>