Strip quotes from fputcsv results.

[Nancy OShea]

For now, I'm querying a MySQL database with phpMyAdmin and exporting results to a comma delimited CSV file.

It works fine but I need to automate this if I can.

The php script I created uses fputcsv but by default it adds double quotes around text strings containing spaces. 

And the service I'm working with doesn't allow quotes in the CSV file.

Results with fputcsv:

1234,"prod name with spaces",29.95,/path/filename.zip,1gb,4320

Desired result:

1234,prod name with spaces,29.95,/path/filename.zip,1gb,4320

<?php

mysqli_select_db($con, "myproducts") or die ("no database found");  

$result = mysqli_query($con,"SELECT prodcode, prodname, price, dload_url, size, expiry_time FROM downloads WHERE dload_url LIKE '%value%' ORDER BY prodcode DESC");

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

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

      while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){

           fputcsv($fp, $row);

        }

fclose($fp);

?>

Any ideas?

Nancy O.

[Rob Hecker2]

Nancy, I have not used this function before (I use PHPExcel to create spreadsheets from queries), but I do see that the double quotes are an option (enclosure), so you may be able to set the option to nothing

fputcsv(file,fields,seperator,enclosure)

[Nancy OShea]

Good suggestion, Rob.

I tried it but enclosure must be a single token like a space, quote, pipe or something.

     fputcsv($fp, $row,',','');

Nancy O.

[Rob Hecker2]

Then would it be possible to run preg_replace or str_replace after the function has run? You could set the enclosure to something unique and strip it out afterwards.

[David_Powers · Accepted solution]

A much simpler solution is to use fwrite() instead of fputcsv():

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

      while($row = mysqli_fetch_array($result, MYSQLI_NUM)){

           fwrite($fp, implode(',', $row) . "\r\n");

        }

fclose($fp);

The reason fputcsv() insists on an enclosure is to prevent problems if the text contains the same character as the delimiter. If you're certain that the value of prodname will never contain commas, this solution will work. But if a product name ever contains commas, you're screwed.

[Nancy OShea]

Perfect!

The reason fputcsv() insists on an enclosure is to prevent problems if the text contains the same character as the delimiter.

Right. And I can see where that would be useful in some cases.

Our dbase contains no commas. So this works.

Thanks a million, David.

[Nancy OShea]

Then would it be possible to run preg_replace or str_replace after the function has run?

Sadly, that didn't work either.  Fputcsv is very stubborn.

But thanks for your input.  It's much appreciated.

Nancy O.