Sazal Ahmed Sazal Ahmed - 3 months ago 8
MySQL Question

Getting error in exported csv file format using php

I have used below code to save data in csv format from my page. After saving i have got some extra line in my csv file . How i will remove it i need refer for this.

$connection=mysql_connect($host, $uname, $pass);

echo mysql_error();

//or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or
die("Database could not be selected");
$result=mysql_select_db($database)
or die("database cannot be selected <br>");

// Fetch Record from Database

$output = "";
$table = "tbl_record"; // Enter Your Table Name
$sql = mysql_query("select * from $table");
$columns_total = mysql_num_fields($sql);

// Get The Field Name

for ($i = 0; $i < $columns_total; $i++) {
$heading = mysql_field_name($sql, $i);
$output .= '"'.$heading.'",';
}
$output .="\n";

// Get Records from the table

while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="\n";
}

// Download the file

$filename = "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo $output;
exit;

?>


here is the below screenshot for output value

output csv file

the red mark are unwanted line.

Answer

its a warning that mysql_* is deprecated, dont use it. Use PDO, a quick fix will be

//Put on top
error_reporting(E_ERROR | E_PARSE);

this will suppress the warnings, here is a good getting starting read of PDO

Update

To get the Fields Name in CSV using mysqli you will need to change this code

Change this to

// Get The Field Name
for ($i = 0; $i < $columns_total; $i++) {
      $heading = mysql_field_name($sql, $i);
      $output .= '"'.$heading.'",';
}
$output .="\n"; 

This

while ($finfo = mysqli_fetch_field($result)) {
    $heading = $finfo->name;
    $output .= '"'.$heading.'",';
}

Enabling mysqli_*

Check your php.ini for extension=mysqli.so or similar. it may be commented out using a # just uncomment it.

Enabling PDO

On a windows server you can add the following lines in your php.ini

extension=php_pdo.dll
extension=php_pdo_mysql.dll

On a Linux server you can compile php with the following option --with-pdo-mysql In your php.ini, add the following lines

extension=pdo.so
extension=pdo_mysql.so