douhao8456 2016-08-11 14:56
浏览 15
已采纳

php中的SQL查询

I run a small helpdesk and I have a requirement to produce a ticket report every month to my line manager.

I am looking to upgrade the way in which we produce this report because at present, I run the below query I wrote manually every month by using 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

I would like to put this all in a very simple webpage allowing the user select the month and year and then offer them a HTML formatted download.

Would I be best placed to put this in PHP or can I create it in HTML? Please be kind, I am a total newbie and my thought process is a little off.

Many thanks for your time and patience!

  • 写回答

5条回答 默认 最新

  • duan33360 2016-08-11 15:37
    关注

    I'll show you the basic : first, one HTML file where the user enters month and year as numbers (notice the call to "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>

    Now the PHP file "report.php" (notice how month and year are captured at the beginning and stored in variables $month and $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.
?>

    You have to replace the text "YOUR BIG SELECT HERE" by your big query. This is how you insert variables $month and $year in the query :

     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

    Copy-paste previous codes in two files named "report.html" and "report.php", then open "report.html" in your browser.

    There are many things to improve, for example, with jQuery you can improve how the user enters month and year, but that's for another question.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(4条)

报告相同问题？

悬赏问题

  • ¥15 拟通过pc下指令到安卓系统，如果追求响应速度，尽可能无延迟，是不是用安卓模拟器会优于实体的安卓手机？如果是，可以快多少毫秒？
  • ¥20 神经网络Sequential name=sequential, built=False
  • ¥16 Qphython 用xlrd读取excel报错
  • ¥15 单片机学习顺序问题！！
  • ¥15 ikuai客户端多拨vpn，重启总是有个别重拨不上
  • ¥20 关于#anlogic#sdram#的问题，如何解决？(关键词-performance)
  • ¥15 相敏解调 matlab
  • ¥15 求lingo代码和思路
  • ¥15 公交车和无人机协同运输
  • ¥15 stm32代码移植没反应