johram pong johram pong - 1 month ago 7
MySQL Question

downloading db records in csv but getting blank rows

Guys this my code downloads records from mysql as csv but wheresoever in rows there is any value with a comma , it does not download and i get a blank row instead

what could be this solution for this

here is my code

require_once('config.php');
$u =$_REQUEST['u'];
$cs =$_REQUEST['cs'];
$y =$_REQUEST['y'];
$d =$_REQUEST['d'];
$m =$_REQUEST['m'];
$date = "$y-$m-$d";
$todays = date("d-m-Y");
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=Mortgage-'.$u.'-Records-'.$date.'.csv');
//select table to export the data
$select_table=mysql_query("SELECT * FROM records WHERE user ='".$u."' AND status ='".$cs."' AND DATE_FORMAT(posted, '%Y-%m-%d') = '$date' ORDER BY id DESC");
$rows = mysql_fetch_assoc($select_table);
if ($rows)
{
getcsv(array_keys($rows));
}
while($rows)
{
getcsv($rows);
$rows = mysql_fetch_assoc($select_table);
}

// get total number of fields present in the database
function getcsv($no_of_field_names)
{
$separate = '';


// do the action for all field names as field name
foreach ($no_of_field_names as $field_name)
{
if (preg_match('/\\r|\\n|,|"/', $field_name))
{
$field_name = '' . str_replace('', $field_name) . '';
}
echo $separate . $field_name;

//sepearte with the comma
$separate = ',';
}

//make new row and line
echo "\r\n";
}


really appreciate your time and help

Answer

Change your first part of the code like this:

require_once('config.php');
$u =$_REQUEST['u'];
$cs =$_REQUEST['cs'];
$y =$_REQUEST['y'];
$d =$_REQUEST['d'];
$m =$_REQUEST['m'];
$date = "$y-$m-$d";
$todays = date("d-m-Y");
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=Mortgage-'.$u.'-Records-'.$date.'.csv');
//select table to export the data
$select_table=mysql_query("SELECT * FROM records WHERE  user ='".$u."' AND status ='".$cs."' AND DATE_FORMAT(posted, '%Y-%m-%d') = '$date'  ORDER BY id DESC");

$i = 0;
while($rows = mysql_fetch_assoc($select_table);)
{
    if($i === 0) {
        getcsv(array_keys($rows));
    }

    getcsv($rows);
    $i++;
}

And do this change in function getcsv - replace this code:

$field_name = '' . str_replace('', $field_name) . '';

with this:

$field_name = '"' . $field_name . '"';

But instead of using mysql_query try to implement PDO because mysql lib is prune to mysql injection and will is deprecated in new php versions.