crosstab in php for codeigniter

Question

am using the following code

  Sample Gender Handedness
    1   Female  Right-handed
    2   Male    Left-handed
    3   Female  Right-handed
    4   Male    Right-handed
    5   Male    Left-handed
    6   Male    Right-handed
    7   Female  Right-handed
    8   Female  Left-handed
    9   Male    Right-handed
    10  Female  Right-handed

              Left-
             handed    Right-
                       handed Total
    Males   2   3   5
    Females 1   4   5
    Total   3   7   10

am having as above table and i need to get as the second table. i need to use crosstab but i was not known how to do that. can someone help me please. am doing this in codeigniter.

en.wikipedia.org/wiki/Cross_tabulation please check this url — user2739848

peterm peterm · Accepted Answer · 2013-09-13T05:06:24

You can do it with a query

SELECT gender,
       SUM(CASE WHEN Handedness = 'Left-handed' THEN 1 ELSE 0 END) left_handed,
       SUM(CASE WHEN Handedness = 'Right-handed' THEN 1 ELSE 0 END) right_handed,
       COUNT(*) total
  FROM Table1
 GROUP BY gender WITH ROLLUP

Output:

| GENDER | LEFT_HANDED | RIGHT_HANDED | TOTAL |
|--------|-------------|--------------|-------|
| Female |           1 |            4 |     5 |
|   Male |           2 |            3 |     5 |
| (null) |           3 |            7 |    10 | -- this is your total produced by WITH ROLLUP

Here is SQLFiddle demo

I'm not an expert in CI but your code might look like

$sql = "SELECT gender,
               SUM(CASE WHEN Handedness = 'Left-handed' THEN 1 ELSE 0 END) left_handed,
               SUM(CASE WHEN Handedness = 'Right-handed' THEN 1 ELSE 0 END) right_handed,
               COUNT(*) total
          FROM Table1
         GROUP BY gender WITH ROLLUP";
$data = $this->db->query($sql)->result_array();

crosstab in php for codeigniter

1 Answers