Hello

I am trying to count the number of hours someone has spent in a classroom setting.

So I have a mysql table with username, class_start_date , class_end_date

so each user could have multiple rows. So I need to for each row where the username equals a specific username get the # days between the class start and class end and then copunt the total number of hours based on an 8 hour work day.

so like
John Doe 2010-01-01 | 2010-01-02
John Doe 2009-12-11 | 2010-12-12

total classroom hrs 32 hrs

So can someone let me know what a mysql query might look for this
I tried

$sql = mysql_query("SELECT DATEDIFF('Class_startdate', 'Class_enddate') AS Days FROM Training_classes where Trainer='$inst'");
$result = mysql_fetch_array($sql);
extract($result);
echo "$Days";

SELECT SUM(DATEDIFF('class_end_date','class_start_date')) * 8 AS total_hours WHERE username='John Doe';


In DATEDIFF() first date should be ending date, and second date should be start date. If you switch them, you will get negative result.

Thanks for the help. Howevrr now I get an empty result set?
here if my full sql


$inst = 'jdoe';
$sql = mysql_query("SELECT SUM(DATEDIFF('Class_enddate','Class_startdate')) * 8 AS total_hours FROM Training_classes WHERE Trainer='$inst'");
$resultset = mysql_fetch_array($sql);
$hours = $numrow['total_hours'];
echo "$hours";


when I run the above query in my sql (replacing the $inst with a value) I get total_hours with a value of NULL ???

$resultset = mysql_fetch_assoc($sql);
$hours = $resultset['total_hours'];

I actually tried changing the mysql_fetch_array() to mysql_fetch_assoc and that did not work I still get a return of Null even though there is one test entry in the mysql database

so my code would be

$inst = 'jdoe';
$sql = mysql_query("SELECT SUM(DATEDIFF('Class_enddate','Class_startdate')) * 8 AS total_hours FROM Training_classes WHERE Trainer='$inst'");
$resultset = mysql_fetch_assoc($sql);
$hours = $resultset['total_hours'];
echo "$hours";

What is the output of var_dump($resultset)?

What is the output from mysql if you enter the query directly?

What is the output from mysql if you "SELECT * FROM Training_classes"?

In your example you listed "John Doe" in the username column, but you are searching for "jdoe"...

The query is sound though...


mysql> select (datediff('2010-01-02','2010-01-01')+datediff('2009-12-12','2009-12-11'))*8 as total_hours;
+-------------+
| total_hours |
+-------------+
| 16 |
+-------------+
1 row in set (0.00 sec)