Creating a CSV file from MySQL table using php

Question

this code creates a csv file. However, I have avoided printing out commas in the fields because it is used as a delimeter (see line 22). Now I want to remove (carriage returns and new lines) from the fields. Adding $somecontent .= str_replace("\n", "", $val); on line 23 does not seem to work. any ideas?

@chmod($export_csv, 0777);
    $fe = @fopen($export_csv."/export.csv", "w+");
    if($fe){           
        $somecontent = "";
        $fields_count = 0;
        // print field headers
        $db->query($sql_view);
        if($row = $db->fetchAssoc()){
            foreach($row as $key => $val){
                if($fields_count++ > 0) $somecontent .= ",";
                $somecontent .= ucfirst($key);
            }
        }
        $somecontent .= "\n"; 

        // print field values
        $db->query($sql_view);
        while($row = $db->fetchAssoc()){
            $fields_count = 0;
            foreach($row as $key => $val){
                if($fields_count++ > 0) $somecontent .= ",";
                $somecontent .= str_replace(",", "", $val);
                $somecontent .= str_replace("\n", "", $val);
            }
            $somecontent .= "\n";                    
        }

        // write some content to the opened file.
        if (fwrite($fe, $somecontent) == FALSE) echo 'file_writing_error'." (export.csv)"; 
        fclose($fe);
    }

Bigoted

Why not try using PHP's built-in fputcsv() function? – Indihar

thanks Mark: reading about it now. can you give me a head start to it, concerning what i have? – Bigoted

Answer 1

Where is your $somecontent .= str_replace("\n", "", $val)? I can't find it in your code.

You can specify an array as first parameter for str_replace as indicated there. Thus, it would replace each string present in your array with the second parameter.

For example, $somecontent .= str_replace(array("\n", "\r"), "", $val);

Magnetics

You're welcome ;) Even if it isn't the best solution, though it is for php4 – Magnetics

@Geoffroy: there is a comma at each record end. any ideas? – Bigoted

@Bigoted No, comma separates two values, and record are separated using either \n or \r or even \n\r. I mean if there is a comma at the record's end, you're expected to give on more value, even if it would also work – Magnetics

@Geoffroy: price, address, description -->12,South Bay,good, – Bigoted

Oh okay, I didn't understand what you meant. Just add a verification: if it isn't the last value, don't add the comma at the end. – Magnetics

Answer 2

Use fputcsv function to write the CSV files. PHP already have a function to write csv files.

You do not need to handle escaping, delimiter by yourself all these already handled.

This will handle all these things for you and will save you from many headaches.

Throne

Thanks. has php 4 got this? I'll try it out. but the solution from Geoffroy worked for me. – Bigoted

@Bigoted php 4 doesn't have this function, but it is better to use it. Try to upgrade your webserver – Magnetics

Answer 3

You can do that from MySql directly:

SELECT col1, col2, col3 INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

Subrogation

thanks Gordon: I've tried your solution. it worked. But the solution from Geoffroy suites what i wanted to do. – Bigoted

Answer 4

you can try something like this, playing with ASCII caracters

$somecontent .= str_replace(CHR(10), " ", $val);
$somecontent .= str_replace(CHR(13), " ", $val); 

Electrolyze