Export MYSQL to CSV leaving empty fields

Question

I am trying to export a mysql query to csv. I have it working, the only thing is it's not putting the empty fields in there. I them separate by pipes, |. I need to be able to download the query and upload it somewhere else, so I need it to export the empty fields along with it. Here is my current export code:

<?php


$from = $_REQUEST['from'];
$to = $_REQUEST['to'];
$filename = "APGE_ELEC_" . $to;
header('Content-Disposition: attachment; filename="'.$filename.'".csv"');

    $hostname = ""; //SET SERVER/HOSTNAME
    $dbusername = ""; //SET DATABASE USERNAME
    $dbname = ""; //SET DATABASE NAME
    $dbpassword = ""; //SET DATABASE USERNAME

$dbhandle = mysql_connect($hostname, $dbusername, $dbpassword) 
  or die("Unable to connect to MySQL");

$selected = mysql_select_db($dbname,$dbhandle) 
  or die("Could not select Data Base");


$query = "SELECT * FROM v88374 WHERE date >= DATE_FORMAT('" . $from . "', '%Y%m%d') AND date <=  DATE_FORMAT('" . $to . "', '%Y%m%d')";


$export = mysql_query ($query ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) ."|" . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = $value . '|' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n"; 

}

print "$header\n$data";


exit();


?>

I hope its not a huge fix. I appreciate any help. Thank you.

It'd be much easier to use fputcsv, which will allow you to specify a delimiter and then write the csv to a file, which will be properly formatted. Also, mysql_* functions are deprecated, and really should move to PDO or mysqli. — aynber
– aynber, Commented Aug 2, 2013 at 13:52

fancyPants · Accepted Answer · 2013-08-02 14:19:25Z

3

Why not simply do

SELECT whatever
FROM your_table
INTO OUTFILE '/directory/file.csv'
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

?

And from the manual:

... if the MySQL client software is installed on the remote machine, you can instead use a client command such as mysql -e "SELECT ..." > file_name to generate the file on the client host

answered Aug 2, 2013 at 14:19

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aynber · Accepted Answer · 2013-08-02 14:42:57Z

1

To use fputcsv, try this:

$fp = fopen('your_file_name.csv','w');
fputcsv($fp,$fields,'|'); // add in your header row
while( $row = mysql_fetch_row( $export ) )
{
    if($row != NULL)
        fputcsv($fp,$row,'|'); // Each row is already an array, so you just need to export it here
}
fclose($fp);

edited Aug 2, 2013 at 14:42

answered Aug 2, 2013 at 14:00

aynber

23.2k9 gold badges57 silver badges69 bronze badges

5 Comments

dkeeper09 Over a year ago

I'm getting Warning: fputcsv() expects parameter 2 to be array. null given. What do I do with the $header exactly?

aynber Over a year ago

I changed header to fields since that's where you're putting the fields directly from the query. Interesting that $row is null from your while loop. I'm going to tweak that line as well.

dkeeper09 Over a year ago

Getting close I think. Sorry for all the questions. if I change it to your changes above, it now downloads but just keeps downloading..I had to stop it at 50MB. Is that weird?

aynber Over a year ago

Depends on the amount of your data. Check the file so far to see if it's okay, and then check to see how many rows your query returns.

aynber Over a year ago

I removed the null on the while, and added a check below. That should take care of the null error and large data.

Elpy · Accepted Answer · 2013-08-02 13:57:27Z

0

I think the mistake is in here:

    if ( ( !isset( $value ) ) || ( $value == "" ) )
    {
        $value = "\t";
    }
    else
    {
        $value = str_replace( '"' , '""' , $value );
        $value = $value . '|' . "\t";
    }
    $line .= $value;

When $value is empty you make it a tab and add it without a pipe, the stuff in the else statement is not executed.

Also you have a major security issue with your query, so unless it is for personal use only and some sanitation to the query.

$from = mysql_real_escape_string($_REQUEST['from']);
$to = mysql_real_escape_string($_REQUEST['to']);

answered Aug 2, 2013 at 13:57

Elpy

2721 silver badge4 bronze badges

Collectives™ on Stack Overflow

Export MYSQL to CSV leaving empty fields

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related