$num_column = mysql_num_fields($view);
$csv_header = '';
for($i = 0; $i < $num_column; $i++) {
$csv_header .= '"' . mysql_field_name($view,$i) . '";';
}
$csv_header .= "
";
$csv_row = '';
while($row = mysql_fetch_row($view)) {
for($i = 0; $i < $num_column; $i++) {
$csv_row .= '"' . $row[$i] . '";';
}
$csv_row .= "
";
}
I am currently creating a CSV file from a stored procedure on PHPMyadmin. Here's a code snippet from where I am creating the export file.
I want to have decimal separators as a comma e.g (1,45) and thousands separators as a decimal point e.g (14.124).
I am aware of the number_format function but I am unsure as to how to apply it to ALL numbers within my generated CSV export.
I'm going to assume that you want to do this in PHP rather than in a stored proc.
First things first: do not use mysql_* functions. They are deprecated in PHP 5, and removed completely in PHP 7 - and for good reason. Look into mysqli or PDO. If you're not familiar with OOP in PHP, it might seem a little confusing, but it's really not too different from the mysql_* functions.
With that aside, I would suggest running each value through a function when you output it. You can either have that function format anything that looks like a number, or you use a white list to format specific columns that you know are numbers.
Here is an example all-inclusive function - you can trim it down to what you need. It will allow you to pass the value to be formatted, as well as an optional argument for the column name, and another optional argument for an array of columns that should be formatted as a number.
function format_number_output ($val, $col=false, $whitelist=array())
{
// First, check if the column name was passed
if ($col !== false && count($whitelist) > 0)
{
// Is this one of the columns that should be formatted?
if (in_array($col, $whitelist) && is_numeric($val))
{return number_format($val, 2, ',', '.');}
// If not, then just return the original value
else
{return $val;}
}
// If the column name was not passed, then format anything that looks like a number
else if (is_numeric($val))
{return number_format($val, 2, ',', '.');}
// Otherwise, just return the original value
else
{return $val;}
}
And you could test it out with different options:
// Returns "1234.56":
print format_number_output("1234.56", "actually_text", array("numeric_field", "another_numeric"));
// Returns "1.234,56":
print format_number_output("1234.56", "numeric_field", array("numeric_field", "another_numeric"));
// Returns "text":
print format_number_output("text", "numeric_field", array("numeric_field", "another_numeric"));
// Returns "1.234,56":
print format_number_output("1234.56");
// Returns "text":
print format_number_output("text");
In addition, I would strongly suggest you take the advice of GrumpyCrouton and use fputcsv. The CSV format has so many stupid little gotchas that you don't want to deal with.
You could modify your code to have it work like this (I've left your mysql_fetch_array call - but again, learn to use PDO/mysqli ASAP before your project gets large and refactoring becomes difficult):
$fp = fopen("output.csv", "w");
while($row = mysql_fetch_row($view)) {
$cur_row = array();
for($i = 0; $i < $num_column; $i++) {
$cur_row[] = format_number_output($row[$i]);
}
fputcsv($fp, $cur_row);
}
fclose($fp)
And if you wanted to go with the white-list approach, you could use mysql_fetch_array (or rather - the PDO/mysqli equivalent) to get the column names:
$fp = fopen("output.csv", "w");
while($row = mysql_fetch_array($view)) {
$cur_row = array();
foreach ($row as $col=>$val) {
$cur_row[] = format_number_output($val, $col, array("numeric_field", "another_numeric_field"));
}
fputcsv($fp, $cur_row);
}
fclose($fp)