need to average values across multiple records which have the same id

[qtrimble]

I have a MySQL database which contains water quality data from

sampling events.  There are multiple records for each of the sample

events.  Each one of the records from the sample event contain the

sample date and a site identification value (numeric).

 

What I need to do is search all of the records that exist in the

database which occur on the same date with the same site

identification value.  Each of the water quality measurements for a

given sample event, for a given site, need to be averaged.

 

HERE IS THE QUESTION:

With the information that i provided above, which scenario would be

the best:

 

1) Create a View in MySQL

2) Use PHP to do this and store averaged values in an array

 

I'm not very skilled in these technologies so I will need some details

from your answer to get me started.

 

Much appreciation to all who contribute to solving my problem.

[mr_bijae]

There are arguments for both scenarios you have suggested. The primary consideration is, will PHP be the only API accessing the data? If so then you're fine building it in PHP. If you're going to have other applications getting the data from the database, then you're better off putting it in view or stored procedure.

[mr_bijae]

Without knowing your table structure I'm not able to write a specific query for you. however it should be something like this;

 

select avg(waterquality) as quality, siteId, sample_date

from quality_table

group by Sample_date, siteid

 

This will produce a query that gives one average value, per date, per site id