mySQL queries into array

ICE9

I'm a noob with PHP arrays, so i could really use some assistance on this fairly simple issue.

I'm making a stats page for IT techs that uses support ticket and phone data to produce performance stats. The problem is that i need to pull multiple queries grouped by technician, and still be able to show the data with techs on rows and results in columns. This example is simplified, so the full version takes ALOT longer due to 15 queries per tech and over 30 techs.

Example

Tech | Opened | Closed | Backlog
Name1 | 20 | 10 | 30
Name2 | 25 | 12 | 30
Name3 | 21 | 16 | 30
Name4 | 10 | 12 | 30

Current Method

The current method runs an individual query for each cell. There is a query for each column, that uses the name of the tech to pull only the count for that tech. It loops through a list of techs.

Query Agents : pulls the list of technicians names
Query Opened:
Query Closed:
Query Backlog:

For each agent, the opened, closed, backlog queries are run. This takes WAY too long.

Proposed Method

I have modified the queries to "Group by" tech instead of "where tech =". This way i can run 3 queries that each contain all techs stats.

The loop would once again loop through all techs but would print the results from the correct row of the queries. Now I encounter 2 problems.
1. The techs are not in the same order in the different queries.
2. A tech may be in one query but not another (ie opened but did not close any tickets)

I guess what i would like to know how to do is, save the query results to an array where the tech's name is the array row pointer.

I.E. Opened(techname) returns the result of the Opened query where the agent is techname.

Any suggestions?

bpat1434

Why not do something like this:

<?php
$sql = "SELECT * FROM `stats_table` GROUP BY Agent";
$result = mysql_query($sql);
echo '<table>
    <tr>
      <th>Name</th><th>Opened</th><th>Closed</th><th>Backlog</th>
    </tr>';
while($row = mysql_fetch_array($result))
{
    $tech = $row['Agent'];
    $selsql = "SELECT * FROM `stats_table` WHERE Agent = '$tech'";
    $rslt = mysql_query($selsql);
    while($row2 = mysql_fetch_array($rslt))
    {
        echo '<tr>';
        echo '<td>'.$row['Agent'].'</td>
            <td>'.$row2['Opened'].'</td>
            <td>'.$row2['Closed'].'</td>
            <td>'.$row2['Backlog'].'</td>
            </tr>';
    }
}
echo '</table>';
?>

That's probably what you're looking for.

Basically, what we do is get all the results by our first query and group them by the Agent name. This will return 1 per agent essentially. So with that, we can now fetch an array and use those values to get the other information.

So the second sql statement takes a value ($row['Agent']) and uses it to compare to the table and select all the rows that meet the criteria. Then we get an array of the information, and for each row there is, we show the table of information.

Then loop through until there is nothing left.

~Brett

ICE9

This is a perfect solution for the output, but still isn't the entire solution.

$sql = "SELECT * FROM stats_table GROUP BY Agent";

That query assumes that all the stats are already in a two dimentional table. Part of the problem is getting the 4 queries into a two dimensional table.

Field 1 = Query that pulls the list of names.
Field 2 = Qeury that shows count of opened, grouped by agent
Field 3 = Qeury that shows count of closed, grouped by agent
Field 2 = Qeury that shows count of backlog, grouped by agent

I would run query 1 for the list of agents, then run queries 2,3,4 grouped by agent. Then match the results of queries 2,3,4 to a 2 dimensional array where the results of query 1 are the row pointers.

I can say it, i just can't seem to understand how PHP handles the arrays. So far the mySQL array to PHP array has been the most difficult portion of PHP for me to understand.

bpat1434

I'm not understanding how you want to make it easier by running 3 different queries for each person. What is the table setup? Do a mySQL dump so I can see what you're doing, and maybe I'll be able to help you more.

~Brett

ICE9

Sry about the double post, but here's a more specific question.

This is how i could solve it if i knew the right command to find the row in the mySQL results.

Query 1 returns
Agent1
Agent2
Agent3

Query 2 returns
Agent1, 10
Agent2, 15
Agent3, 12

Query 3 returns
Agent1, 5
Agent3, 6

After running the 4 queries, i can create a data table looping through the results of query 1, and using the results of query 1 to find the data in the additional queries.

What command would allow me to ruturn the value of the 2nd field in the mySQL query, where the first field equals the agent in the above example? With my current knowledge of PHP, i would have to loop through the entire array till i found the agent then copy the count result to the stats array. This is not a good solution. As seen above, the 3rd query did not return a result for agent2 so it would have to be able to handle the error and return a 0.