gpt4 book ai didi

php - 单击按钮将 mysql 查询导出到 csv

转载 作者:行者123 更新时间:2023-11-29 11:55:03 24 4
gpt4 key购买 nike

所以我有一个 php 代码,显示 mysql 数据库中的表还有一个过滤表的过滤字段。

当单击“导出数据”按钮时,我需要帮助将表结果导出到 csv 文件。

我该怎么做?

下面是代码

<?php
error_reporting(0);
include("config.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>TAD Customer Search</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<style>
BODY, TD {
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
}
</style>
</head>


<body>

<form id="form1" name="form1" method="post" action="index.php">
<label>Search: </label>
<input type="text" name="string" id="string" size="20" value="<?php echo $_REQUEST["string"]; ?>" />
<label>Country: </label>
<select name="country">
<option value="">--</option>
<?php
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY country ORDER BY country";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
while ($row = mysql_fetch_assoc($sql_result)) {
echo "<option value='".$row["country"]."'".($row["country"]==$_REQUEST["country"] ? " selected" : "").">".$row["country"]."</option>";
}
?>
</select>
<input type="submit" name="button" id="button" value="Filter" />
</label>
<a href="index.php">Reset</a>
</form>
<br />
<table style="text-align: left; width: 100%;" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="text-align: left;"><input type="submit" name="btnexportdata" value="Export Data" /></td>
<td style="text-align: right;">
<form method="post" action="input.php">
<input type="submit" value="Add" />
</form></td>
</tr>
</tbody>
</table>
<br />
<table width="100%" border="1" cellspacing="0" cellpadding="4">
<tr>
<td width="150" bgcolor="#CCCCCC"><strong>Account Number</strong></td>
<td width="150" bgcolor="#CCCCCC"><strong>Company Name</strong></td>
<td width="150" bgcolor="#CCCCCC"><strong>Country</strong></td>
<td width="150" bgcolor="#CCCCCC"><strong>Email</strong></td>
<td width="150" bgcolor="#CCCCCC"><strong>Phone Number</strong></td>
<td width="150" bgcolor="#CCCCCC"><strong>Brands</strong></td>
</tr>
<?php

if ($_REQUEST["string"]<>'') {
$search_string = " AND (cname LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR email LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR accnumber LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR pnumber LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR brands LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%')";
}
if ($_REQUEST["country"]<>'') {
$search_country = " AND country='".mysql_real_escape_string($_REQUEST["country"])."'";
}

$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE id>0".$search_string.$search_country;
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
while ($row = mysql_fetch_assoc($sql_result)) {
?>
<tr>
<td><?php echo $row["accnumber"]; ?></td>
<td><?php echo $row["cname"]; ?></td>
<td><?php echo $row["country"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td><?php echo $row["pnumber"]; ?></td>
<td><?php echo $row["brands"]; ?></td>
</tr>
<?php
}
} else {
?>
<tr><td colspan="6">No results found.</td>
<?php
}
?>
</table>
</body>
</html>

最佳答案

这是您可以尝试的代码

 // Define  database connection variable dynamically
$DB_Server = "localhost"; //MySQL Server
$DB_Username = "root"; //MySQL Username



$DB_Password = ""; //MySQL Password
$DB_DBName = "test1"; //MySQL Database Name
$DB_TBLName = "tabletest"; //MySQL Table Name
$filename = "excelfilename"; //File Name
//create MySQL connection
$sql = "Select * from csvtable";
$Connect = @mysqli_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysqli_error() . "<br>" . mysql_errno());
//select database
$Db = @mysqli_select_db( $Connect,$DB_DBName) or die("Couldn't select database:<br>" . mysqli_error() . "<br>" . mysql_errno());
//execute query
$result = @mysqli_query( $Connect,$sql) or die("Couldn't execute query:<br>" . mysqli_error() . "<br>" . mysql_errno());

function cleanData(&$str)
{
if ($str == 't')
$str = 'TRUE';
if ($str == 'f')
$str = 'FALSE';
if (preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
$str = "'$str";
}
if (strstr($str, '"'))
$str = '"' . str_replace('"', '""', $str) . '"';
}

// filename for download
$filename = "file_" . date('Ymd') . ".csv";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: text/csv;");
$out = fopen("php://output", 'w');
$flag = false;

while ($row = mysqli_fetch_assoc($result))
{
if (!$flag)
{
// display field/column names as first row
fputcsv($out, array_keys($row), ',', '"'); $flag = true;
}
array_walk($row, 'cleanData');
// insert data into database from here
fputcsv($out, array_values($row), ',', '"');
}
fclose($out);
exit;
//end

关于php - 单击按钮将 mysql 查询导出到 csv,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33344819/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com