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

thanks Mark: reading about it now. can you give me a head start to it, concerning what i have? — karto
– karto, Commented Apr 6, 2011 at 8:04

Shakti Singh · Accepted Answer · 2011-04-06 07:47:37Z

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.

edited Apr 6, 2011 at 7:47

answered Apr 6, 2011 at 7:42

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

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

2 Comments

karto Over a year ago

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

Jaffa Over a year ago

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

Gordon · Accepted Answer · 2011-04-06 07:45:32Z

1

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;

answered Apr 6, 2011 at 7:45

Gordon

318k76 gold badges547 silver badges566 bronze badges

1 Comment

karto Over a year ago

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

Jaffa · Accepted Answer · 2011-04-06 07:42:55Z

0

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

answered Apr 6, 2011 at 7:42

Jaffa

12.7k5 gold badges54 silver badges103 bronze badges

5 Comments

Jaffa Over a year ago

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

karto Over a year ago

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

Jaffa Over a year ago

@karto 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

karto Over a year ago

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

Jaffa Over a year ago

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.

krifur · Accepted Answer · 2011-04-06 07:51:43Z

0

you can try something like this, playing with ASCII caracters

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

answered Apr 6, 2011 at 7:51

krifur

9004 gold badges16 silver badges37 bronze badges

Collectives™ on Stack Overflow

Creating a CSV file from MySQL table using php

4 Answers 4

2 Comments

1 Comment

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

5 Comments

Comments

Linked

Related