i have 2 tables: departments (20 departments) , tickets (lets 1000 tickets). each ticket assigned 1 department. wanted know how many tickets assigned each department.
[solved] thank kind tip frz3993
for sake of completing thread working result, @ bottom find new script
i succeeded in these 2 queries.
the former loads departments.
for latter, have used select count how many tickets current department.
php
<?php $mysqli = new mysqli("localhost", "root", "*****", "tickets"); $openticket = null; /* check connection */ if ($mysqli->connect_errno) { printf("connect failed: %s\n", $mysqli->connect_error); exit(); } $query = "select id, name dept order id asc"; // loads departmentes , id if ($result = $mysqli->query($query)) { while ($row = $result->fetch_assoc()) { //echo $row["id"] . " " . $row["name"] . "<br>"; // test point $sqlcounttickets = "select count(dept_id) ticket (dept_id=" . $row["id"] . " , status!=1)"; // count how manytickets department id , if status 1, skip, since ticket closed //echo $sqlcounttickets; // test point $result2 = $mysqli->query($sqlcounttickets); //execute second query , result of select count //if ($mysqli->error) { //test point // die($mysqli->error); //} // no errors $rowdue = $result2->fetch_row(); if ($rowdue[0] > 0){ echo "deptlabelnum: " . $row["id"] . " - deptname: " . $row["name"] . " " . $rowdue[0] ."<br>"; } $openticket=$openticket+$rowdue[0]; } /* free result set */ $result->free(); } echo "<br>" . "open tickets: " . $openticket; /* close connection */ $mysqli->close(); ?> the output unsorted since tickets amount department random
deptlabelnum: 0 - deptname: global (all departments) 1 deptlabelnum: 1 - deptname: lcd 1 deptlabelnum: 2 - deptname: smartphones 6 deptlabelnum: 4 - deptname: pendrive 4 deptlabelnum: 6 - deptname: plasma 7 deptlabelnum: 22 - deptname: hdd 1 deptlabelnum: 23 - deptname: notebook 8 deptlabelnum: 24 - deptname: tablet 12 open tickets: 40 you may bet on :-) , i'd sort output in descending order
so tablet should first 12 tickets second notebook 8 tickets 3rd plasma
and on
do suggest load output of cycle mysql temporary table?
or use php array?
or can done more effective query?
thank , suggestion since confuse of three
r.
p.s. solution - new script 1 query new script encloses in html table result.
php
/* check connection */ if ($mysqli->connect_errno) { printf("connect failed: %s\n", $mysqli->connect_error); exit(); } echo '<table>'."\xa"; $query = " select count( ticket.id ) ticket_count, dept.id, dept.name ticket left join dept on ticket.dept_id = dept.id ticket.status !=1 group dept.id order ticket_count desc"; if ($result = $mysqli->query($query)) { while ($row = $result->fetch_assoc()) { echo "\t" . "<tr><th>" . $row["name"] . "</th><th>" . $row["ticket_count"] . "</th></tr>". "\xa"; $openticket=$openticket+$row["ticket_count"]; } /* free result set */ $result->free(); } echo "\t" . "<tr><th></th><th></th></tr>". "\xa"; echo "\t" . "<tr><th>" . "open tickets: " . "</th><th>" . $openticket . "</th></tr>". "\xa"; echo "</table>". "\xa"; /* close connection */ $mysqli->close(); ?>
you can 1 query. also, sure list of departments there no tickets them:
select d.*,tmp.ticket_count departments d left join (select count(*) ticket_count, department_id tickets group department_id) tmp on d.id = tmp.department_id 
Comments
Post a Comment