使用 php 执行 Postgres 查询并使结果可供下载

发布于 2024-12-12 00:09:06 字数 2410 浏览 0 评论 0原文

我得到一个查询，在一个场景中我执行查询，它以包含数据和标题等的表格格式显示结果...我想创建的第二个场景是一个直接下载链接，它将执行查询并放置结果转换为 .csv 文件以便在 Excel 中保存或打开。我已经有了显示代码，但我想知道如何为用户下载它。

这是执行和显示的一些代码。

    <?php
    $month = $_POST['mydate'];
    $monthfrm = date("Y-m-d", strtotime($month));
    $monthhd = date("F", strtotime($month));
    $monthto = date("Y-m-d", strtotime("$monthfrm . +1 month"));
    $date = date("F j,Y");
    if ($_POST['credsubmit']) {
       print('<head>');
       print('<link href="helper.css" media="screen" rel="stylesheet" type="text/css" />');
       print('<link href="dropdown.css" media="screen" rel="stylesheet" type="text/css" />');
       print('<link href="default.css" media="screen" rel="stylesheet" type="text/css" />');
    // Connecting, selecting database
       $dbconn = pg_connect("yeah we all know what goes here");
         if (!$dbconn) {
          die('Could not connect: ' . pg_last_error());
          }// Performing SQL query
     $query = "
       SELECT cm.\"ID\",a.\"ID\" as \"DDI\",cm.\"Date\",c.\"Amount\",ct.\"Name\" as \"Name\",cm.\"Comments\" as \"Comments\"
       FROM \"BIL_CreditMemo\" cm
       LEFT JOIN \"BIL_Credit\" c ON (c.\"ID\" = cm.\"BIL_CreditID\")
       LEFT JOIN \"ACT_Account\" a ON (c.\"ACT_AccountID\" = a.\"ID\")
       LEFT JOIN \"BIL_val_CreditMemoReason\" ct ON (ct.\"ID\" = cm.\"BIL_val_CreditMemoReasonID\")
       WHERE cm.\"Date\" >= '$monthfrm' AND cm.\"Date\" < '$monthto';
       ";
       $result = pg_query($query) or die('Query failed: ' . pg_last_error());
       // Printing results in HTML
       echo "<h2 align=center style=margin-top:20>Credit Memo Report for: $monthhd</h2>";
       echo "<table align=center width=60%>\n";
       print('<tr class="trstyle"><td>ID</td><td>DDI</td><td>Date</td><td>Amount</td><td>Name</td><td>Comments</td></tr>');
       while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
       echo "\t<tr>\n";
         foreach ($line as $col_value) {
           echo "\t\t<td>$col_value</td>\n";
         }
       echo "\t</tr>\n";
      }
      echo "</table>\n";// Free resultset
      pg_free_result($result);// Closing connection
      pg_close($dbconn);
      }
      ?>

这就是我到目前为止得到的结果并希望将结果直接下载到 csv 文件吗？非常感谢任何帮助。

原文

I got a Query that in one scenario I execute the query and it displays the results in a table format with data and headers etc... The second scenario I want to creat is a direct download link that will execute the query and put the results into a .csv file for saving or opening in Excel. I have the code for the display already but I am wondering how to make it download for the user.

Here is a tidbit of code for the execute and display.

    <?php
    $month = $_POST['mydate'];
    $monthfrm = date("Y-m-d", strtotime($month));
    $monthhd = date("F", strtotime($month));
    $monthto = date("Y-m-d", strtotime("$monthfrm . +1 month"));
    $date = date("F j,Y");
    if ($_POST['credsubmit']) {
       print('<head>');
       print('<link href="helper.css" media="screen" rel="stylesheet" type="text/css" />');
       print('<link href="dropdown.css" media="screen" rel="stylesheet" type="text/css" />');
       print('<link href="default.css" media="screen" rel="stylesheet" type="text/css" />');
    // Connecting, selecting database
       $dbconn = pg_connect("yeah we all know what goes here");
         if (!$dbconn) {
          die('Could not connect: ' . pg_last_error());
          }// Performing SQL query
     $query = "
       SELECT cm.\"ID\",a.\"ID\" as \"DDI\",cm.\"Date\",c.\"Amount\",ct.\"Name\" as \"Name\",cm.\"Comments\" as \"Comments\"
       FROM \"BIL_CreditMemo\" cm
       LEFT JOIN \"BIL_Credit\" c ON (c.\"ID\" = cm.\"BIL_CreditID\")
       LEFT JOIN \"ACT_Account\" a ON (c.\"ACT_AccountID\" = a.\"ID\")
       LEFT JOIN \"BIL_val_CreditMemoReason\" ct ON (ct.\"ID\" = cm.\"BIL_val_CreditMemoReasonID\")
       WHERE cm.\"Date\" >= '$monthfrm' AND cm.\"Date\" < '$monthto';
       ";
       $result = pg_query($query) or die('Query failed: ' . pg_last_error());
       // Printing results in HTML
       echo "<h2 align=center style=margin-top:20>Credit Memo Report for: $monthhd</h2>";
       echo "<table align=center width=60%>\n";
       print('<tr class="trstyle"><td>ID</td><td>DDI</td><td>Date</td><td>Amount</td><td>Name</td><td>Comments</td></tr>');
       while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
       echo "\t<tr>\n";
         foreach ($line as $col_value) {
           echo "\t\t<td>$col_value</td>\n";
         }
       echo "\t</tr>\n";
      }
      echo "</table>\n";// Free resultset
      pg_free_result($result);// Closing connection
      pg_close($dbconn);
      }
      ?>

So that is what I got so far and want to make the results a direct download to a csv file? Any help is greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

就此别过 2024-12-19 00:09:06

您需要设置一些 HTTP 标头，然后只需回显 CSV 数据即可。例如，

<?php
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=test.csv");
header("Pragma: no-cache");
header("Expires: 0");


echo "Name,Favourite Colour\n";
echo "Phil,Blue\n";
?>

请注意，如果您专门针对 Excel，则可以使用一些库来创建 .xls 文件；我非常喜欢 PHPExcel

You need to set a few HTTP headers, then you just echo out CSV data. For example,

<?php
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=test.csv");
header("Pragma: no-cache");
header("Expires: 0");


echo "Name,Favourite Colour\n";
echo "Phil,Blue\n";
?>

Note that if you're specifically targeting Excel, then there are libraries that will allow you to create .xls files; I quite like PHPExcel

回复收藏 0 原文