php 在PHP中查询mysql并将数据导出为CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16391528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query mysql and export data as CSV in PHP
提问by aandroidtest
I have myssql db with different tables. The data between the tables are linked and I retrieve and display them by using the userid. I used the reference from PHP MYSQLi Displaying all tables in a database
我有不同表的 mysql 数据库。表之间的数据是链接的，我使用用户 ID 检索和显示它们。我使用了PHP MYSQLi Displaying all tables in a database
But how do I export this information as a csv file? I have tried instead of echo changed it to a string and print the string to a csv file but it is not working.
但是如何将此信息导出为 csv 文件？我尝试过而不是 echo 将其更改为字符串并将该字符串打印到 csv 文件，但它不起作用。
I have also tried the example from: http://sudobash.net/php-export-mysql-query-to-csv-file/
我也尝试过以下示例：http: //sudobash.net/php-export-mysql-query-to-csv-file/
But I can see the data but on top there is also junk (like "<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1' cellspacing='0' cellpadding='1'>"
etc) inside the csv file.
但我可以看到数据，但最重要的是"<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1' cellspacing='0' cellpadding='1'>"
csv 文件中还有垃圾（如等）。
Is there another way to do this?
有没有另一种方法可以做到这一点？
回答by dcd0181
If you want to write each MySQL row to a CSV file, you could use the built in PHP5 function fputcsv
如果要将每个 MySQL 行写入 CSV 文件，可以使用内置的 PHP5 函数 fputcsv
$result = mysqli_query($con, 'SELECT * FROM table');
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
$fp = fopen('file.csv', 'w');
foreach ($row as $val) {
fputcsv($fp, $val);
}
fclose($fp);
Which should return a comma separated string for each row written to file.csv:
这应该为写入的每一行返回一个逗号分隔的字符串file.csv：
row1 val1, row1 val2
row2 val1, row2 val2
etc..
Also be sure to check permissions for the directory you are writing to.
还要确保检查您正在写入的目录的权限。
回答by RCNeil
This is a solution that incorporates the headers or field names by using MySQLi'sfetch_fieldsfunction -
这是一个通过使用MySQLi 的fetch_fields函数合并标题或字段名称的解决方案-
$query = "SELECT * FROM table";
$result = $db->query($query);
if (!$result) die('Couldn\'t fetch records');
$headers = $result->fetch_fields();
foreach($headers as $header) {
$head[] = $header->name;
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="export.csv"');
header('Pragma: no-cache');
header('Expires: 0');
fputcsv($fp, array_values($head));
while ($row = $result->fetch_array(MYSQLI_NUM)) {
fputcsv($fp, array_values($row));
}
die;
}
This is also a modification of a different answerwhere a similar solution was suggested but the headers part did not work for me, so I changed the method of retrieving them. Credit is due to @Jrgns
这也是对不同答案的修改，其中建议了类似的解决方案，但标题部分对我不起作用，因此我更改了检索它们的方法。归功于@Jrgns
回答by peterm
You can try to utilize MySql INTO OUTFILEclause:
您可以尝试使用 MySqlINTO OUTFILE子句：
SELECT *
INTO OUTFILE '/tmp/tablename.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM tablename
WHERE userid = 1
回答by manuelmatas
for creating and downloading a csv file from a mysqli query (using object oriented) I think this would help.
对于从 mysqli 查询（使用面向对象）创建和下载 csv 文件，我认为这会有所帮助。
This is a Class that connects with the database and the functions will do whatever you want using mysqli and PHP. In this case, calling this class (require or include), just use the "downloadCsv()" function.
这是一个与数据库连接的类，函数将使用 mysqli 和 PHP 执行您想要的任何操作。在这种情况下，调用这个类（需要或包含），只需使用“downloadCsv()”函数。
As an example, this would be the "class.php" file:
例如，这将是“class.php”文件：
<?php
class DB{
private $con;
//this constructor connects with the database
public function __construct(){
$this->con = new mysqli("Your_Host","Your_User","Your_Pass","Your_DatabaseName");
if($this->con->connect_errno > 0){
die('There was a problem [' . $con->connect_error . ']');
}
}
//create the function that will download a csv file from a mysqli query
public function downloadCsv(){
$count = 0;
$header = "";
$data = "";
//query
$result = $this->con->query("SELECT * FROM Your_TableName");
//count fields
$count = $result->field_count;
//columns names
$names = $result->fetch_fields();
//put column names into header
foreach($names as $value) {
$header .= $value->name.";";
}
}
//put rows from your query
while($row = $result->fetch_row()) {
$line = '';
foreach($row as $value) {
if(!isset($value) || $value == "") {
$value = ";"; //in this case, ";" separates columns
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . ";"; //if you change the separator before, change this ";" too
}
$line .= $value;
} //end foreach
$data .= trim($line)."\n";
} //end while
//avoiding problems with data that includes "\r"
$data = str_replace("\r", "", $data);
//if empty query
if ($data == "") {
$data = "\nno matching records found\n";
}
$count = $result->field_count;
//Download csv file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=FILENAME.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data."\n";
}
?>
After creating the "class.php" file, in this example, use that function on "download.php" file:
创建“class.php”文件后，在本例中，在“download.php”文件上使用该函数：
<?php
//call the "class.php" file
require_once 'class.php';
//instantiate DB class
$export = new DB();
//call function
$export->downloadCsv();
?>
After download, open the file with MS Excel.
下载后，用 MS Excel 打开文件。
I hope this help you, I think I wrote it well, I didn't feel comfortable with the text and code field.
我希望这对您有所帮助，我认为我写得很好，我对文本和代码字段感到不舒服。
回答by Tom Wright
Try this out.
试试这个。
function addRowToCsv(& $csvString, $cols) {
$csvString .= implode(',', $cols) . PHP_EOL; //Edits must be 6 characters - all I added was a "." before the =. :-)
}
$csvString = '';
$first = true;
while ($row = mysqli_fetch_assoc($query)) {
if ($first === true) {
$first = false;
addRowToCsv($csvString, array_keys($row));
}
addRowToCsv($csvString, $row);
}
header('Content-type: text/csv');
header('Content-disposition: attachment;filename=MyCsvFile.csv');
echo $csvString;
Notice that the first argument to addRowToCsv is passed by reference. This is not required and you could easily use a return value, but this is just how I would do it.
请注意， addRowToCsv 的第一个参数是通过引用传递的。这不是必需的，您可以轻松使用返回值，但这正是我要做的。
If you want to save the output to a file rather than serving it as a download then use the above but replace
如果要将输出保存到文件而不是将其作为下载提供，请使用上述但替换
header('Content-type: text/csv');
header('Content-disposition: attachment;filename=MyCsvFile.csv');
echo $csvString;
With..
和..
file_put_contents('MyCsvFile.csv', $csvString);
