我有不同表的mysql数据库。表之间的数据是链接的,我使用userid检索和显示它们。我使用的参考从PHP MYSQLi显示数据库中的所有表
但是如何将这些信息导出为csv文件呢?我已经尝试将echo更改为字符串并将字符串打印到csv文件,但它不起作用。
我也尝试了以下例子:http://sudobash.net/php-export-mysql-query-to-csv-file/
我可以看到数据但上面也有垃圾(比如"<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1' cellspacing='0' cellpadding='1'>"
)
还有别的方法吗?
如果您想将每一行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);
应该为写入file.csv
的每一行返回一个逗号分隔的字符串:
row1 val1, row1 val2
row2 val1, row2 val2
etc..
这是一个通过使用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;
}
这也是一个不同的答案的修改,其中提出了类似的解决方案,但头部分不适合我,所以我改变了检索它们的方法。功劳归功于@Jrgns
你可以尝试使用MySql的INTO OUTFILE
子句:
SELECT *
INTO OUTFILE '/tmp/tablename.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ''n'
FROM tablename
WHERE userid = 1
用于从mysqli查询(使用面向对象)创建和下载csv文件,我认为这将有所帮助。
这是一个连接数据库的类,函数将使用mysqli和PHP做任何你想做的事情。在这种情况下,调用这个类(require或include),只需使用"downloadCsv()"函数。
作为一个例子,这是"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";
}
?>
创建"class.php"文件后,在本例中,对"download.php"文件使用该函数:
<?php
//call the "class.php" file
require_once 'class.php';
//instantiate DB class
$export = new DB();
//call function
$export->downloadCsv();
?>
下载后,用MS Excel打开文件
我希望这对你有帮助,我认为我写得很好,我对文本和代码字段感到不舒服。
试一下
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;
注意,addRowToCsv的第一个参数是通过引用传递的。这不是必需的,您可以很容易地使用返回值,但这正是我要做的。
如果您想将输出保存到文件中,而不是作为下载提供,则使用上述命令,但替换
header('Content-type: text/csv');
header('Content-disposition: attachment;filename=MyCsvFile.csv');
echo $csvString;
与. . file_put_contents('MyCsvFile.csv', $csvString);