[RESOLVED] display data in a report format

[vang]

hi,

i would like to learn how to display mysql data in a report format.
So far i've not come across any online articles/tutorial on this.
Any guidance/direction/online tutorial is greatly appreciated, thanks.

table fields: fruit, unit cost, sold qty, sold date(yy-mm-dd)

eg. format:

fruits unit cost Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec total

apple 0.60 1.2 0.6 2.4 3.0 1.8 1.2 3.0 3.6 2.4 1.2 3.6 1.2 14.0
orange 0.80 1.6 0.8 2.4 3.2 1.6 1.6 3.2 4.0 2.4 1.6 3.2 1.6 18.0

banana 0.50 1.0 1.5 2.5 3.0 1.5 2.0 3.0 3.5 2.5 1.5 3.5 1.5 9.0

Total 3.8 2.9 7.3 9.2 4.9 4.8 9.2 11.1 7.3 4.3 10.3 4.3 41.0

pls note that there are no field in table that store the value of "total".
it is calculated when the data is extracted from the table & displayed on page.

regards

[bradgrafelman]

First off, is this statement completely accurate:

vang wrote:

sold date(yy-mm-dd)

? Are you sure it isn't YYYY-MM-DD, which is MySQL's format for storing data in a DATE column type... or is 'sold date' actually a VARCHAR column?

Also, what is it you're wanting to display exactly? Just the unit cost? How is that broken down into months... do you average all entries in the DB for a given month? Does the sold quantity come into play?

EDIT: Just to give you an idea of what I'm thinking thus far, I believe the key to the solution will be the use of "GROUP BY fruit, DATE_FORMAT(sold_date, '%b')" but there is some question as to what data you need for each month.

Once you figure out the data, you could loop through the DB results and build a multi-dimensional array, e.g.:

while($row = mysql_fetch_assoc($query))
    $data[$row['fruit']][$row['sold_month']] = $row['data_collected'];

[vang]

dear bradgrafelman,

many thanks for your guidance 🙂

i'll try to work from there.

regards