johram pong johram pong - 11 months ago 108
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 Source

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.