我经营着一个小型的服务台,我要求每个月向我的直属经理制作一份工单报告。
我正在寻求升级生成此报告的方式,因为目前,我运行每月使用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,您可以改善用户输入月份和年份的方式,但这是另一个问题。