mashymoo mashymoo - 3 months ago 23
SQL Question

sql queries in php

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!

Answer

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.