[prog] mysql, php, arrays

[Conor Daly]

On Tue, Oct 14, 2003 at 01:22:46AM -0400 or thereabouts, Saint-Wolfe wrote:
> I have a select statement that produces these results
> 
> mysql>  select s_code, term, year, c_code, dayname(date), status from 
> attendance where s_code=193586498 and term='fall' and year=2003 and 
> date between '2003-09-01'and '2003-09-05';
> 
> I need to get this into a table that looks  something like this
> +-----------+------+------+--------+---------------+---------------+
> 
> MWF-TuTh | mon  | tues | wed    | thur           | fri
> +-----------+------+------+--------+---------------+---------------+
> 8am                      in attendance                        in 
> attendance

All you need for your table is a count of days in attendance rather than all
you've asked for.  Something like:

select dayname(date), count(*) from attendance 
where date between '2003-09-01'and '2003-09-05'
group by dayname(date);

will give you a list like:

Tuesday	  |  3
Wednesday |  2
Thursday  |  3
Friday    |  1

Now all you need is to parse that and print in your horizontal format.  Of
course, you need to handle 's_code' also.  Once you start building pivot
tables, sql isn't enough and you have to start looking at a program.  I
found it best to use sql to get your first example as output to file and
then use a separate program to do the pivat table bit on the datafile.

Conor
-- 
Conor Daly 
Met Eireann, Glasnevin Hill, Dublin 9, Ireland
Ph +353 1 8064276 Fax +353 1 8064247
------------------------------------
bofh.irmet.ie running RedHat Linux 08:47:00  up 39 days, 22:11, 13 users,  load average: 0.00, 0.10, 0.09


**********************************************************************
This e-mail and any files transmitted with it are confidential 
and intended solely for the addressee. If you have received
this email in error please notify the sender.
This e-mail message has also been scanned for the
presence of computer viruses.
**********************************************************************