使用 1 个查询运行 2 个 sql 查询以将查询结果保存为 .csv 格式 [英] Run 2 sql query with 1 query for save query result as .csv format

问题描述

在我的网页中，我有 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...     

    }
}        

推荐答案

1) 我如何将此查询运行到一个查询?

1) How do i run this query to ONE query ?

您不希望它作为一个查询运行.通常最好有很多小的简单查询而不是一个复杂的查询.实际上，我建议您更新代码以进行更多查询，例如不存在()"的内容不应作为子查询完成，它应该是一个完全独立的查询以提高性能.

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 文件?

2) Following query is showing data successfully. So how do i save it as .csv file ?

有两个部分，首先您需要发送正确的 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 格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！