我经营着一个小型的服务台,我要求每个月向我的直属经理制作一份工单报告。

我正在寻求升级生成此报告的方式,因为目前,我运行每月使用Navicat手动编写的以下查询:

 SELECT
 updates.incidentid AS `Incident ID`,
 updates.bodytext AS `Call Status`,
 updates.duration AS `Total Minutes`,
 software.`name` AS 'Support Type',
 incidents.title AS Description,
 contacts.forenames AS `First Name`,
 contacts.surname AS `Last Name`,
 FROM_UNIXTIME(incidents.opened, '%d.%m.%Y') AS `Date Logged`,
 sites.`name`,
 users.realname
 FROM
 updates
 INNER JOIN incidents ON updates.incidentid = incidents.id
 JOIN contacts ON incidents.contact = contacts.id
 INNER JOIN sites ON sites.id = contacts.siteid
 INNER JOIN users ON incidents.`owner` = users.id
 INNER JOIN software ON incidents.softwareid = software.id
 WHERE
 updates.bodytext = 'Incident Closed'
    AND FROM_UNIXTIME(incidents.opened, '%m') = '07'
            AND FROM_UNIXTIME(incidents.opened, '%Y') = '2016'
 ORDER BY contacts.siteid ASC


我想将所有内容放在一个非常简单的网页中,允许用户选择月份和年份,然后为他们提供HTML格式的下载。

我最好放在PHP中还是可以用HTML创建它?请客气,我是一个新手,我的思维过程有点差。

非常感谢您的耐心配合!

最佳答案

我将向您展示基本的内容:首先,一个HTML文件,用户在其中输入月份和年份作为数字(请注意对“ report.php”的调用):

<html>
  <body>
    <form method="post" action="report.php">
      Enter month (1..12) <input type="text" name="month"/>
      <br/>
      Enter year (four digits) <input type="text" name="year"/>
      <br/>
      <input type="submit" value="Display report"/>
    </form>
  </body>
</html>


现在,PHP文件“ report.php”(注意如何在开始时捕获月份和年份并将其存储在变量$month$year中):

<?php
$month = $_POST["month"]; // PARAMETERS FROM
$year  = $_POST["year"];  // THE HTML FILE.
$cnx = mysqli_connect( "localhost","user","password","databasename");
$data = mysqli_query( $cnx,"YOUR BIG SELECT HERE" ) or die( mysqli_error( $cnx ) );
echo "<table>" .
     "  <tr>" .
     "    <td>Incident id</td>" .
     "    <td>Call status</td>" .
     "    <td>Description</td>" .
     "    <td>Date logged</td>" .
     "    <td>Site name</td>" .
     "    <td>User</td>" .
     "  </tr>";
while ( $row = mysqli_fetch_array( $data ) ) // LOOP TO DISPLAY DATA.
  echo "<tr>" .
       "  <td>{$row["IncidentID"]}</td>" .
       "  <td>{$row["CallStatus"]}</td>" .
       "  <td>{$row["Description"]}</td>" .
       "  <td>{$row["DateLogged"]}</td>" .
       "  <td>{$row["name"]}</td>" .
       "  <td>{$row["realname"]}</td>" .
       "</tr>";
echo "</table>"; // TABLE END.
?>


您必须用大查询替换文本“ YOUR BIG SELECT HERE”。这是在查询中插入变量$month$year的方式:

 SELECT
 updates.incidentid AS `Incident ID`,
 updates.bodytext AS `Call Status`,
 updates.duration AS `Total Minutes`,
 software.`name` AS 'Support Type',
 incidents.title AS Description,
 contacts.forenames AS `First Name`,
 contacts.surname AS `Last Name`,
 FROM_UNIXTIME(incidents.opened, '%d.%m.%Y') AS `Date Logged`,
 sites.`name`,
 users.realname
 FROM
 updates
 INNER JOIN incidents ON updates.incidentid = incidents.id
 JOIN contacts ON incidents.contact = contacts.id
 INNER JOIN sites ON sites.id = contacts.siteid
 INNER JOIN users ON incidents.`owner` = users.id
 INNER JOIN software ON incidents.softwareid = software.id
 WHERE
 updates.bodytext = 'Incident Closed'
    AND FROM_UNIXTIME(incidents.opened, '%m') = '$month'   ◄■■■■
            AND FROM_UNIXTIME(incidents.opened, '%Y') = '$year'   ◄■■■■
 ORDER BY contacts.siteid ASC


将先前的代码复制并粘贴到名为“ report.html”和“ report.php”的两个文件中,然后在浏览器中打开“ report.html”。

有许多要改进的地方,例如,使用jQuery,您可以改善用户输入月份和年份的方式,但这是另一个问题。

10-05 20:39
查看更多