问题描述
在我的网页中,我有 2 页.1) admin.php 2) csv.php. 在 admin.php 页面中,以下查询显示来自 db 的数据.在 csv.php 页面中,我使用相同的查询将数据保存为 .csv 格式,但无法保存.
In my web I've 2 page. 1) admin.php 2) csv.php. In admin.php page following query is showing data from db. In csv.php page I used same query to save data to .csv format but Can't save it.
我决定在 ONE QUERY 中运行相同的查询.这样我就可以获得查询结果并将其保存为csv格式.
I decided to run this same query in ONE QUERY. So that I can get the query result and can save it to csv format.
问题:
1) 我如何将此查询运行到一个查询?
1) How do i run this query to ONE query ?
2) 以下查询成功显示数据.那么如何将其保存为 .csv 文件?
2) Following query is showing data successfully. So how do i save it as .csv file ?
我搜索 google 并找到了许多结果,这些结果显示了如何仅使用一个查询将数据保存为 .csv 格式.但是您看到我的查询中有 2 个 while 语句,那么我如何将其保存为 .csv 格式?不知道:(
I search google for that and found many result which is showing how do I save data as .csv format with only one query. But you see that I've 2 while statement in my query then how do i save it as .csv format ? NO idea :(
感谢并寻求您的帮助.:)
注意: 我是 php 和 mysql 的新手.
Thanks and Looking for your help. :)
Note: I'm new learner about php and mysql.
$sqlagentdetails = "select * from users WHERE company_name != ''";
$rowresult = mysql_query($sqlagentdetails);
while($row = mysql_fetch_array($rowresult, MYSQL_ASSOC))
{
$pc1 = $row['pc1'];
$pc2 = $row['pc2'];
$pc3 = $row['pc3'];
$pc4 = $row['pc4'];
$emailAgent = $row['user_email'];
$user_id = $row['id'];
$myQuery = mysql_query("
SELECT *
FROM user_property upr
WHERE (postcode = '$pc1' OR
postcode = '$pc2' OR
postcode = '$pc3' OR
postcode = '$pc4') AND
datediff(CURDATE(), upr.creation_date) <= 7 AND
NOT EXISTS(SELECT ofr.property_id
FROM offers ofr
WHERE ofr.property_id = upr.property_id AND
ofr.agent_id IN(SELECT id
FROM users
WHERE company_name !=''
)
)
ORDER BY property_id DESC");
while($row = mysql_fetch_array($myQuery)){
// more data are goes to here...
}
}
推荐答案
您不希望它作为一个查询运行.通常最好有很多小的简单查询而不是一个复杂的查询.实际上,我建议您更新代码以进行更多查询,例如不存在()"的内容不应作为子查询完成,它应该是一个完全独立的查询以提高性能.
You don't want it to run as one query. It's usually better to have lots of small simple queries instead of one complicated query. In fact I would suggest you update your code to have even more queries, for example the contents of the "not exists()" should not be done as a subquery, it should be a completely separate query to improve performance.
2) 以下查询成功显示数据.那么如何将其另存为 .csv 文件?
有两个部分,首先您需要发送正确的 HTTP 标头以触发 CSV 下载:
There are two parts, first you need to send the correct HTTP headers to trigger a CSV download:
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="export.csv";' );
然后将数据以CSV格式打印出来:
Then just print out the data in CSV format:
while ($row = mysql_fetch_array($myQuery)) {
$first = true;
foreach ($row as $cell) {
if ($first)
$first = false;
else
print ',';
print '"' . addslashes($cell) . '"';
}
print "\n";
}
注意:CSV 是一种错误的格式,这仅适用于 Microsoft Excel 的某些版本.根据用户居住的地方(例如:欧洲),它可能无法正常工作.但是,对于大多数版本的 Excel,上述方法都可以使用.除了避免使用 CSV 之外,没有其他好的解决方案.
Note: CSV is a bad format, and this will only work in some editions of Microsoft Excel. Depending where the user lives (eg: Europe) it might not work properly. For most editions of Excel the above will work however. There is no good solution except to avoid using CSV.
这篇关于使用 1 个查询运行 2 个 sql 查询以将查询结果保存为 .csv 格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!