Export MySQL data to .csv using PHP
15,394
Solution 1
Try this :
$rows = mysql_query("SELECT `name`, `gender` FROM TABLE");
while ($row = mysql_fetch_assoc($rows)) {
if($row['gender'] == 1) {
$row['gender'] = 'Male';
} else {
$row['gender'] = 'Female';
}
// Or ternary condition
// $row['gender'] = ($row['gender'] == 1 ? 'Male' : 'Female');
fputcsv($output, $row);
}
Solution 2
Use the IF statement
in your SQL to minimize PHP logic:
SELECT `name`, IF(gender=1,'Male','Female') as 'gender' FROM TABLE
Solution 3
<?php
$db_record = 'yourRecod';
// optional where query
$where = 'WHERE 1 ORDER BY 1';
// filename for export
$csv_fileName = 'db_export_'.$db_record.'_'.date('Y-m-d').'.csv';
// database variables
$hostname = "localhost";
$user = "yourUserName";
$password = "yourPassword";
$database = "yourDataBase";
// Database connecten voor alle services
mysql_connect($hostname, $user, $password)
or die('Could not connect: ' . mysql_error());
mysql_select_db($database)
or die ('Could not select database ' . mysql_error());
$csv_export = '';
$query = mysql_query("SELECT * FROM ".$db_record." ".$where);
$field = mysql_num_fields($query);
// create line with field names
for($i = 0; $i < $field; $i++) {
$csv_export.= mysql_field_name($query,$i).';';
}
$csv_export.= '';
while($row = mysql_fetch_array($query)) {
// create line with field values
for($i = 0; $i < $field; $i++) {
$csv_export.= '"'.$row[mysql_field_name($query,$i)].'";';
}
$csv_export.= '';
}
// Export the data and prompt a csv file for download
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=".$csv_fileName."");
echo($csv_export);
?>
i give you full sample code which i am using to Export MySql data to .CSV using PHP
Author by
Arif
Updated on June 27, 2022Comments
-
Arif almost 2 years
I'm exporting data to a
.csv
file and it's working perfectly but I have one small issue. I fetchname
andgender
from a table but for gender I saveid
in my database (i.e.,1 = Male
,2 = Female
). My below code gives me id for gender, how can I fix it? Return 1 for Male and 2 for Female:$rows = mysql_query("SELECT `name`, `gender` FROM TABLE"); while ($row = mysql_fetch_assoc($rows)) { fputcsv($output, $row); }
-
David about 10 yearsTernary condition seems nicely to me @ncrocfer ;)
-
Bidstrup over 8 yearsThis dosnt give me linebreaks for each new row :-(