how do Generating Database Cross Tabulations in php

Looking to hire a PHP developer for a paid position? Looking for a paid PHP job? Want to post your resume? Let the job hunt begin...

Moderator: General Moderators

Post Reply
manojsemwal1
Forum Contributor
Posts: 217
Joined: Mon Jun 29, 2009 4:13 am
Location: India

how do Generating Database Cross Tabulations in php

Post by manojsemwal1 »

hai everybody iam facing a problem to print my html table
My query are like
$sql2="SELECT Distinct
country, loc AS location,
COUNT(CASE WHEN dept = 'pers' AND gender = 'f' THEN id ELSE NULL END)
AS 'pers-f',
COUNT(CASE WHEN dept = 'pers' AND gender = 'm' THEN id ELSE NULL END)
AS 'pers-m',
COUNT(CASE WHEN dept = 'sales' AND gender = 'f' THEN id ELSE NULL END)
AS 'sales-f',
COUNT(CASE WHEN dept = 'sales' AND gender = 'm' THEN id ELSE NULL END)
AS 'sales-m',
COUNT(CASE WHEN dept = 'dev' AND gender = 'f' THEN id ELSE NULL END)
AS 'dev-f',
COUNT(CASE WHEN dept = 'dev' AND gender = 'm' THEN id ELSE NULL END)
AS 'dev-m',
COUNT(*) AS total
FROM
person
INNER JOIN
depts ON (person.dept_id=depts.dept_id)
INNER JOIN
location ON (location.loc_id=person.loc_id)
INNER JOIN
countries ON (location.country_id=countries.country_id)
GROUP BY
country, location";

And Result like this:

country location f -m f m f m total
Germany Berlin 1 0 0 0 0 1 2
Germany Bonn 0 0 0 1 0 0 1
Germany Munich 0 1 1 0 0 0 2
Itly Rome 0 1 0 0 1 0 2
uk London 0 1 0 1 0 0 2


When to get output in html format how can i print Country Name only Once time.

with regards,
Manoj
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: how do Generating Database Cross Tabulations in php

Post by jackpf »

You can select DISTINCT country name.
manojsemwal1
Forum Contributor
Posts: 217
Joined: Mon Jun 29, 2009 4:13 am
Location: India

Re: how do Generating Database Cross Tabulations in php

Post by manojsemwal1 »

Thanks for your quick reply i tried distinct but still display same result.
manojsemwal1
Forum Contributor
Posts: 217
Joined: Mon Jun 29, 2009 4:13 am
Location: India

Re: how do Generating Database Cross Tabulations in php

Post by manojsemwal1 »

somebody sent some reply
Post Reply