create results!

[fender]

Hi all!

New member in the forum, and I have a difficult* question for you.

First of all i want to apologise for my pure english!

I've made a Trouble Ticketing System for my company, and now I want to create reports. What kind of reports ? Ok...:

Some of the fields in mySQL, are:

ttn, priority,dateopen,dateclose,duration

The ttn and priority fields are varchar, ususal fields.
The duration field is varchar with format "4 hour(s), 00 minute(s)"
The dateopen,dateclose is DATE format 2003-03-20
The timeopen,timeclose is TIME format 12:00:00

I want to display the number of the tickets that the duration field is less than the string 4 hour(s), 00minute(s), and these tickets.

ex.

dateopen, dateclose,timeopen,timeclose,duration
2003-1-1, 2003-1-2, 12:00:00 11:59:00 23hour(s), 33minute(s)
2003-1-1, 2003-1-1, 12:00:00 15:36:00 3hour(s), 36minute(s)
2003-1-1, 2003-1-1, 12:00:00 15:46:00 3hour(s), 46minute(s)

from these, with criterion the 4 hour(s), 00 minute(2), i=I want to display :

2 tickets of 3 are less than 4 hour(s), 00 minute(s) :
2003-1-1, 2003-1-1, 12:00:00 15:36:00 3hour(s), 36minute(s)
2003-1-1, 2003-1-1, 12:00:00 15:46:00 3hour(s), 46minute(s)

any idea?

I tryied to do this with if statements, but the only thing I contrive is to show the correct numbers of tickets found, but not these 2 records.

• difficult for me, maybe for you guyz, it's easy!

[barand]

Why don't you make life easy for yourself and hold the duration in minutes (integer)?

[fender]

This is an option I have, but, I don't how this option will solve the problem I have...

Originally posted by barand
Why don't you make life easy for yourself and hold the duration in minutes (integer)?

[barand]

It then becomes a simple query

$query = select count(*) as tktcount from table where duration < 240"

$result = mysql_query($query);
list($count) = mysql_fetch_row($result);

echo "There are $count less than 4 hours"