MySQL Query Woes, Basic Question.

[pcjackson06]

I have two tables.

'work_types' has two columns:
1) wt : Work type name. (IE, Radiology)
2) wt_abv : Work type abbrv. (IE, RAD)

'data' has five columns:
1) customer_id (the hospitals abbrv)
2) customer_no (the hospital's ID #)
3) work_type (the type of transcription: radiology, operating room, etc.)
4) date_rev (date transcription received)
5) date_trans (date transcription was dictated)

The following query produces a single row identifying the average time of a transcription for any given work_type:

 $query = "SELECT customer_id, sec_to_time(AVG(unix_timestamp(date_trans) - unix_timestamp(date_rec))) FROM data WHERE customer_id=1 AND work_type='".$keywords['0']."' GROUP BY customer_id  

I send a "keyword" in the URL (ie. http://localhost/time.php?search=RAD) using a drop-down menu generated by the contents of TABLE 1 mentioned above.

The above is great! However, I want to have a single query that produces a list of all worktypes and the average time to dicate. I realize I need to use a loop, I just do not know how.

Any help would be GREATLY appriciated.

Cheers,
Phillip

[bpat1434]

In your query remove the customer_id=1 item, then just do:

$query = "SELECT customer_id, sec_to_time(AVG(unix_timestamp(date_trans) - unix_timestamp(date_rec))) AS avg_time FROM data WHERE work_type='".$keywords['0']."' GROUP BY customer_id";
while($row = mysql_result(mysql_query($query)))
{
   echo 'The average time to dicatate at hospital with id '.$row['customer_id'].' is '.$row['avg_time'].'<br>';
}

Something like that....