New to MySQL and lost already..

[JukesK]

Hi,
I'm new to coding (anything) and my manager has tasked me with developing a stats page for the IT dept. trouble ticket system (php helpdesk by EXOScripts.com)(MySQL database)

I have laid out the table on the page but just need to populate it.

the table has the following coloumns in it..

USER (staff member)
COUNT (Number of tickets clsoed relating to the dept.)
TICKET AVERAGE (Time taken on average to close the tickets.)

What i need is a piece of coding to average out the time taken over a user-definable period of time (timestamps)
i presume it would be something similar to

(sum=phpdesk_tickets.closed) - (sum=phpdesk_tickets.closed) / "$Count"

where $count is the count of the tickets relating to the user in a user-given period of time.

How do i get it to work out the average time per user and place it into the Ticket Average column??

As mentioned at the top i'm completely new to coding...

Thanx in advance for any and all advice...

[PirateTUX]

Ok...

To start out with, have you used PHP before? Do you know how to connect to a MySQL database? I promise I'm asking with no prejudice, not trying to belittle... just want to know where to start.

The way I typically approach tasks like this is to try to break them into two parts: the data collection part and the data manipulation part. The data collection part asks "What data do I need and what's the best way to ask for it?" and the data manipulation part asks "How do I want to present this data to the user?"

Starting with the data collection part, you want to present the user with information about a specific staff member from within a specific date range. That means that you're going to have arguments in your SQL statements about the USER and about the date of the table entry. You'll probably end up with a SELECT statement such as:

SELECT * FROM `table_name` WHERE `USERID`='###' AND (`ENTRY_DATE`>='some_date' AND `ENTRY_DATE`<= 'some_other_date')

This will get you the data you need for the next step, which is data manipulation.

Now, some people are very good and combining the two steps, and there are ways to do the data manipulation, especially aggregation, from within the SQL statement. I, however, am notoriously bad at this sort of thing. If there's anybody around who wants to contribute information about that, please don't hesitate.

As far as my methods for data manipulation, I like to be as straight-forward as possible. What I would do is get a count of the number of records that the above query returned, then run a for loop that adds all the recorded times together, then divide by the count. That will give you the average that you seek.

Please let everyone know what your current level of knowledge is with PHP. That will go a long way toward people being able to help you with your project!

Thanks!