outputting mysql data in columns not rows

mysql php question

[grey580]

Alrite here we go.

I have some information I wish to pull from a database and display in spreadsheet like format.
I'm querying our db to find apps from a date range that were assigned to a rep, then total up the number of apps per month the rep was assigned and totals per rep over the months.

The problem i'm having is getting the data to output into columns.
I'm sure it has a simple solution but it's eluding me.

Here is an example of the data should look like on the webpage.

¦ month 1 ¦ month 2 ¦ month 3 ¦ Total
name1 ¦ 2 ¦ 2 ¦ 2 ¦ 6
name2 ¦ 2 ¦ 2 ¦ 2 ¦ 6
name3 ¦ 2 ¦ 2 ¦ 2 ¦ 6

Total ¦ 6 ¦ 6 ¦ 6 ¦ 18

[grey580]

Oh yeah and if someone can recommend a querystring i'd appreciate it.

[httpwebwitch]

output them into an HTML table [w3.org]

[iblaine]

Look up the mysql case statement, similar to decode for oracle. I'm guessing you have a table with two columsn, name and month and for each name you want to sum up the totals for month by name. The mysql may not be correct - the oracle sql is correct so it could be translated into mysql.

table column: name,month
table data:
jeff,1
jeff,2
bob,3
bob,1
bob,3

example of output:
name,month1,month2,month3
jeff,1,1,0
bob,1,0,2

Oracle:
select
name,
sum(decode(month,1,1,0)) as month1,
sum(decode(month,2,1,0)) as month2,
sum(decode(month,3,1,0)) as month3
from
mytable
group by
name;

mysql:
select
name,
sum(case month
when 1 then 1) as month1,
sum(case(month
when 2 then 1) as month2,
sum(case month
when 3 then 1) as month3
from
mytable
group by
name;

[dkin]

if the last solution does not help, post some code and we will see what we can do for you.

[grey580]

this is my current query.

$myquerystring2 = "select rep_id, count(rep_id) as apps, date_format(app_date, \"%Y-%m\") as t_date from fullapp where app_date >= \"2004-06-01\" AND app_date <= \"2004-08-31\" group by t_date, rep_id";

And it is giving me the data i need.

But now I need to output this to a webpage.
so it displays the months on the top row.
the users down the left side.
and the values of the count in the appropriate row and column.
and on the far right column display the totals for all the months.
and on the bottom row display the totals for the months from all users.

I'd like to do this whithin my while statement. but i'm having issues. I'd post some code but i deleted it in frstration. And i'm starting over again.

this is what i'd like to get as ouptput on the page.
This is just an example. the query has a date range that can be increased. the table needs to be able to dynamically increase or decrease in size.
<table width="505" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>&nbsp;</td><td>month 1</td><td>month 2</td><td>month 3</td><td>total</td>
</tr>
<tr>
<td>rep 1</td><td>2</td><td>2</td><td>2</td><td>6</td>
</tr>
<tr>
<td>rep 2</td><td>2</td><td>2</td><td>2</td><td>6</td>
</tr>
<tr>
<td>rep 3</td><td>2</td><td>2</td><td>2</td><td>6</td>
</tr>
<tr>
<td>total</td><td>6</td><td>6</td><td>6</td><td>18</td>
</tr>
</table>

[iblaine]

I think this will work.

select
1,
name,
sum(case month when 1 then 1) as month1,
sum(case month when 2 then 1) as month2,
sum(case month when 3 then 1) as month3,
sum(case month when 1 then 1 when 2 then 1 when 3 then 1) as total
from mytable
group by name
union
select
2,
NULL as name,
sum(case month when 1 then 1) as month1,
sum(case(month when 2 then 1) as month2,
sum(case month when 3 then 1) as month3,
sum(case month when 1 then 1 when 2 then 1 when 3 then 1) as total
from mytable;

[grey580]

nice. ...ok i see where you're going however there is going to be some drop down menus on the page to select a date range. ranges of several months/years at a time.
from 2004-01 to 2004-08 for example.

and we have more than just 3 reps i need to have the query select them all from the table and display them along with all the other info.