PhP export .csv change delimiter from comma to others

Question

I have this PhP code where in the user clicks a button and it automatically downloads a .csv file and inside it are different kinds of information. So this .csv file is comma delimited so it means whenever there is a comma the pointer goes to the next cell. But i'd like to change the delimiter to anything other than comma because some of my data has comma for example Location column has data such as 501A, 501B and it needs to be on a single cell. But instead it goes to another cell so id doesnt fit when I print it in portrait. So i just wanna ask how can I change the delimiter and maintain the data with commas? thanks!

<?php
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "envoy");
function db_connect() {

  $db = mysqli_connect(DB_HOST, DB_USER, DB_PASS) or die("ERROR!");
  mysqli_select_db($db, DB_NAME);

  return $db;
}

$db = db_connect();
date_default_timezone_set("EST5EDT");
$date = date('m-d-Y,h.i.sa');
$date2 = date('m/d/Y');
$filename = $date.'.csv';
//$fp = fopen($filename,"w");

$sql2 = "SELECT sku as Sku,name as 'Brochure Name',location as Location FROM brochureinfo where modified LIKE '$date2' ORDER BY name  ";
$rs2 = mysqli_query($db, $sql2);
$total_records = mysqli_num_rows($rs2);
mysqli_close($db);

if($total_records>0){
$row = mysqli_fetch_assoc($rs2);

$seperator = "";
$comma = "";

foreach ($row as $name => $value){
    $seperator.= $comma. ''.str_replace('','""',$name);
    $comma=",";

}
$seperator .= "\n";

//echo $seperator;




mysqli_data_seek($rs2,0);

while ($row = mysqli_fetch_assoc($rs2)){


//$seperator = "";
$comma = "";

foreach ($row as $name => $value){

            $value = str_replace( array( "\r" , "\n", "\r\n", "\n\r" ) ,'' , $value);
            $value = str_replace('</b><br>','',$value);
            $value = str_replace('<b>','',$value);
            $value = str_replace('<br>','',$value);
            $value = str_replace('<br />','',$value);

    $seperator.= $comma. ''.str_replace('','""',$value);
    $comma=",";

    }
$seperator .= "\n";
}

header('Content-Type: text/csv');
header("Content-Disposition: attachment; filename=$date.csv");
header('Pragma: no-cache');

echo $seperator;
}
else{
    header('Content-Type: text/csv');
header("Content-Disposition: attachment; filename=$date.csv");
header('Pragma: no-cache');
echo "No record modified today";}

Answer 1

You can use fputcsv() for generation data in csv format

int fputcsv ( resource $handle , array $fields [, string $delimiter = "," [, string $enclosure = '"' [, string $escape_char = "\" ]]] )

EDIT:

Your code will be looks somethiing like this:

<?php
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "envoy");
function db_connect() {

  $db = mysqli_connect(DB_HOST, DB_USER, DB_PASS) or die("ERROR!");
  mysqli_select_db($db, DB_NAME);

  return $db;
}

$db = db_connect();
date_default_timezone_set("EST5EDT");
$date = date('m-d-Y,h.i.sa');
$date2 = date('m/d/Y');
$filename = $date.'.csv';
$fp = fopen($filename,"w");

$sql2 = "SELECT sku as Sku,name as 'Brochure Name',location as Location FROM brochureinfo where modified LIKE '$date2' ORDER BY name  ";
$rs2 = mysqli_query($db, $sql2);
$total_records = mysqli_num_rows($rs2);
mysqli_close($db);

if ($total_records>0) {
    $row = mysqli_fetch_assoc($rs2);

    $delimiter = ';';
    $data = [];
    foreach ($row as $name => $value){
        $data[] = $name;
    }
    fputcsv($fp, $data, $delimiter);


    mysqli_data_seek($rs2,0);

    while ($row = mysqli_fetch_assoc($rs2)){
        fputcsv($fp, $row, $delimiter);
    }

    fclose($fp);

    header('Content-Type: text/csv');
    header("Content-Disposition: attachment; filename=$date.csv");
    header('Pragma: no-cache');

    echo $seperator;
}
else {
    fclose($fp);

    header('Content-Type: text/csv');
    header("Content-Disposition: attachment; filename=$date.csv");
    header('Pragma: no-cache');
    echo "No record modified today";
}

Answer 2

Use PHP function, fputcsv to put each line as an array ( where each column value will be an item to the array), so you don't need to worry about commas inside. Since you are directly downloading the file, use PHP output as file handle. Remember to set the headers before output starts.

$output = fopen('php://output', 'w');

// output the column headings/ values
fputcsv($output, array('Column 1', 'Column 2', 'Column 3');